We just had another interesting question today in one of our internal forums. The question was how do we dynamically vary the columns in a Dashboard Prompt based on a selection in another dashboard prompt. Let me explain a bit further. For example, lets assume that we have 2 dashboard prompts. One has values like Region Sales and Brand Sales (these are hard coded values, same as what we saw in the previous blog entry). Now the requirement was, whenever Region Sales is chosen in one prompt the values in the other prompt should change as to show the values of only category names (CATEGORY_NAME). Whenever Brand Sales is chosen in the 1st prompt the 2nd prompt should show only the product names (PRODUCT_NAME). I can envision a similar requirement in many scenarios where one might want to have a single page wise dashboard prompt that would filter all the other prompts within the dashboard whenever a selection is made. In order to achieve this, lets start with the first dashboard prompt where we shall print dummy values ‘Region Sales’ and ‘Brand Sales’. Make this dashboard prompt to set a presentation variable say Report. For more details on how to achieve this refer my blog entryhere.
Now lets start creating the sub-prompt whose reference column would change based on what is chosen in the first prompt. In order to do this, lets include a dummy column (whatever we choose in the dashboard prompt will not affect any columns. In order to use this prompt we can only use presentation variables) by entering the below case statement in the formula tab.
CASE WHEN 1=0 THEN PRODUCTS.CATEGORY_NAME ELSE ‘Dummy unused Column’ END
Now in the dashboard prompt, convert the results to show only SQL Results and enter the below SQL.
SELECT CASE WHEN ‘@{Report}’='Region Sales’ THEN PRODUCTS.CATEGORY_NAME
WHEN ‘@{Report}’='Brand Sales’ THEN PRODUCTS.PRODUCT_NAME ELSE ” END FROM SH2
So basically, we are passing the presentation variable that we used in the first prompt to the second prompt. Lets now look at the final output after including both the prompts in a dashboard.
Lets first choose Region Sales and see what happens to the second dashboard prompt.
Similarly, lets choose Brand Sales and see what happens.
The only disadvantage of this approach is that the users would have to hit the GO button to make the dashboard prompts to change. But again this can be very helpful in situations where you want a single dashboard prompt to control the output of multiple dashboard prompts.
Posts
