Drill Down/Up | Selecting Level to Start within the Hierarchy

This post is a complement to the publication “Drill Down/Up | Basic technique in a Multi-Level Hierarchy“, where a detailed explanation of the basics of how this technique works is given.

This new exercise was born from a question I received in one of my blogs, where they told me that in their company a hierarchy of more than 10 levels was used and users wanted to be able to select the level at which to start within the hierarchy, instead of always having to start from the level zero.

Additionally, it was also required that the decision of the level with which the Drill Down cycle will end be variable depending on the level of disaggregation of the data itself.

In this exercise we will continue working with the geographic hierarchy and the user will be able to select the level to start: 1) Region, 2) City, 3) State and 4) Postal Code.

STEP 1. CREATE PARAMETERS.  

PARAMETER	TYPE	CURRENT VALUE
Level	Integer	0
Path	String	World
Select Initial Level	Integer	Value 1 – Display as: Region  Value 2 – Display as: State  Value 3 – Display as: City  Value 4 – Display as: Postal Code

STEP 2. CREATE DIMENSIONS THAT THEIR VALUE DEPENDS ON THE LEVEL THAT THE USER SELECTS TO START.

The key to solving this variant is to create intermediate variables whose value depends on the value selected by the user as the starting level of the Drill Down.

Data L1	CASE [Select Initial Level]  WHEN 1 THEN [Region]  WHEN 2 THEN [State]  WHEN 3 THEN [City] + “, ” + [Abbreviation]  WHEN 4 THEN [Postal Code]  END
Data L2	CASE [Select Initial Level]  WHEN 1 THEN [State]  WHEN 2 THEN [City]  WHEN 3 THEN [Postal Code]  WHEN 4 THEN “Total”  END
Data L3	CASE [Select Initial Level]  WHEN 1 THEN [City]  WHEN 2 THEN [Postal Code]  ELSE “Total”  END
Data L4	CASE [Select Initial Level]  WHEN 1 THEN [Postal Code]  ELSE “Total”  END

It is important for the correct definition of these formulas to previously know the possible values of the fields that make up the hierarchy.

In this exercise, when the user decides to start with the City field, we know that the same city name can be used in different states of the country, an example is the city Apple Valley, since there is a city with that name in California and another in Minnesota.

For these cases, when starting from level 3, it is necessary to add to the name of the City, the name or abbreviation of the State, in order to differentiate each of the cities: Apple Valley, CA vs Apple Valley, MN.

STEP 3. CALCULATE THE DIMENSIONS THAT WILL SUPPORT US WITH THE DRILL DOWN.

Stop?	WINDOW_MAX(INDEX()) = 1
DD Level	IIF([Stop?], 0, [Level] + 1)
DD Label	CASE [Level]  WHEN 0 THEN [Data L1]  WHEN 1 THEN [Data L2]  WHEN 2 THEN [Data L3]  WHEN 3 THEN [Data L4]  WHEN 4 THEN “Total”  END
DD Path	IIF([Stop?],  “Total”, [Path] + “»” + ATTR([DD Label]))
DD Filter	STARTSWITH(“Total»” + [Data L1] + “»” +  [Data L2] + “»” + [Data L3] + “»” +  [Data L4] , [Path])

STEP 4. CALCULATE THE DIMENSIONS FOR THE DRILL UP MENU.

DU Level	ASCII([City]) % ([Level] + 1)
DU Path	LEFT([Path], FINDNTH([Path] + “»”, “»”, [DU Level] + 1) – 1)
DU Label	SPLIT([DU Path], “»”, -1)
DU Color	[DU Level] = [Level]

STEP 5. CREATE A SHEET THAT PRESENTS THE MENU WITH THE OPTIONS OF THE LEVEL TO START.

A new sheet or menu will be defined with the possible starting hierarchy options to update the “Select Initial Level” parameter.

The new sheet in the dashboard is managed within a Show / Hide container that allows the list to only be visible at the request of the user.

Why don’t I use direct parameter update? or What is the underlying reason for defining a sheet to show the available options?

Because when selecting a level to start in the new view, that is, toggling between one level and another, in addition to updating the new parameter “Select Initial Level”, it will allow us to update or reinitialize the values of the two main parameters.

Initial Level	ASCII([City]) % 4 + 1
Description	CASE [Initial Level]  WHEN 1 THEN “Region”  WHEN 2 THEN “State”  WHEN 3 THEN “City”  WHEN 4 THEN “Zip Code”  END
Reset Level	0
Reset Path	”Total”

STEP 6. DEFINITION OF PARAMETER ACTIONS.

Create two Parameter Actions for the Drill Down menu.

PARAMETER ACTION	SOURCE	TARGET PARAMETER	FIELD OR VALUE	AGGREGATION
DD Level	Drill Down	Level	DD Level	None
DD Path	Drill Down	Path	DD Path	None

Create two Parameter Actions for the Drill Up menu.

PARAMETER ACTION	SOURCE	TARGET PARAMETER	FIELD OR VALUE	AGGREGATION
 DU Level	Drill Up	Level	DU Level	None
DU Path	Drill Up	Path	DU Path	None

Create three Parameter Actions, for the selection menu of the Level to start.

PARAMETER ACTION	SOURCE	TARGET PARAMETER	FIELD OR VALUE	AGGREGATION
Select Initial Level	Levels	Select Initial Level	Initial Level	None
Reset Level	Levels	Level	Reset Level	None
Reset Path	Levels	Path	Rest Path	None

¡Listo! 

La versión interactiva la puede consultar aquí.

Muchas gracias y si tiene alguna pregunta sobre el post, no dudes en ponerte en contacto conmigo en Twitter (@rosariogaunag).