Recently I was approached with a request to implement ‘REPORT SELECTION” using dashboard prompts.
Basically, the user is given the choice using a drop-down list to select the report that he wants to see on a dashboard page.
Here is a snapshot of the functionality of the prompt.
When the user selects the QUANTITY from the prompt, he sees report #1 and when he selects SALES he sees report #2
We will implement this functionality using Guided Navigation sections.
The first step is to create a dashboard prompt that will give the user the choice of the report to select. In show SQL section of the dashboard prompt enter the following SQL.
SELECT CASE WHEN 1=0 THEN REPORT_ALIAS.DUMMYCOLUMN ELSE ‘QUANTITY’ END FROM CUBE_DATA
UNION
SELECT CASE WHEN 1=0 THEN
REPORT_ALIAS.DUMMYCOLUMN ELSE ‘SALES’ END FROM CUBE_DATA
Here, the dummy column is actually not being used in the prompt, but serves us to define the two report names QUANTITY and SALES as values in the drop-down of the dashboard prompt.
We will create a presentation variable named REPORT_NAME for the above prompt.
The next step is to create source requests for the guided navigation section. We need to create a request that will return non-zero rows when the first value in the prompt that is ‘QUANITITY” is chosen and zero rows when the second value that is “SALES’ is chosen. Similarly we will create another source request that will return rows for whenSALES is chosen and zero rows when QUANTITY is chosen.
SOURCE REQUEST #1
For this let us create a request that will return one column with value ‘QUANTITY’ and apply the presentation variable REPORT_NAME that we created earlier. So what this does is, when the value of the presentation variable is equal to ‘QUANTITY’ then this source request will return rows.
SOURCE REQUEST #2
This request will return one column with value SALES. We will apply the presentation variable REPORT_NAME as filter so that when SALES is chosen from the dashboard prompt, this request will return non-zero rows.
Now we will create sections in the dashboard that will include the report selector prompt, and the QUANTITY report and SALES report in the other sections. The sections that contain the reports will be guided navigation sections. We will reference the source request we created above for each of these sections and configure it to show the section if the request returns non-zero rows.
Now on the dashboard when the user selects ‘QUANTITY’, he sees only theQUANTITY report and when he selects SALES he see the SALES REPORT.
Posts
