DRILL DOWN & UP WITH PARAMETER ACTIONS

Update 02/27/2020: A new blog with a new technique to use Drill Down and Drill Up with Parameter Actions was published with the name “Drill Down with left menu and Drill Up with top menu“.

---

In this exercise we will work with 5-level Drill Down and Drill Up using Parameter Actions. Drill Up are very useful when working with more than three levels of hierarchy.

Main requirements:

• Definition of five levels of consultation hierarchy for sales: 1) Category, 2) Subcategory, 3) Region, 4) Segment and 5) Shipping mode.
• When the user selects a “mark”, the chart will be updated and will show only the information with the next hierarchy level according to the value of the “mark” selected.
• As the Drill Down option is used, that is, when the hierarchy consultation levels are deepened, options must be added within a top menu bar, which allows you to return to any of the previous levels of aggregation.
• On the other hand, when a Drill Up option is activated in the menu bar, the main chart is updated and in addition, the available menu options are updated, according to the selected Drill Up level.

Then I share the steps I followed.

STEP 1: CREATE THE PARAMETERS.

As Parameter Actions are going to be used, the first thing we must do is define the Parameters to be used.

One parameter will be used for each level of the hierarchy: 1) Category, 2) Sub-Category, 3) Region, 4) Segment and 5) Ship Mode.

TYPE	PARAMETER	CURRENT VALUE
String	DD Parameter1	Empty
String	DD Parameter2	Empty
String	DD Parameter3	Empty
String	DD Parameter4	Empty
String	DD Parameter5	Empty

STEP 2: CALCULATE A NEW DIMENSION WITH THE MAXIMUM VALUE OF DRILL DOWN OF CONSULTATION USED.

Initially, the parameters are assigned the value of an empty string, but as the user interacts with the Viz, the Parameter Actions will update the value of its associated parameter. With the update of the parameter values, the value of this field will automatically be recalculated.

Max Level

IIF([DD Parameter1] = “” OR [DD Parameter5] <> “”, 1, IIF([DD Parameter2] = “”, 2, IIF([DD Parameter3] = “”, 3, IIF([DD Parameter4] = “”, 4, 5))))

Convert to Dimension with the right click.

STEP 3: CREATE A NEW DIMENSION FOR EACH LEVEL OF DRILL DOWN.

A variable is created for each level of the hierarchy: Category, Sub-Category, Region, Segment and Ship Mode. Its value will depend on the level of consultation or detail used.

DD Dim1	[Category]
DD Dim2	IIF([Max Level] >= 2, [Sub-Category], “”)
DD Dim3	IIF([Max Level] >= 3, [Region], “”)
DD Dim4	IIF([Max Level] >= 4, [Segment], “”)
DD Dim5	IIF([Max Level] >= 5, [Ship Mode], “”)

Note: The dimension “DD Dim1” is optional.

STEP 4: CALCULATE A NEW DIMENSION WITH THE NAME THAT WILL BE DISPLAYED IN THE BAR CHART.

Its value will depend on the maximum consultation level used.

Last Dimension

IIF([Max Level] = [Max Level], CASE [Max Level] WHEN 1 THEN [DD Dim1] WHEN 2 THEN [DD Dim2] WHEN 3 THEN [DD Dim3] WHEN 4 THEN [DD Dim4] WHEN 5 THEN [DD Dim5] END, NULL)

STEP 5: CREATE A DIMENSION TO FILTER.

It’s required to create a filter to show only the next level of detail, according to the last mark selected by the user in the visualization.

For example, when the user selects the Category = Furniture mark, the following consultation should show only the Sub-Categories that correspond to the newly selected Category.

Filter

[Max Level] = 1 OR ([Max Level] = 2 AND [DD Dim1] = [DD Parameter1]) OR ([Max Level] = 3 AND [DD Dim1] = [DD Parameter1] AND [DD Dim2] = [DD Parameter2]) OR ([Max Level] = 4 AND [DD Dim1] = [DD Parameter1] AND [DD Dim2] = [DD Parameter2] AND [DD Dim3] = [DD Parameter3]) OR ([Max Level] = 5 AND [DD Dim1] = [DD Parameter1] AND [DD Dim2] = [DD Parameter2] AND [DD Dim3] = [DD Parameter3] AND [DD Dim4] = [DD Parameter4])

