VA Report Example: Use an Aggregated Data source with passed parameters

In this example, we will continue where this article SAS Visual Analytics: Use a parameter to drive a dynamic reference line leaves off. Specifically, at the end of the article, we have a line chart with dynamic reference line that uses an expression-based parameter and now we want to add a couple of button bar control objects to filter the line chart.

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

Expression-based parameters are evaluated without context to selections made on the page; therefore, do not respond to data queries. To get the expected results and for the control objects to successfully filter both the line chart data and the reference line, we will need to use an Aggregated Data source with passed parameter values.

Create Parameters for Control Objects

First, we need to create parameters to hold the values selected in our control objects. These are the parameter values we will pass to our aggregated data source. In our example, we have two category driven button bars, so we will need to create two character parameters.

Here are the steps:

Select the Facility Region button bar
Open the Actions pane
Select the drop-down button under the Parameter Links group
Click + New parameter

You will see that a parameter is automatically created for you with the name Facility Region Parameter and it is selected as the parameter link for this button bar.

If you expand the properties for the parameter in the Data pane, you can see that type of parameter is character and there is a description of how the value is set.

Repeat the same steps for the Product Line button bar.

You will see both newly created, and linked parameters in the Data pane.

Create Aggregated Data Source with Passed Parameter Values

Now we can create an aggregated data source and pass the selected control object values via parameters. Using the Data Source menu, select New data from aggregation of MEGA_CORP.

First, move over the data items we need to calculate the dynamic reference line:

Date by Year
Date by Month
Facility Region
Product Line
Expenses

Second, use the + New filter button to create an advanced filter where Facility Region = Facility Region Parameter AND Product Line = Product Line Parameter.

The aggregated data source is now linked to the control objects via the parameters. This means that every time the values in the control objects change, the aggregated data source will requery and apply the new parameter values. Bear in mind that when the report is opened, the aggregated data source is created as a temporary table in the user’s CASUER CAS library and updated in the same location. This is done for every user that views the report.

Create Reference Line Value using Aggregated Data Source

Now that we have the correct subset of data for our aggregated data source, we can create the new reference line expression. It’s the same expression as we defined before, but we are using the measures from our aggregated data source. It is helpful to add a prefix to your data items in the aggregated data source so you do not get confused.

Sum(_ForAll_, AggDataSourceExpenses) / Distinct(_ForAll_, AggDataSourceDatebyMonth)

Just as a reminder, here is the reference line expression for the original data source.

Create Expression-Based Parameter to drive Dynamic Reference Line

Now we need to create a new expression-based parameter to drive the dynamic reference line to either use the value for all of the data or a subset of the data. To do this, we will check if the control objects have values selected, if they do, then we will use the aggregated data source reference line. Otherwise, if no values are selected, then we will use the value for all of the data.

Again, we are using the aggregated data source to workaround how expression-based parameters behave in that they do not filter based on any control objects. They evaluate their value for the entire data source. So, we created a subset of the data source by creating the aggregated data source.

Here is the final parameter we need to create to use in our line chart.

Assign New Expression-Based Parameter to Reference Line

Select the line chart object and then using the Options pane assign the expression-based parameter we just created to be the reference line.

See a demonstration here.

(view in My Videos)

Conclusion

Now you know how to combine an aggregated data source and expression-based parameters when you need a value to respond to data queries driven by control objects. The expression-based parameters are not being filtered, but you are first subsetting the data, and then the expression-based parameter is being evaluated again the subset of data.

For further information:

Find more articles from SAS Global Enablement and Learning here.