Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Change Measure / Group when drill down on a bar chart

Reply
Occasional Contributor
Posts: 6

Change Measure / Group when drill down on a bar chart

Let say I have a table like this.

 

ContinentCountryCityRevenue (M)
AmericaUSANew York1000
AmericaUSANew York2000
AmericaUSATexas1500
AmericaUSATexas2000
AmericaMexicoGuadalajara1500
AmericaMexicoGuadalajara1500
AmericaMexicoPuebla2000
AmericaMexicoPuebla1500
AsiaIndonesiaJakarta1000
AsiaIndonesiaJakarta1500
AsiaIndonesiaBandung2000
AsiaIndonesiaBandung2500
AsiaMalaysiaKuala Lumpur1500
AsiaMalaysiaKuala Lumpur2000
AsiaMalaysiaJohor Bahru1500
AsiaMalaysiaJohor Bahru1000

 

I want to have a drill down stacked-bar chart that represent this.

On the first level, the category should be Continent and the Group should be Country with the Revenue as the Value.

After drilling down, the category should be Country and the Group should be City.

 

I can achieve the category changing by using hierarchy, but how do I change the grouping everytime I drilldown? 

 

Thank you!

 

 

Anthony Steven

 

SAS Super FREQ
Posts: 70

Re: Change Measure / Group when drill down on a bar chart

Hi @anthony28852,

 

The way I've found you can do that is by implementing your own drill down capability, so you know in wich level of the hierarchy you are and use that information to modify the data item assigned to the group role. It's not ideal, but it worked.

 

In the example below, I have a "hiearchy" with 4 levels (Continent / Country / State / City) so I've added 3 drop-down lists to perform the navigation, one for each level of the hierarchy (we don't need a drop-down for the last level of the hierarchy). Each one of the drop-down controls has a parameter assigned to them called _continent, _country, and _state respectively.

 

Those parameters were used to derive two calculated items: Geo and Geo Subdivision. Geo contains the values of the current level of the hierarchy, and Geo Subdivision contains the next level. For example, if nothing is selected in the drop-down controls, it means we are at the highest level of the hierarchy (so Geo works as continent and Geo Subdivision works as country). If a continent is selected, Geo will be displaying countries and Geo Subdivision will contain the states (and so on...)

 

Expression for Geo:

 

Expression Geo.PNG

 

Expression for Geo Subdivision:

 

Expression Geo Subdivision.PNG

 

I've then assigned Geo to the Category role in the Bar Chart and Geo Subdivision in the Group role, as well as Revenue as the measure.

 

Finally, the interactions were defined as below. Observe that this gives a cascading prompt from continent to country to state.

 

Interactions

This is the final result (initially at the continent level):

 

Figure1.PNG

 

Choose North America as the continent:

 

Drill into North America

 

Choose United States as the country:

 

Drill into United States

 

Choose Texas as the state:

 

Drill into Texas

 

To help identify which level of the hierarch we are looking at, I've added a Button Bar at the top left of the report and assigned a calculated item to it called Geo Level, defined as follows:

 

Expression for Geo Level

 

Best,

Renato

Post a Question
Discussion Stats
  • 1 reply
  • 325 views
  • 1 like
  • 2 in conversation