I got a question the other day with regard to passing dashboard prompt values to multiple reports from different Subject Area’s. The user’s questions was “If i have a dashboard prompt on a column from one subject area, will i be able to restrict the data on a report from another subject area?”. Thought the question is pretty straight forward, i thought this warrants a detailed explanation of how the dashboard prompts work. As always, lets understand this through an example. We shall be using the default Paint and Paint Exec subject areas. Also, as we move along we will build more subject areas to see the impact of a dashboard prompt on 4 different reports from 4 different subject areas. Lets first start with creating a simple dashboard prompt on the year column of the Paint subject area.
Note down the Presentation Table Name and Presentation Column Name of the above prompt. It is“Periods”.”Year”. Now, lets go to the Paint Subject area and create a simple report containing Year and Sales. Ensure that you have a “is prompted” clause filter on the Year column.
Similarly, create the same report using “Periods”.”Year” column and the sales measure column on the Paint Exec subject area. Also add the is prompted clause filter on the year column. Now, put the dashboard prompt and both of the reports in a dashboard page. Now, if you change the year value in the prompt, both the reports would get affected.
Now, you have noticed that both the reports get filtered because the Presentation Table and Presentation Column names for both Paint and Paint Exec Subject areas for the Year column are the same. Now, lets create a copy of the Paint Subject area and rename the Presentation Table of the Year column to something like “Periods New”.
Now, create the same report as shown above i.e Year and Sales report with an “is prompted” clause on the Year column. Add this report to the dashboard. Now, if you notice, the filter would not be applied to the third report.
This would be the typical use case. One would have different subject areas with different names and we would like to apply filter from a dashboard prompt on one column to a report on another column in another subject area. So, in order for the filter to be applicable without changing presentation Table and column names, we have something called as “Aliases”. In our case, add an alias to the Periods New presentation Table with same Presentation Table name of the column that was used in the prompt. In our case it is Periods.
Just by adding the filters, the filters would not be applied. Go back to the 3rd report we created. In the column formula of the year column (this is important. Aliases would not be resolved internally. We would have to put their names in the formula explicitly), change it from “Periods New” to “Periods”. Now you would notice that the filter would be applied on the 3rd report also.
In our example, all our subject areas come from a single BM. What if we have completely 2 different subject areas from 2 different Business Models? The above would work even in that case too. Ensure that your reports refer to the aliased columns instead of their original column names. The below screenshot shows a 4th report (SA from a different BM) filtered using the same technique.
So, in a nut shell, if you want a dashboard prompt filter to be applied to reports sourcing different BM or SA’s you basically would have to add an alias of the dashboard promt column’s Presentation Table name and the Presentation Column name to the report columns. This is not applicable if you are using presentation variables. Aliases are not required if you are filtering reports by presentation variables.
Posts
