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

I have a requirement like when User selects value from a prompt it has to display different reports in dashboard. Prompt contains values rather than columns .. so i couldn't use Column selector. and at a time only one report to be displayed.If user selects one from prompt it has to display first report. if user selects two it has to display the second report

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.