Look at the report below. Imagine being asked to allow your users to select which Measure, highlighted in yellow, they are looking at: Income, Expense or Profit. This is a frequent report requirement and I’m going to outline just one of the ways you can design your report to satisfy this request using parameters.
In a previous blog, I talked about Using parameters in SAS Visual Analytics reports to prompt users to drive either an aggregated measure or calculated item. This example will allow your users to dynamically select which measures they want to see in their visualizations.
- Create the custom category to drive the button bar
- Create the parameter to hold the button bar’s selection
- Create the calculated data item that will hold the selected measure’s value
- Add report objects and assign roles
Step 1: Create the custom category to drive the button bar
On the Data tab, use the down menu and select New Custom Category….
Next, from the New Custom Category dialogue, select any category that has a cardinality greater than the number of metric choices you want to give your user. In this example, we will allow our report users to select between Revenue, Expense and Profit, so we have 3 options. Therefore, select a category with a cardinality greater than 3, so 4 or more will work.
The next part might seem awkward, but this prevents us from having to load a separate table into LASR. If you have the role and capabilities to load data into LASR then you could load a 3-row table that contains one column with the entries Revenue, Expense and Profit, and you could create your control object from that data source. However, you then create the dependency that both tables must be loaded to LASR for the report to work. The technique outlined in this blog allows us to use just this one data source, but restricts the placement of control object. Using this technique means that you cannot use this control object as a report or section prompt. IF you need to use this control as a report or section prompt then you will need to load a separate table.
Otherwise, follow along.
Name your custom category List of Measures. Next, create the labels of the measures you wish your report users to select from. Again, in my example, I want to allow my report users to pick either Revenue, Expense or Profit. Hint: Double click on Label 1 to rename it then use the New label button to add others.
Next, drag and drop at least one value into each custom category label grouping. The custom category data item will only allow you to save it once each category label grouping has a value. You may leave the radio button default Group remaining values as Other selected.
Finally, click OK to save your new custom category.
Step 2: Create the parameter to hold the button bar’s selection
Now that we have the custom category to feed the button bar’s values, we need to create a parameter to hold the button bar’s selection. From the Data tab, use the drop down menu and select New Parameter….
Select the Type Character and give your parameter a meaningful name. You can leave the current value, or default value, blank. When we assign the parameter to the button bar the value will be populated upon selection.
Step 3: Create the calculated data item that will hold the selected measure’s value
Here we need to create a calculated data item that will hold the value of the selected measure. This is the measure we will use in our table and graph objects. In pseudo code, we want to create a new measure that
If user selects "Expense" Return <Expense>
Else, If the user selects "Profit" Return <Profit>
Else Return <Revenue>
From the Data tab, use the drop down menu and select New Calculated Item….
Then use the Visual or Text editor to create a new calculated item named Measure.
Use nested IF…ELSE statements from the Boolean operators and the x = y statement from the Comparison operators.
Step 4: Add report objects and assign roles
Now we can add our report objects to our report. IMPORTANT: With our technique of using an unrelated category as the source of our custom category we cannot put our button bar in the report or section prompt areas. If we put the button bar in either the report or section prompt areas it would automatically filter the data and we would not get the intended results. DO NOT put the button bar in the report or section prompt area.
In this sample report, you can see I’ve added the Button Bar Control object, the Bar Chart, Crosstab and Line Chart objects to the report area. I want to narrow this section by year, so I have included a Drop-Down List Control to the section prompt area.
Now let’s look at the role assignments for our various objects. Here is what the report looks like before any style enhancements:
Drop-Down List Control Object
Button Bar Control Object
This is where we want to assign our newly created custom category and save that selection to our parameter. This will give us all the categories of our custom category, including the “Other” category.
Next, we will need to add a filter on this object to remove the “Other” category. From the Filters tab, add a filter for the List of Measures category and deselect the “Other” category.
Bar Chart, Crosstab and Line Chart
Use our new calculated item as the measure for each of the Bar Chart, Crosstab and Line Chart.
There is one interaction, where the Bar Chart filters the Crosstab.
And here is what the report looks like after I’ve altered some of the objects’ styles from the Style tab. I colored the selected bar value to coordinate with the rest of the report objects.
In this screenshot, I’ve selected the Promotional Product Line.
As you can envision, this technique can be used for more than just metrics, you could also use this to allow your users to pick a category to use in your objects! Or both.
Just bear in mind that you are restricted to using the Control Objects that support parameters. This includes:
- Text Input
- Button Bar
- Drop-Down List
- Slider (single-point only)
Limitations and Considerations
In the above example, the three metrics I selected, Expense, Profit and Revenue, all use the same DOLLAR15.2 format and the same SUM aggregation. This may not be the case for everyone’s desired metrics.
If you chose metrics that do not share the same format, there is no way to conditionally format the calculated data item. The recommended format to use, in this case, would be the COMMAx.x or NUMERICx.x format.
If your metrics require different aggregations, then you must use an aggregated measure instead of the calculated data item I outline in Step 3 above. In the aggregated measure expression, use one of the Aggregated operators, either simple or advanced, to drive the type of aggregation for each of the returned metrics.
Note: The following screenshots are from VA 7.4 and use a different, but similar, data source while rest of the blog uses VA 7.3.