3 steps to create dynamic reference line labels in SAS Visual Analytics
When creating reports in SAS Visual Analytics, I often use reference lines in my graphs. However out of the box, reference lines are static additions to VA report objects. After adding a reference line to an object, you have to manually type in its value and label. While this is a nice feature, it is not optimal because the reference line's location will remain in the same spot regardless of whether the graph is filtered, highlighted, etc.
This communities post suggested way around this is to create a custom graph that starts with a bar chart and line chart, after which you can use the line chart as a 'reference line'. Very clever idea! However the one drawback of this is that there is no way you can label your reference line.
Well turns out there IS a way to label your reference line using a different type of SAS Custom Graph. Not only that, but you can also have the reference line's label CHANGE based on the different filters that are placed on the graph. Take a look at the example report below, which visualizes a retail sports and outdoor company's yearly profits by product line.
In the report above depending on which product line is selected, not only does the reference line's location change but the reference line's label also changes! This is something that VA report developers have been looking to do for quite some time. Now, thanks to the SAS® Graph Builder this dynamic reference line and label can easily be added to SAS Visual Analytics Reports! In this article, I'll show you how to do this.
Step 1: Build a custom graph
Begin by opening the SAS® Graph Builder and dragging a "Bar chart" onto the center pane. Then also add a "Series Plot" to your custom graph. The bar chart will be the main graph for our visualization, and the series plot is going to act as our reference line.
In my previous post about using the "Data Labels" role, I described how you can place text values from your data source into SAS custom graphs. We are going to use the same method here to add our dynamically changing label to the reference line.
Go to the "Roles" menu from the right side of your screen and click "Add."
In the "Add Role" menu, select "Data Label" for Role Type and Name the role "Reference Line Label."
Press "OK."
For the rest of the roles in your custom graph, rename them as follows:
Shared Role1 = X Axis
Series Plot 1 Y = Reference Line Value
Bar Chart 1 Response = Bar Chart 1 Response
Your custom graph is complete! Save your custom graph and give it a name.
Step 2: Prepare your data
For this post's data, I'll be using the orsales.csv file which is available on the SAS® Viya Example Data Sets website.
After importing the orsales.csv file into SAS, it initially looks like this:
First we will need to summarize the data based on what we want our report to show. In the example at the top, the bar chart's category (or x-axis) is "Year" and the response variable is average "Profit". Also the bar chart is filtered by the "Product Line." So we will preform some data preparation steps to get the data into shape for this visual.
After performing this first step of the data prep, the output data will look like this:
Next we need to add two new columns. One will be the reference line's value and the other will be the reference line's label. The value of the reference line is simply the average of the product line's "Profit" across the values of the bar chart's category (or "Year"). For the reference line's label, I simply took the number from the reference line's value and converted it to a category and concatenated the word "Avg:" in front of it. Also, as we learned in my previous post, we only need to locate the reference line's label in one place on the graph. I chose the first year (1999) in the bar chart as the location for my reference line label.
After performing this last step of the data prep, the output data looks like this:
The ETL to do these two data preparation steps is included in this article.
Now that we have both our custom graph and our report-ready data, we can now build our report!
Step 3: Build the report!
Start by importing the output dataset 'labeled_reference_line' into the report and converting the numeric "Year" column from a measure to a category.
Let's begin by creating a tab level control. From the data pane on the left, drag the "Product_Line" onto the top tab. It will create a button bar.
Make this a required control by selecting the checkbox in the bar's options menu:
Next import your new custom graph to a Visual Analytics report. For the custom graph's roles, apply the following assignments:
And you're done! You've created a bar chart which displays the average profits for each year that will dynamically change depending on which product line is selected. However you also have a reference line and label which will change based on the selected product line:
For my final report I adjusted the report-level options to acquire the colors and font in the example above.
How to make this example work for you
This example was created in SAS Visual Analytics 8.3. Again, for this post's data, I used the orsales.csv file which is available on the SAS® Viya Example Data Sets website.
The attachments for this post are:
- The code to create the report ready data - Dynamic_Reference_Line_ETL.sas
- A JSON file containing the custom graph and final report - Dynamic_Reference_Line.json
Import the data on your SAS Visual Analytics instance. Import the report via the "Import via GUI" section of these instructions.
