Oracle BI EE 10.1.3.3/2 – Conditional Drills and Dynamic Tool Tips – HTML Formatting and GO URL

I got these 2 questions via email yesterday. We have discussed a variation of the above questions before. Lets look at both the questions one by one.

Question 1: How can we enable Conditional Drills for a column? i.e. if you look at the screenshot below, we have a very simple report containing only the region names. Based on the Region Values, is there a possibility to drill down to different reports. For example, i would like to drill down to say Report A for Asia Region and for other Regions i would like to have the default drill enabled.

      

This is a pretty interesting question since currently BI EE does not have the capability to setup conditional drills in the Repository. Lets look at a simple way of achieving this using HTML formatting. Of course, this is applicable only for tabular views. Lets start with creating a simple target Report (Report A for the drill on Asia Region) containing only the Region Column. Create a filter on this column with is prompted clause.

      

Now, start creating the main report with only the Region Column. In the formula tab of this Region Column, enter the below formula

CASE WHEN GEOGRAPHY.REGION_NAME = ‘Asia’ THEN ‘<a href=”http://localhost:9704/analytics/saw.dll?Go&Path=/shared/Paint%20Demo/Conditional%20Drills/Report%20A&Action=Navigate&P0=1&P1=eq&P2=GEOGRAPHY.REGION_NAME&P3=’||GEOGRAPHY.REGION_NAME||’&Options=rmf” style=”text-decoration: none;”>’||GEOGRAPHY.REGION_NAME||’</a>’ ELSE GEOGRAPHY.REGION_NAME END

      

Now, convert the data format of this column to HTML. Now when you click on Asia, it will take you to Report A with Asia as parameter being passed to that Report. For other Regions, normal drills would happen.

      

      

Question 2: How do we Produce Dynamic Tooltips in a Report?

Again, this is pretty straight forward if you know HTML tags. But remember this would work only for Tabular views. Lets start with the same simple report containing Region names. In the formula tab enter the below formula

‘<div title=”‘||GEOGRAPHY.REGION_NAME||’”>’||GEOGRAPHY.REGION_NAME||’</div>’

      

Convert the column to HTML format.

      

Now, you should be able to get the column values themselves as the tooltip in your tabular report.