STEP 6: CREATE BAR CHART.

Move to the shelves of:

• Filters   “Filter” = True
• Columns   “Sales” sort descending
• Rows   “Last Dimension”
• Color    “Max Level”
• Detail    “DD Dim1”, “DD Dim2”, “DD Dim3”, “DD Dim4” y “DD Dim5”

STEP 7. DEFINITION OF DRILL DOWN PARAMETER ACTIONS.

Create a dashboard with the newly created chart.

Create a Parameter Action for each level of Drill Down. To define them, the “Change Parameter” option of the “Actions” menu will be used, within the newly created dashboard menu.

PARAMETER ACTION	TARGET PARAMETER	FIELD OR VALUE	AGGREGATION
Parameter1	DD Parameter1	DD Dim1	None
Parameter2	DD Parameter2	DD Dim2	None
Parameter3	DD Parameter3	DD Dim3	None
Parameter4	DD Parameter4	DD Dim4	None
Parameter5	DD Parameter5	DD Dim5	None

STEP 8: CREATE A NEW DIMENSION TO CLEAN THE VALUE IN THE PARAMETERS WITH THE VALUE OF A NULL STRING.

Blank

“”

STEP 9: CREATE VARIABLES FOR THE LEGEND IN THE DRILL UP MENUS.

Its value will depend on the maximum level of consultation.

Back L1	IIF([Max Level] > 1, “« BACK TO” + CHAR(10) + ”   CATEGORIES”, “”)
Back L2	IIF([Max Level] > 2, “« BACK TO” + CHAR(10) +  ”  SUB-CATEGORIES”, “”)
Back L3	IIF([Max Level] > 3, “« BACK TO” + CHAR(10) + ”   REGIONS”, “”)
Back L4	IIF([Max Level] > 4, “« BACK TO” + CHAR(10) + ”   SEGMENTS”, “”)

STEP 10: CREATE A SHEET FOR EACH LEVEL OF DRILL UP.

The sheets will be used to create a menu with the different levels of Drill Up.

Sheet	Detail	Text
Back1	Blank	Back L1
Back2	Blank	Back L2
Back3	Blank	Back L3
Back4	Blank	Back L4

Include in the Dashboard each of the sheets, to create the menu for the user.

Below is an image of what the full menu would look like at the highest consultation level.

STEP 11. DEFINITION OF PARAMETER ACTIONS FOR DRILL UP.

Create a Parameter Action for each level of Drill Up to clean the parameters.

PARAMETER ACTION	SHEET	TARGET PARAMETER	FIELD OR VALUE	AGGRE  GATION
Back1	Back1	DD Parameter1	Blank	None
Back2	Back2	DD Parameter2	Blank	None
Back3	Back3	DD Parameter3	Blank	None
Back4	Back4	DD Parameter4	Blank	None

The latest version of the Viz is here.

SUMMARY

The use of Parameter Actions facilitates the definition of complex Drill Down & Up.

The basics to perform this exercise involve:

FOR THE DRILL DOWN:

Define for each level of Drill Down (5 in this exercise):

• A Dynamic Dimension to update the value of an associated Parameter, whose value is determined in turn by the different parameters used.
• A Parameter.
• A Parameter Action to update the parameter value, with the dynamic dimension.

FOR THE DRILL UP:

Define a dimension to clean the parameters:

• A dimension with the value of a null string.

Define for each level of  Drill Up (4 in this exercise).

• A Dimension with the legend that represents the corresponding Drill-Up.
• A new Sheet to show the legend of the previous point and that includes the dimension to clean the parameters.
• A Parameter Action to be applied in each of the sheets, to clean the value of the associated parameter.

Thank you very much and if you have any questions about the post, do not hesitate to contact me on Twitter (@rosariogaunag).