4月 282016
 

Look at the report below. Imagine being asked to allow your users to select which Measure, highlighted in yellow, they are looking at: Income, Expense or Profit. This is a frequent report requirement and I’m going to outline just one of the ways you can design your report to satisfy this request using parameters.

In a previous blog, I talked about Using parameters in SAS Visual Analytics reports to prompt users to drive either an aggregated measure or calculated item. This example will allow your users to dynamically select which measures they want to see in their visualizations.

parameters to pick your metric in Visual Analytics Reports1

Steps

  1. Create the custom category to drive the button bar
  2. Create the parameter to hold the button bar’s selection
  3. Create the calculated data item that will hold the selected measure’s value
  4. Add report objects and assign roles

Step 1: Create the custom category to drive the button bar

On the Data tab, use the down menu and select New Custom Category….

Next, from the New Custom Category dialogue, select any category that has a cardinality greater than the number of metric choices you want to give your user. In this example, we will allow our report users to select between Revenue, Expense and Profit, so we have 3 options. Therefore, select a category with a cardinality greater than 3, so 4 or more will work.

parameters to pick your metric in Visual Analytics Reports2

The next part might seem awkward, but this prevents us from having to load a separate table into LASR. If you have the role and capabilities to load data into LASR then you could load a 3-row table that contains one column with the entries Revenue, Expense and Profit, and you could create your control object from that data source. However, you then create the dependency that both tables must be loaded to LASR for the report to work. The technique outlined in this blog allows us to use just this one data source, but restricts the placement of control object. Using this technique means that you cannot use this control object as a report or section prompt. IF you need to use this control as a report or section prompt then you will need to load a separate table.

Otherwise, follow along.

Name your custom category List of Measures. Next, create the labels of the measures you wish your report users to select from. Again, in my example, I want to allow my report users to pick either Revenue, Expense or Profit. Hint: Double click on Label 1 to rename it then use the New label button to add others.

Next, drag and drop at least one value into each custom category label grouping. The custom category data item will only allow you to save it once each category label grouping has a value. You may leave the radio button default Group remaining values as Other selected.

Finally, click OK to save your new custom category.

parameters to pick your metric in Visual Analytics Reports3

Step 2: Create the parameter to hold the button bar’s selection

Now that we have the custom category to feed the button bar’s values, we need to create a parameter to hold the button bar’s selection. From the Data tab, use the drop down menu and select New Parameter….

Select the Type Character and give your parameter a meaningful name. You can leave the current value, or default value, blank. When we assign the parameter to the button bar the value will be populated upon selection.

parameters to pick your metric in Visual Analytics Reports4

Step 3: Create the calculated data item that will hold the selected measure’s value

Here we need to create a calculated data item that will hold the value of the selected measure. This is the measure we will use in our table and graph objects. In pseudo code, we want to create a new measure that

If user selects "Expense" Return <Expense>
Else, If the user selects "Profit" Return <Profit>
Else Return <Revenue>

From the Data tab, use the drop down menu and select New Calculated Item….

parameters to pick your metric in Visual Analytics Reports5

Then use the Visual or Text editor to create a new calculated item named Measure.
Use nested IF…ELSE statements from the Boolean operators and the x = y statement from the Comparison operators.

parameters to pick your metric in Visual Analytics Reports6

Step 4: Add report objects and assign roles

Now we can add our report objects to our report. IMPORTANT: With our technique of using an unrelated category as the source of our custom category we cannot put our button bar in the report or section prompt areas. If we put the button bar in either the report or section prompt areas it would automatically filter the data and we would not get the intended results. DO NOT put the button bar in the report or section prompt area.

In this sample report, you can see I’ve added the Button Bar Control object, the Bar Chart, Crosstab and Line Chart objects to the report area. I want to narrow this section by year, so I have included a Drop-Down List Control to the section prompt area.

parameters to pick your metric in Visual Analytics Reports7

Now let’s look at the role assignments for our various objects. Here is what the report looks like before any style enhancements:

parameters to pick your metric in Visual Analytics Reports8

Drop-Down List Control Object

parameters to pick your metric in Visual Analytics Reports9

Button Bar Control Object

This is where we want to assign our newly created custom category and save that selection to our parameter. This will give us all the categories of our custom category, including the “Other” category.

parameters to pick your metric in Visual Analytics Reports10

Next, we will need to add a filter on this object to remove the “Other” category. From the Filters tab, add a filter for the List of Measures category and deselect the “Other” category.

parameters to pick your metric in Visual Analytics Reports11

Bar Chart, Crosstab and Line Chart

Use our new calculated item as the measure for each of the Bar Chart, Crosstab and Line Chart.

parameters to pick your metric in Visual Analytics Reports12

There is one interaction, where the Bar Chart filters the Crosstab.

parameters to pick your metric in Visual Analytics Reports13

And here is what the report looks like after I’ve altered some of the objects’ styles from the Style tab. I colored the selected bar value to coordinate with the rest of the report objects.
parameters to pick your metric in Visual Analytics Reports14

In this screenshot, I’ve selected the Promotional Product Line.

parameters to pick your metric in Visual Analytics Reports15

Other Applications

As you can envision, this technique can be used for more than just metrics, you could also use this to allow your users to pick a category to use in your objects! Or both.

Just bear in mind that you are restricted to using the Control Objects that support parameters. This includes:

  • Text Input
  • Button Bar
  • Drop-Down List

Slider (single-point only)

 

 

 

 

 

tags: SAS Professional Services, SAS Visual Analytics

Use parameters to pick your metric in Visual Analytics Reports was published on SAS Users.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)