17
November
Selecting columns dynamically
November 17, 2016
In previous post I talked about column selectors. They are not enough in complex cases, for example:
- Single selection of columns for several reports.
- Using the same column selection in several columns of the same analysis.
- Dependency between selection of columns (for example, I can select “Target Revenue” in measures only if “Year” selected in one of the dimensions.)
In this post I will create an example where selecting a column from prompt would influence 2 columns in analysis.
First I create a Dashboard Prompt:
All I want is to create a presentation variable, I will name P_col1, so I add a new Variable prompt (and not a Column Prompt).
There I will select Presentation Variable and it’s name (P_col1) the Label and the user input format. Here you can select whatever you like, just make sure user can select only one value. I selected a “Radio Buttons” option.
Next I add Custom Values. It is important that those Custom Values will be exactly the same way those columns appear in the column Formula. For example this is the Formula of the “Offices”.”Department” column:
and this is the Prompt definition (I selected the Year, Product Type and Department columns):
The last thing I did was to select a specific value as default selection (this is important).
This is the resulting prompt:
Now i will create an Analysis that uses the above described presentation Variable and place them both in a Dashboard.
First I will create an equivalent to a column selector:
In the formula I create a presentation variable with a default value:
the result in my case is: @{P_col1}{“Time”.”Per Name Year”}
I’ll add few more columns:
And this is the result:
I could do exactly the same with column selector. Now, for fun, lets add something I couldn’t do with column selector: I’ll add a measure that will show “Discount Amount” if the “Per Name Year” was selected and “Billed Quantity” otherwise.
So my extra column will have the formula:
CASE WHEN ‘@{P_col1}{“Time”.”Per Name Year”}’='”Time”.”Per Name Year”‘ THEN “Base Facts”.”Discount Amount” ELSE “Base Facts”.”Billed Quantity” END
This part I couldn’t do with column selectors.
Now I’ll place both in a dashboard:
You might have noticed the first column name didn’t change after I selected the “Product Type”. We didn’t have this issue with column selectors.
To fix it I’ll place the Presentation Variable in the Column Heading:
Now the result header is dynamic:
While developers might be happy with the described above, users and UI / UX people might not share my enthusiasm with headers and Prompt Values such as “Time”.”Per Name Year” and column header such as Measure2.
No Comments