Business Intelligence – Oracle

Oracle BI 10.1.3.3/2 – Selecting Reports from Dashboard Prompts and Guided Navigation Sections

Posted by Venkatakrishnan J on January 17, 2008

While i was away on vacation, a set of pretty good questions came up as comments in the Contact Me page of this blog. Though i never expected this page to get any comments at all, it looks like i was wrong. So, apologies to everyone who have been waiting for a reply from my end. I plan to make a reply as soon as a comment is made from now on. Among those comments, one user had a question on how to go about creating a dashboard prompt to list down specific report names and while selecting the report names the user wanted to show those corresponding reports within the dashboard. I have had this question asked before. Though it is not pretty straight forward to achieve this, lets look at one of the possible approaches today. In order to do this, lets start with a very simple example. You can modify this to suit your needs. In our example, our aim is to make the dashboard prompt to show two report names ‘Brand Sales’ and ‘Region Sales’. Based on what we select in this dashboard prompt we would be showing either the Brand Sales Report or the Region Sales report in the dashboard. Lets start with creating the dashboard prompt. For now we shall be hardcoding the values in the dashboard prompt.

So, create a dashboard prompt on a column (though this column will not be actually used. This is just to trick the BI Server) and in the formula enter the below case statement.

      CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Brand Sales’ END

As you see the idea is not to filter on the actual column but instead on a dummy value of Brand Sales.

      

Once this is done, go to the Show drop down and go to SQL Results. In the SQL Results enter the below SQL. Also, make the prompt to set a presentation variable say Report

      SELECT CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Region Sales’ END FROM SH2 UNION ALL SELECT CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Brand Sales’ END FROM SH2

      

As you see above, what we are basically doing is making the dashboard prompt to print our Report Names and then we are making the presentation variable to accept these report name values. Now the next step is to create 2 reports that each will return rows for one of the above selections and zero rows for the other selection so that we can use this as a source for our guided navigation section.

      

      

      

What we are basically doing above is creating a report that just has a column with a dummy value Region Sales. And a filter is applied on this report with the presentation variable Report so that this report will return non-zero rows when we choose Region Sales value in the dashboard prompt. Similarly this report will return zero rows if we choose Brand Sales value in the dashboard prompt. Create another report as shown above but in this case make it to return non-zero rows when Brand Sales is chosen.

Now go to the dashboard and include the dashboard prompt that we created in a normal section. Now included 2 other sections below this dashboard prompt and each of these sections would be a guided navigation section. Include the region sales report in GN section and Brand Sales report in the other GN section. Go to the GN section properties containing the Region Sales Report and in the source, reference the report that we created above and make it to show only when it returns some rows. Similarly, for the GN section containing the brand sales report, reference the second report that we created above and make it to show only when it returns non-zero rows.

      

      

Now if you go choose Brand Sales in the Dashboard prompt, you would see that the Brand Sales report would be listed. Similarly if you choose Region Sales, then the Region Sales report would be shown.

      

      

The idea is pretty straight-forward. But this can be used especially if you want to allow your users to choose multiple reports.

About these ads

8 Responses to “Oracle BI 10.1.3.3/2 – Selecting Reports from Dashboard Prompts and Guided Navigation Sections”

  1. [...] JDeveloper 11g and Web Service SOAP APIsContact MeOracle BI EE 10.1.3.3/2 – Top 10 Common ErrorsOracle BI 10.1.3.3/2 – Selecting Reports from Dashboard Prompts and Guided Navigation SectionsAbout MeOracle BI EE 10.1.3.3/2 – Merging RepositoriesOracle BI EE 10.1.3.3/2 – Write Back Option – [...]

  2. A Shah said

    Venkat
    This was a very useful post and thank you for this wonderful blog. I implemented the above mentioned technique to create a dashboard prompt with list of report names. However, I have around 20 reports in the list and soon as I go to the dashboard page with this prompt, SQL for all the reports on this page gets generated on the backend. Even when I select one of the reports from the drop down list, I see many other SQL still running on the backend. Since our report list is long, this puts a lot of load on the database. Is there a way to stop the queries from being generated and wait for the user’s response? I would appreciate your reply.

    Thank you,
    A Shah

  3. Jeremy said

    Hi Venkatakrishnan,

    This is a wonderful tip. I was wondering if there was another way of achieving the report selection from prompt. In this example…it appears two reports are being aggregated. The aggregated report is then filtered by the dummy column.

    Is there a way to prompt a specific report to minimize computer overhead? I have to reports built…one with a high level view, and a second that goes into granular detail. By default I would like the high level report to be displayed, but if the chooser needs to get to the detail level, the user may prompt the second report. Is this possible?

    Regards,

    Jeremy

  4. Srikanth said

    @ Jeremy,

    If i understood the problem corrrectly, Using the View selector will solve the issue. Place the default report which need to be appear as the Top View in the Views Included List

  5. Jeremy said

    Hi there,

    It’s been months since I last posted here. At the time, I was a completely new user to BI. That said, with a few months under my belt, I’ve come to the realization that this is the exact solution I was looking for. Srikanth, I actually was using the View Selector during this period, but there was a lot of overhead toggling between the views of the various pivot tables. In any case, thanks for your response.

    J

  6. Jeremy said

    With regards to this design, I’ve noticed the same as “A Shah” in terms of performance. I was expecting this solution would minimize the overhead on the server by allowing the user to call a specific report from a list. However, the system seems to do a great deal of pre-processing with each query (“Preparing Guided Navigation” one for every report in the list). Is there any other solution that just processes the single report required? Assuming the user is looking to setup different views (say from High to Low level), it would appear using a combination of multiple pivots and view select is a better alternative. However, if the reports are completely different, then this may be the best solution.

  7. Vazir Ahamed said

    I’m having two reports I have applied guided navigation on both. Based on the guided navigation results it will show the report which will be any one. But at the background both the requests are passing to the datasource only BI server is show hide the reports. Is there a way not to request to teh data source.

  8. Jigar said

    Hi Venkat,

    This seems to be nicely written method to do so. But is there a workaround in similar lines for siebel analytics 7.8 ??

    Thanks

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: