Wednesday, January 2, 2013

Avoid running report for All Values on Dashboard when using Multi-select Prompt


While using Multi-select as prompt, run the report on Dashboard the report will run for all values initially. But customer’s requirement is: Not to show all the values before running but has to show some custom message, like; Select Prompt values first and Run the Report
One way is: With setting Default values which are not existed in db for prompts. Here, i am explaining the procedure when customer don’t want to see any default values for prompts and still want to see Custom Message before selecting any values from prompts.
Let’s say, your dashboard is containing these 2 objects, Multi-select prompt set and a Report.
So, in my case, there are few multi-select prompts as shown:
Multi-Slct Prmts
And, Report Criteria may look like:
Report Criteria
If you observe Filters part of Criteria, all prompts related to respective columns are set to “is prompted”
Dashboard shows all values in the report when you navigate to particular page containing those 2 Catalog objects (Prompts, and Report)
Instead of showing all values by default, we need to show some custom message which says user to select the prompt values and run the report.
Here is the work around:
1.  Build a report with only those columns used in Prompt set. In this example those are, “Prod Subcategory”, “County Region”, & “Calendar Year”. And it’s filter set should look like:
Changed Filter Set
Observe that, i set a filter condition on all columns to non-existed database values instead of “is prompted”, and used logical Operator OR.
2.  Save this report as Intermediate Report
3.  Click on Results Tab of this Request.
4.  Edit your “No Results” View to show the message initially when you click on particular page of dashboard. Write the message there.
No Results View
5. Save this report
6.  Build a dashboard in such a way that each section contains each catalog object related to this page. Section 1 contains multi-select prompt, Section 2 contains Original Report to be shown, and finally Section 3 contains Intermediate Report.
Edit Dashboard
7.  Mention Guided Navigation properties for Section 2 and Section 3. i.e. Guided Navigation using Conditional Sectionstype.
8.  Click on Properties of Section 2 then Guided Navigation… and browse the Intermediate Report.
Section 2 GNP
9.  Do the Same thing for Section 3 also. (FYI, here Source Request and Target requests are same). But this time, select if request returns nothing radio button.
Section 3 GNP
10.  Save the Dashboard and see the Results. You will be watching the No Results View Message initially here, instead of showing all values of report. (Here you have not selected any prompt value yet..)
Results on Dashboard2
Here, Source Request i.e. Intermediate Report doesn’t return any rows. So, Section 3 content will be shown on dashboard.
11.  Now, select any prompt value and Run the report. This time, Intermediate report filters are override by the prompt values and it returns some rows. So, Section 2 content i.e. original report will be appeared on dashboard
Note: This will not work in the following Case like, you have set some prompt values and run the report. Clear all the prompt values here and run the report, now it shows all values.
So, it works when and as you click on the particular Dashboard Page.