Today we shall see an example of how to go about reducing the scope of Dashboard prompts to only specific reports. As you would already know, Dashboard Prompts have 2 types of scoping. One is Page and the other is Dashboard.
So, basically you cannot have multiple prompts for the same dashboard column in the same page. Lets look at an approach today to see how we can have dashboard prompts of reduced scope. Before doing that, lets try to understand the scoping of Dashboard prompts. Lets first start with a creating 2 dashboard prompts each pointing to the same REGION_NAME column. Each of the dashboard prompts would set a presentation variable. In my case, i have chose Sample andSample1 as the presentation variable names. Both the prompts would have a page scope.
Now, lets create 2 reports each having the same columns (REGION_NAME and SALES). For one report, let’s create a filter on REGION_NAME to be equal to that of the presentation variableSample and the for the other report, create a filter of REGION_NAME to be equal to that of the presentation variable Sample1.
Now, lets pull both the dashboard prompts and the reports into a single dashboard page. As you would see, when you choose any value for one of the prompts only the second report output changes since the prompt would still call the latest presentation variable Sample1 instead of Sample. This happens because of the page scope of the prompts.
Now, how do we go about reducing the scope of the prompts to only one report. In order to do this, edit both the prompts. In formula section, enter
CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘You can put Anything Here’ END
And convert the Results to SQL Results and enter the below SQL.
SELECT GEOGRAPHY.REGION_NAME FROM SH2
Now, if you go back to the dashboard page, you would notice that both the presentation variables would be set and both the reports would have proper filters applied.
Though we have seen this technique before, we have not applied it in this context earlier. This can be pretty useful in a lot of situations when you do not need multi-select prompts.
Posts
