If you had gone through my previous blog entry here, you would be knowing how to go about dynamically varying the columns in a report. But one drawback that i mentioned there was, one had to manually enter the column names and any wrong entry would give out an error. Now lets look today how to go about achieving a drop down of column names. Though this can be achieved easily using column selector, the approach that we shall see today would affect all the reports in your dashboard (if you want). This is just an extension of what we saw the other day here. So, our aim is to basically have a list of columns in a dashboard prompt and based on what we choose in the dashboard prompt, all the reports should change having the selected column. Lets start with building the dashboard prompt first. Our aim is to make the dashboard prompt display 2 columns, GEOGRAPHY.COUNTRY_NAME and GEOGRAPHY.CITY_NAME. So, use any column in the dashboard prompt and in the function window enter the following function,
CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘Variable Column List’ END
We are including the above formula in order to ensure that the dashboard prompt does not affect any column.
In the SQL Results section enter the below formula. Also make the dashboard prompt to set a presentation variable say Geography. Ensure that you have any one of the column names, for example, GEOGRAPHY.CITY_NAME as a default.
SELECT CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘GEOGRAPHY.COUNTRY_NAME’ END FROM SH2 UNION ALL SELECT CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘GEOGRAPHY.CITY_NAME’ END FROM SH2
Now lets create a new report whose one of the columns would be either CITY or COUNTRY depending on what we choose in the dashboard prompt and the other column would be sales. In order to vary the first column, enter @{Geography} without the single quotes.
Now include the dashboard prompt and the report in a dashboard page. Now you would notice that when we change the value in the dashboard prompt, the corresponding column in the report changes.
This can be useful in situations where you would like to change the columns across many reports. If all you have is a single report, then you would be better off with a single column selector view.
Posts
