BookmarkSubscribeRSS Feed
Fluorite | Level 6

Let say I have a table like this.


ContinentCountryCityRevenue (M)
AmericaUSANew York1000
AmericaUSANew York2000
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 Employee

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.



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




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






Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 1 like
  • 2 in conversation