Dynamic dashboard ranges - its all in the data

Business Problem: The traffic lighting (red/green/yellow) range is different for one group/category than the others but the indicator needs to include all group values.

Recommendation: It's all in the data.

Here is an example. For the Candy_Sales_Summary data (located in the EG Sample Data folder) there are three groups of customers. The distribution of revenue within the retailer group is significantly higher than the other two groups, therefore the range is different. The table below includes the executive's defined red/yellow/green ranges for each group.

 Type Revenue (Million) Range (red, yellow, green) Result Grocery \$9.7 <10, 10<15, 15+ Red Retailer \$46.82 <40, 40<60, 60+ Yellow Wholesale \$18.59 <10, 10<15, 15+ Green

Step 1. Update the data source to include a calculated range result based on the different groups.

You can do this any number of ways. For this example, I just used a quick sql statement and data step to get the 1, 2, 3 result based on the above criteria.

```proc sql;
create table vals as
select
type,
sum(sale_amount) as rev format=dollar12.
from egsample.candy_sales_summary
group by type;
quit;
data bid.typeind;
set vals;
if type = 'Retailer' then do;
if rev/1000000 < 40 then ind=1;
else if 40 <= rev/1000000 < 60 then ind=2;
else ind=3;
end;
else do;
if rev/1000000 < 10 then ind=1;
else if 10 <= rev/1000000 < 15 then ind=2;
else ind=3;
end;
run;```

The resulting data table has three columns, the type, revenue value and the indicator number.

 type rev (in mil) ind Grocery \$9.7 1 Retailer \$46.82 2 Wholesale \$18.59 3

Step 2. Create a BI Dashboard Range to convert the calculated range into color categories.

In BI Dashboard, only one range can be applied to the indicator display therefore we need to create a simple range of 1, 2, 3 and use ETL code to derive the  range on the data table.

Step 3. Generate an indicator

Included is a sample indicator that uses the updated data source (from step 1.) and the range (created in step 2.). Note that I modified the data definition to use the variable REV as the label for the data in IND. Otherwise the numbers displayed would be 1, 2, 3.

1. Harsh Gajjar
Posted November 28, 2011 at 8:00 am | Permalink

Hello Angela,
Really Interesting Blog. But it would be really helpful if you can guide me through the following scenario.: I have a dataset which is having the range values . But now I want ranges to be defined on the basis of the dataset. i.e I want to include dataset values as ranges in dashboard.It should automatically retrieve the values without having me to manually type the values of ranges..

If there is a possible solution please let me know via mail.

• Angela Hall
Posted November 28, 2011 at 1:26 pm | Permalink

At this time, the ranges must exist within the range definitions in BI Dashboard. The workaround I describe in the blog post is the only way around this limitation.

• Harsh Gajjar
Posted November 28, 2011 at 2:35 pm | Permalink

So The only way we can have ranges is manually inserting in the range definitions in SAS BI Dashboard..We just cannot automate the Process.

One More Question which I've encountered is how to get a prompted filter in SAS Bi Dashboard..

• Angela Hall
Posted November 28, 2011 at 2:42 pm | Permalink

Calculating the result of the indicator within the data is the workaround to loading/automating range definitions in BI Dashboard.

For the 2nd question - use a Dynamic Prompt and create an interaction between that and the indicator it will change. Prompted filters will also be covered in my upcoming book "Building Business Intelligence Using SAS: Content Development Examples". It will be available for purchase in only a few short weeks :)

2. J. Liou
Posted April 17, 2012 at 7:41 pm | Permalink

Thank you for the tip, it's very interesting!

3. Nicole Scroggins
Posted November 13, 2012 at 3:19 pm | Permalink

Hi Angela-

I am new to the BI dashboard, and I was wondering in step 3 where you modified the data definition to use the variable REV as the label for the data in IND, did you simply apply a format to the indicator column and label it as the REV? or how were you able to accomplish this?

• Angela Hall
Posted November 27, 2012 at 10:10 pm | Permalink

In Step 3, I used the indicator properties (found on the right side of the BI Dashboard web application - after selecting the indicator) and chose REV as the data element in the label drop down box and the IND data element as the data drop down selection.

Hope this helps. Otherwise, a screenshot followup might be in order.

• Nicole Scroggins
Posted November 28, 2012 at 1:42 pm | Permalink

Ok my dashboard properties must be a little different. I dont have the drop down for label. I was able to get it to work by defining a format within EG before pulling the data into dashboard. Does this dynamic range also work with gauges or does this just work with the lights?

• Angela Hall
Posted December 4, 2012 at 12:44 pm | Permalink

Great news Nicole.
The dynamic 'colors' will work with any indicator that uses a range.

1. By Customize the bar chart color palette in BI Dashboard on September 12, 2012 at 12:44 pm

[...] the same color in each indicator or from refresh to refresh. With most dashboard tricks, like the dynamic dashboard range, it is all about the data. The steps are to create a numeric value for each bar's categorical [...]