[OBIEE11g] - Adding Tooltips and conditional coloring to Currency Data

Here we will observe how to format data and add tool tips on column headers and column data.

1. Open the URL: http://localhost:9704/analytics and login as administrator user “webogic”.
2. Select “New” -> “Analysis” -> “Sample Sales” to start with your new report.
3. Select the 3 columns from the left “Subject Areas” pane: “Per Name Month” and “Revenue” as show below.
4. Then on the 3rd “Revenue” column select options “Edit Formula”
   
5. In the “Edit Column Formula” window select the “Custom Headings” checkbox and enter “Revenue D” in the “Column Heading” field and click the “OK” button.
   
6. Select the 3rd “Revenue D” column options and select “Column Properties”.
7. In the “Column Properties” window select the “Conditional Format” tab.
8. Select the “Add Condition >” then the “Revenue D”.
9. In the “New Condition” window select “is less than or equal to” for the “Operator” field and “700000″ for the “Value” column and click the “OK” button.
10. In the “Edit Format” window under the “Cell” section select the “Background Color”.
11. In the “Color Selector” window enter “#FFCC99″ and click the “OK” button.
    
12. Next select the “Data Format” tab and check the “Override Default Data Format” checkbox.
13. then enter as per below show screenshot:
    
14. Create another similar condition bye selecting the “Add Condition >” -> “Revenue D”.
15. Enter “is greater than” for the “Operator” and “700000″ for the “Value”.
16. Select the “Style” tab and click on the “Background Color” under the “Cell” section and enter “#CCFFFF” and click “OK” to close the “Color Selector” window.
17. On the “Data Format” tab enter the same values as mentioned in previous steps.
18. Make sure your entries confirm to the below screenshot and click the “OK” button.
    
19. Go back to the “Selected Columns” screen and select the 2nd “Revenue” column options “Edit formula”
20. Select the “Column Headings” and “Contains HTML Markup” checkboxes and enter below text for the “Column Heading” field:
21. <div title=”This Column Shows the Total Sales”>Revenue</div>
22. In the “Column Formula” enter the below text:
23. CASE WHEN “Base Facts”.”Revenue” > 700000 THEN ‘<div title=”Very Good Sales Revenue” style=”background: #ccffff;text-align: right”>’ || ‘$’ || CAST(“Base Facts”.”Revenue” AS VARCHAR(40)) || ‘</div>’ ELSE ‘<div title=”Not So Good Sales Revenue” style=”background: #ffcc99;text-align: right”>’ || ‘$’ || CAST(“Base Facts”.”Revenue” AS VARCHAR(40)) || ‘</div>’ END
    
24. Click “OK” button to close the “Edit Column Formula” window.
25. Select the 2nd “Revenue” column options “Column Properties”.
26. Then select the “Data Format” tab.
27. Check the “Override Default Data Format” checkbox.
28. Select the “HTML” for the “Treat Text As” field and click the “OK” button.
    
29. Click the “Save Analysis” icon on the top right to save the analysis and enter “Custom Rev Sales” for the “Name” field.
30. Click the “Results” tab to show Tooltips as below on the 2nd Column “Revenue” header.
    
31. You can observe that when the mouse is over the blue and orange areas in the 2nd “Revenue” columns, tooltips are show according to the color.

    

    hiicdfjd

    

    ediidcje

Summary:
As you can see we have created 2 currency columns and for one “Revenue” we had used the custom CASE statement which does our formatting.
for the other “Revenue D” column we have used OBIEE’s out-of-the box conditional formatting.
The tooltips feature is not supported on the out-of-box OBIEE hence we had to put that in our CASE statements.