Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Drills across Sections in a Dashboard

Posted by Venkatakrishnan J on December 26, 2007

Another pretty interesting question came up the other day on one of our internal forums. A small variation of the question goes like this

” I have a tabular view of a report in a dashboard. I also have a chart view of a different report in the same dashboard but in a different section. So, if i click on a metric say Year 2003 on the tabular view then the graph in the another section alone(not other reports) should filter for the year 2003″

We will be seeing how to go about the achieving the above by creating 2 simple reports. Lets start with a simple report containing 2 columns. Year and Sales.

      

Create a tabular view for the above report. I have saved this report as Report4.

      

Now lets create another report with the same columns Year and Sales (for simplicity sake iam using the same columns. You can create any other report). But in this case lets create a chart view instead of a tabular view. Also create a filter on Report5 on year column with “is prompted” clause. Save this report as Report5.

      

      

Our aim is, when i click on any year in the tabular report(report4) then the corresponding chart view in Report5 should vary.

Now go back to the older report and go to the Column properties of the Year Column and in the Data Format tab change the data format to Custom Text and enter the following

[html] <a href= http://localhost:9704/analytics/saw.dll?GO&path=/shared/paint+demo/script+test/report5&Action=Navigate&P0=1&P1=eq&P2=TIMES.CAL_YEAR_NUMBER&P3=@ target=”biee2″>@</a>

What we have basically done is we have made the column to be of HTML format. Each and every column value is made to behave like a dynamic GO URL using the href tag. The href would basically point to the second report (report5) that we have created and will also pass the year value via “@” symbol. Do note the target value biee2. This is what we will use to point to a particular section. Save this report again.

Now go back to the second chart report and create a narrative view. In the narrative view enter the following

 <IFRAME src=http://localhost:9704/analytics/saw.dll?GO&Path=/shared/Paint+Demo/script+test/Report5 name=”biee2″ height=”300″ width=”200%”></IFRAME>

      

Do note the name(biee2) of the iframe that we have used above. It is the same as the target that we gave in the first report (Report4). This iframe basically points to itself (the compound layout will have the chart view and hence this will show the chart view. Ensure that you are not including the narrative view in the compound layout. Also, enable the HTML markup and restrict the number of rows to one.

Now go back to a new dashboard page and include the tabular view of the first report(Report4) and the narrative view of the second report(Report5) in the dashboard.

      

      

      

Now if we click on say Year 2003 the chart within the iframe alone will modify based on what we have clicked. Very simple but can be useful at times in a lot of situations.

      

This can also be achieved using Dashboard Navigation but in that case all the reports will be run again once and will also filter all the other views.

Advertisements

12 Responses to “Oracle BI EE 10.1.3.3/2 – Drills across Sections in a Dashboard”

  1. Sneha said

    Hi Venkat

    This blog is really very helpful.
    Can you pls tell me what the P0 and P1 denotes?

    [html] @

    Thanks & Regards,
    Sneha

  2. Ahmed Khalifa said

    very good
    but how can we drill to 2 sections in the same page by one click

  3. Steve said

    when I follow the steps above, report4 show @ in the year column instead of the value 2007. I tried many variations and cannot get it to work. What am I doing wrong?

  4. Steve said

    @ is the current column. How can I send multiple columns to the drill report?

  5. john said

    Steve have you solved the issue with the “@” in the year column? if yes could you share it? thanks. my email is pp11pp22@yahoo.com

  6. OBIEEdemo said

    Did anyone got this working? For some reason, there is a missing piece.
    please email me krisusa123@yahoo.com

  7. Sudhanshu said

    I have got a similar requirement where I have two reports – master & detail. I’m able to implement navigation using this method. But I’m facing an issue. In the detail report, it seems it need to show fewe records, only then navigation from master report is possible in dashboard. I had initially put the filters in detail report as ‘is prompted’, but then the query kept running. I changed the filter ‘some date field’ to ‘DEC-9999’ and the dashboard shows no results view for detail report. But when I click a record in master report, the detail report opens in a new window instead of in same dashboard. I then changed the filter to ‘MAR-2008’, and the detail report by default showed few records in dashboard. Now when I click on let’s say ‘FEB-2008’ record in master report, it correctly refreshes the detail report in dash board with new data. But I don’t want the detail report to show any data initially, and only on clicking on master report record, it should show the relevant detail records.

    How its possible?

  8. Nara said

    If somebody solve the @ issue please send me email on nvedre@hotmail.com or leave reply on this site please thanks

  9. Mallesh said

    Hi Venkat,

    Its a great blog, Always your blogs are very much helpful for me. I have a question regarding the drill down feature of charts. When we want to drill down to a summary report from a chart, how can we pass the value as a parameter? I tried using the GoNav in the Data Format column but it is working only for the table view and chart view does not having the drill down/Navigate. If we add it manually to Navigate, the summary report is not getting filtered based on the selected chart item. Please let me know if this is possible to do. Thanks in Advance.

    Mallesh

  10. Raghu Murari said

    i hav got the total thing done.
    i’m doing this for an department table on department name.
    for which when i drill on department name, corresponding employees list should be shown.
    my problem is
    1. if the dept name contains a spaces like ‘Human Resources’, then the URL is taking only Human and its ignoring the rest.
    the URL looks like this
    http://l3f0372:9704/analytics/saw.dll?GO&path=/shared/New/3.Emp&Action=Navigate&P0=1&P1=eq&P2=Departments.%22Department%20Name%22&P3=Human

    2. the drill down can be implemented on varchar datatypes only.
    but not on number types like dept_id. Is there any way to that?

  11. OBI Fan said

    Hi Venkat,

    Very useful information.

    The above scenario is not working for me. I am using OBI 10.1.3.2. When I click on year it is opening the target report in new window instead of Iframe.

    The only difference between the our code is my narrative view does not have the check box saying “Contains HTML Markup”.

    Please let me know if any thing else needs to be done.

  12. Sebastian John said

    Hi Venkat,
    I have followed the drill across sections in the dashboard, but when i click the year the target report is opening in a seperate window but not in the same iframe. Im using obiee 10.1.3.4 version… Reply ASAP…

    Please suggest me how i can solve this issue… Thank you for the useful informations sharing in this blog…

    Thanks in Advance!

    Regards,
    Sebastian

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: