SAS Visual Analytics: Exploring new expression-based parameter functionality
New report parameter functionality is here, and it’s a gamechanger in Visual Analytics! You can now specify an expression as the value of a parameter. This expression can be based on data items, functions, operators, or other parameters. You can even apply filters and ranks to the parameter expression! This allows you to accomplish tasks that were previously difficult or impossible. In this post, we will explore various use cases for these parameter enhancements. For more see the SAS Documentation: Working with Parameters in Reports.
Use Case 1: Display Rule with a dynamic threshold
In this example, we will create a display rule that will highlight rows in a list table. The data source is the SASHELP.CLASS table. It contains one line per student. We display the name and height of the student in the list table:
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
We want to pick the best players for a basketball team by selecting the tallest students. For this, we want to create a display rule highlighting rows of students with a height above the 75th percentile. This involves two steps:
- Create a parameter with the value of the 75th percentile of height for all students.
- Use the parameter in the display rule of the list table.
We create the parameter using the +New Data Item button in the Data menu and select Parameter.
In the New Parameter dialog box, give it a meaningful name, leave the type as numeric, and then click on Edit as expression:
Here, enter an expression for our 75th percentile of height:
We have now created a parameter with an expression-based default value! To verify the current value of the parameter (the 75th percentile of height) , we can hover over it in the data menu:
We can see the value is 66.5 inches. We will now use this parameter in a display rule for the list table, which displays Name and Height:
To select the parameter, in the Value drop-down, use the arrow to select the parameter name from the available list of values.
The resulting list table is:
The great thing about this is that it is dynamic. When our students grow taller or new students join the class, the parameter and thereby the display rule is automatically updated!
Use Case 2: Setting the initial value
Another use case for an expression-based parameters is setting initial values to a control. In this example, we have a slider page control to select a date range, which filters the page. We want the user to select a range between the most recent date (in the data) and ten days prior. We can link an initial value of a control to an expression-based date parameter. Note that it is no longer necessary to hardcode a minimum and maximum value for a date parameter, which was a previous requirement.
If you’re following along: we are using the SASHELP.SNACKS table for this example. I create a list table of Product Name, Date of sale and Retail price of product. This is the table we want to interactively filter.
As we will see below, we only need one parameter. The most recent, or maximum, date is a predefined option we can directly select and we don’t need to create a parameter for it. The parameter we need, named Ten days prior, has the following expression:
Now, in the data menu on the left, right-click on Date of sale to create a page control. This should add a slider to the report. Next, from the Options pane under the Slider group, select the parameter, Ten days prior, as the Initial min value and select Maximum value for the Initial max value.
Note that Maximum value is not a parameter, but a predefined option. Voilà, we now have an interactive slider filter that initially has a the most recent date minus ten days selected:
Interlude: parameters are not influenced by interactive or object filters
Before we go on to the next use case, I want to emphasize that parameter values are not affected by interactive filters and object filters. They are, however; affected by data source filters. To demonstrate this, I will use the SASHELP.CLASS table. I create a new data item called Number of Students with the following expression:
I will also create a numeric parameter with the same expression:
We are essentially counting the number of distinct names. Assuming there are no duplicate names we are counting the number of students. We will then assign them to separate text objects. I will also add a page control for Sex. Note that when I select Female in the page control, which acts as a filter for all of the objects on the page, the data item calculation is filtered, but the parameter is not:
We will use the fact that parameter values are not affected by interactive or object filters in the next use case.
Use Case 3: Percent of Total
Many customers use percent of total as a metric to gauge how well their measure is performing when compared to the whole. In this example, I will use the SASHELP.ORSALES table and I want to calculate the percent of total sales for each product group. I will create a data item called Profit Percent of Total (Data Item) with the following expression:
We will also create a parameter called Total Profit with the following expression:
Note we cannot directly display a parameter in a list table (or many other objects). Therefore, we reference the Total Profit parameter in a new data item that we will call Profit Percent of total (using Parameter):
Note that if you substitute the expression of the Total Profit parameter into the expression of the Profit Percent of total (using Parameter) you get the same expression as Profit Percent of Total (Data Item). However, recall that parameters are unaffected by interactive and object filters. It will therefore be the Profit Percentage over all Product Lines – unaffected by any interactive or object filters.
To bring it all together, we create a page control of Product Line:
We also create a crosstab with Profit Percent of Total (Data Item) and Profit Percent of total (using Parameter), and Product Category and Product Group. I switched on totals in the Options:
I see that both have the same values:
However, when I select a value in the page control, for example Outdoors, they are no longer the same:
Note: the total for Profit Percent of total (using Parameter) is 22.68% - illustrating that the denominator of the profit calculation is still over all product lines – unaffected by the page control. In other words, the Outdoors product category accounts for 22.68% of total profit across all product lines – which Profit Percent of total (using Parameter) correctly reflects. Therefore, if you want your percent of total calculation to be unaffected by object or interactive filters, create a parameter for the denominator of the percent of total calculation!
You can use the same principle in many other ways. For example, you could use the aggregated measure with parameter to set the target value of a gauge – unaffected by object and interactive filters.
Conclusion
The new feature of being able to define an expression-based parameter is a very powerful tool that can be used in a variety of ways. Let your creativity run wild! This isn’t all. In the next part, we’ll cover the following examples:
- Parameter value based on a selection in another control object, using the IsSet function
- Using character parameters with the new expression definition
Find more articles from SAS Global Enablement and Learning here.
