Business Intelligence – Oracle

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

Posted by Venkatakrishnan J on March 5, 2008

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.



5 Responses to “Oracle BI EE – Conditional Drills and Dynamic Tool Tips – HTML Formatting and GO URL”

  1. anusha said


    I want to know how to conditionally navigate from a column, based on the values of two different columns. Also the how it is done in case of mutiple criterias…

  2. Vishnu said

    Hi Venkat

    I recvd the below error when I tried to apply the above said tool tip text.
    Pls advice how to proceed

    View Display Error
    Odbc driver returned an error (SQLExecDirectW).
    Error Details
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 42021] The query does not reference any tables. (HY000)
    SQL Issued: SELECT ‘’||MEDIA.Company||’’ saw_0 FROM OWB_TARGET ORDER BY saw_0


  3. Debashis said

    Hi Venkat ,

    The main problem I have found for this while I am adding a link using href and concatenating with report data(which is date) and then displaying a images aside of that .
    Its working perfectly fine with pivot table while my pivot is at column level except the sorting on date .This I believe in next OBI version will adress i.e conditional formatting on pivot date column with sorting .

    But this is a very helpful article indeed !

    Many thanks ,

  4. Rajesh yadav D said

    Hi Venkat,

    i want to know How can i pass parameters in a narative view using a ‘href’ or a GO URL

    Eg: @1

    as you have explained in the above example using some parameters (eg:P0&P1) how can i pass the parameters.

    Best Regards,

  5. Varun Malhotra said

    Hi Venkat,

    thanks a lot for this article. I am halfway through using this method but half of the input still required from you. Could you please help me out in below scenario:

    The column from where i am conditionally drilling down is taking value from dashboard promt and i need to use this presentation variable in the title of target report as well. If such thing is feasible, please help me out.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: