SAS Visual Analytics: Use a parameter to drive a dynamic reference line

1 Like

Released in Stable 2025.12 is the ability to assign a parameter, including an expression-based parameter, as a value of a reference line. There are also new options for the line style, width, and color.

This means we no longer need to create a new custom graph to be able to assign a dynamic reference line (see article here for that technique) and we now have the option to either hard code a numeric value or use a parameter to accommodate new data as the source updates.

Example Dynamic Reference Line

Let’s take a look at an example of how this can be used. In the screenshot below, I show the following:

Category: Date by Month
Measure: Expenses: the sum of expenses per month
Measure: Avg Yearly Expenses: the sum of expenses for the year divided by the number of months for the year
Hidden: Date by Year
Dynamic Reference Line: Expenses Avg (All): the sum of all the expenses divided by all of the months

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

You can configure the dynamic reference line by expanding the Options pane, expand the References Lines group and then click + New reference line.

Label: If you like, add a custom label to the reference line
Value: Create a new parameter on the fly, or you can select an existing parameter from the report. Notice in my screenshot I have two parameters, one from the data source MegaCorp and one from the data source InsightToy. Remember that parameters are global to the report and you can even select a parameter to drive the dynamic reference line from a different data source!
Line style: Select from 23 different line styles
Line thickness: Select the line thickness
Color: Select from either the basic or type your custom color

Expressions Used in Example

The easiest way to ensure you get the exact line chart you want, with the correct values, is to first view the data in a list table. The aim is to create expressions to return the exact values you want plotted on the line chart. Remember, Visual Analytics objects aggregate data, so you want to be sure the measures you want plotted aggregate accurately.

For a measure role in the line chart, the aggregation is performed at the group by level. Reference lines are aggregated for all the data. On top of that, the behavior for an expression-based parameter is to also aggregate for the entire data source. Note that an expression-based parameter is independent of data queries for the objects in the report, which means that it will not filter based on control object selections. For more information, see the SAS Documentation: Working with Parameters in Reports.

We need all of the expressions to be dynamic to automatically account for data updates.

Distinct Month Count

This calculation is used to evaluate the Avg Yearly Expenses. It is used as the denominator to divide the year’s sum by the number of months in that year.

AggregateTable(_CountDistinct_, Table(_CountDistinct_, Fixed('Date by Year'n), 'Date by Month'n))

Avg Yearly Expenses

This is the orange line in the line chart. This expression calculates the year’s average expenses. It will evaluate the sum of the year’s expenses divided by the number of months in the year. The denominator, Distinct Month Count, was shown above and will be used in this expression.

(AggregateTable(_Sum_, Table(_Sum_, Fixed('Date by Year'n), Expenses))) / AggregateTable(_CountDistinct_, Table(_CountDistinct_, Fixed('Date by Year'n), 'Date by Month'n))

Expenses Ref Line

This expression will be aggregated for all the data. Therefore, for our list table, we will want to see the same number for every row of data and we want to be sure it is not additive and also evaluates to the same value for all data. This is where we can use the ForAll context.

Sum(ForAll, Expenses) / Distinct(ForAll, 'Date by Month'n)

I usually test this expression in two ways:

Parameter Expenses Ref Line

Now we can create a new expression-based parameter and copy in our reference line expression. You cannot add a parameter to a list table, therefore, it’s easy to create an aggregated measure to test and verify we are getting the desired result.

Final List Table Check

The last check I like to perform is to make sure the list table returns the expected values with the Date by Year category hidden. This is because I do not want to display this value on the line chart but the data item needs to be included in the object so that the AggregateTable function can evaluate properly.

Create Line Chart

Now we can create the line chart by using the data items we created.

Remember, expression-based parameters do not respond to data queries, so if you wanted to add control objects to filter your data, you will not get the expected results.

This can, however, be accomplished using an aggregated data source and passing parameters. This will be covered in the next article.

Conclusion

Enjoy the new feature to be able to select a parameter to drive a dynamic reference line. This is only one example of how you can implement an interesting use case in viewing several types of averages over the course of time.

For further information:

SAS Documentation: Working with Parameters in Reports
SAS Graph Builder: Custom Line Chart with Reference line for Visual Analytics reports: article | YouTube
Visual Analytics: Exploring new expression-based parameter functionality
Visual Analytics: Understanding the AggregateTable Function for Beginners
The AggregateTable operator... It’s the bee’s knees!
SAS Visual Analytics Advanced Calculations (part 2 of 4): AggregateTable

Find more articles from SAS Global Enablement and Learning here.