Use parameters to pick two metrics in Visual Analytics Reports

[#1]

My first article outlines how to Use parameters to pick one metric in Visual Analytic Reports. A popular request since posting this article is, how can I pick two metrics using parameters? Thus, in this follow-up post, I want to walk through how you can use the same technique to select two metrics for comparison in your VA report.

In this example, I have both a Line Chart and Bar Chart making use of my selected measures, but you can pick the report object of your choice.

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

The steps are the same but I've bolded where we will need to add a few items to accommodate two metrics:

1. Create the custom category to drive both Drop-Down Lists
2. Create two parameters to hold each drop-down selection
3. Create the two calculated data items to return the selected measures’ values
4. Add the report objects and assign roles

Step 1: Create the custom category to drive both Drop-Down Lists

I am going to use the same technique where we will use a category data item with a high cardinality to build a custom list of measures we want the end user to select from. In truth, you just need the data item to have a cardinality + 1 more than the desired list of measures. In this example, I have 9 measures available to select from, so it is best to use a data item with a cardinality of greater than or equal to 10.

Here is my custom category definition. You can see that I’ve based this category on the data item Product Description that has a cardinality of 104 items.

I’ve named my custom category List of Measures and I’ve highlighted in yellow the measure names I would like the user to select from. This list of measures will be used for both of my Drop-Down Lists.

If you wanted to offer two different lists of measures, then you would create a second custom category.

 

Step 2: Create two parameters to hold each drop-down selection

Now we will create two Character parameters to hold the values the user picks from the Drop-Down Lists. You can leave the current value blank; mine only have values because that’s what I last selected in my report.

 

Step 3: Create the two calculated data items to return the selected measures’ values

Now that we have the parameters to hold the selected measures the user wants to see, we need to create two measures that will dynamically return the selected value.

Since this example has nine measures, my nested IF…ELSE statements are quite long. I will show you both a Text version and Visual version.

If you need a more detailed explanation, check out the original article.

Below is the Text version for the first dynamic measure, Measure 1.

Remember, we want to compare the value the user selected which is stored in the SelectedMeasure1Parameter to each of our 9 measure options. If the comparison is true, then we want to return the corresponding numeric data item.

And here is the Visual version for the second dynamic measure, Measure 2.

Pro Tip: To create the second calculated data item easier, right-click on Measure 1 and Duplicate it. Then go into Text mode and just change the number 1 in the parameter name to number 2.

 

Step 4: Add the report objects and assign roles

Now we can finally design our report. I’m going to show you some additional objects you can use to “check your work” to be sure you are storing the selected value in the parameters correctly and calculating the dynamic measure properly.

Here is a look at how I used some of the report objects to verify that both the parameters and calculated data items return the expected values.

Important: Do not put the Drop-Down List objects in either the Report or Page Prompt areas. This will automatically filter the objects in the report and this is not the behavior we want. We only want the Drop-Down Lists to populate the parameter values.

Drop-Down List Objects

This is where you will assign the custom category we built, List of Measures, to the Category role and assign the parameters we created to the Parameter role.

Text Objects

It is easier to first assign the Parameter role and then add any additional free form text. This is where I am using the Text objects to verify the correct value is being stored in our parameters.

List Table Object

I use the List Table object to display the calculated data items we created using our nested IF...ELSE statements to dynamically return our selected measures. If you are not getting the expected results, check the spelling in both your Custom Category and in the string value of your comparisons in the IF...ELSE expressions.

Line Chart and Bar Chart Objects

Here is what we’re really after. Notice the that I turned off both axis labels as well as both legends for the objects. This is because both the axis labels and legends would use Measure 1 and Measure 2 but these are not very descriptive. Therefore, I used the Text object and color coded the parameter values to serve as the legend.

Text Object

Using the dynamic text feature, I will assign the SelectedMeasure1Parameter and SelectedMeasure2Parameter to the Parameters role to serve as my customized legend. Then I use the data fill colors to coordinate the colors of the parameters' text to match.

Key Points to remember:

No Actions Required

You do not need to configure any Actions between the Drop-Down Lists and the report objects. Remember, the report objects are using the parameter driven values to dynamically return the desired measure.

Format Calculated Data Items

Unfortunately, there is no way to conditionally format the calculated data items. It is recommended to use either the COMMAx.x or NUMERICx.x format.

Different Aggregations Needed

Also, if you want to return measures with different aggregations, please refer to the original article under the Limitations and Considerations section at the bottom.

Different Visual Analytics Versions

The original article uses VA 7.3 and 7.4 to demonstrate the functionality. This post uses VA 8.3. Versions of SAS Visual Analytics 7.3 and up will support this report requirement.

Once you are satisfied that all the pieces are working as expected, here is what the final result can look like: