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“.
The functionality of the #SetActions offers us a wide range of possibilities to improve the user experience. A clear example is its use in Drill Down queries, where users can select the elements of the visualization on which it requires a greater level of detail.
The ability to define Drill Down of 2, 3, or even “n” levels is quite simple with #SetActions. However, if we consider the basic or default definition of the #SetActions, the Drill Up is only possible when the user is at the most detailed level of the detail.
When using definitions of two or three levels of details, using the basic definition of the full-cycle query drill is sufficient. But when we need to work with more levels, it’s advisable to offer more greater flexibility for the user to perform Drill Up at any level of the query and it isn’t required to perform the full cycle of consultation.
This exercise was born looking to combine the advantages of the #SetActions in the Drill Down, with the advantages of using custom Drill Up menus for the different levels of the detail.
The final result of this exercise consists of a Dashboard with a bar chart with which the user can perform the Drill Downs to a final level depending on the hierarchy chosen. As the user advances in Drill Down, a Worksheet is added, which represents a new option within the Drill Up menu, so that the user can return at the level of aggregation desired.
Next, I share the final version I got.
Data modeling is the key to achieving the definition of the Drill Up menus. The objective is to add an additional record to the data source of “Super Store” orders, with the values of the data in NULL.
Why do we need an additional record?
Let’s remember, with the #SetActions the user can control with their interaction with the “marks” that members are “IN” or “OUT” in the Set. Based on the selection of the user, we can set the formula of a calculated field to set different values depending on whether the “IN / OUT” of the elements of the Set.
In this exercise, the formulas consider that the elements of the Set that are selected by the user and that are “IN”, indicate that the level of detail will be deepened, that is, that a Drill Down will be made.
Therefore, to perform the opposite option, that is, request a Drill Up, it’s required to reset the “OUT” values in the set involved.
Having this additional record in the data, allows us to create a worksheet of Drill Up, where the user selects the “mark” of the additional record, with null value and thus activates one or more #SetActions to redefine and leave all the values “OUT” of the Set, since none of the actual values matches the null value of the additional record.
Now let’s see how to add the additional record in our data.
Step 1: Create an Excel file called “Reset Sets”.
Within a sheet called “Reset”, create a column with the same name of “Reset” and capture a single record with a value of 1.
Step 2: Define a Join type Full between Orders and Reset.
The union clause to be used is: 0 = Reset
Ready, we already have an additional record with null values.
Below I share an example, to help understand the behavior of the members of a Set in the Drill Down and Drill Up operations.
DEFINE THE ORDER OF VARIABLES IN THE HIERARCHY
Step 3: Create parameters to select the order of the variables in the hierarchy.
A parameter must be created for each of the five hierarchy levels. We will use the following names for the parameters: “Select Dim 1”, “Select Dim 2”, “Select Dim 3”, Select Dim 4 “and” Select Dim 5 “.
They will be defined as “String” and their possible values are: “Category”, “Sub-Category”, “Region”, “Segment” and “Ship Mode”.
FOR LEVEL 1 OF THE HIERARCHY
Step 4: Definition of the Dimension of the Drill Down, Set and Set Action of the first level of the hierarchy.
4.1 Create the Drill Down field for the first level of the hierarchy.
The value of the variable “DD Dim1” will be determined by the value selected by the user in the first parameter.
4.2 Create the Set “DD Dim1 Set”.
The Set will be associated with the dimension created in the previous step.
4.3 Create the Set Action “Set Action1” for the Worksheet.
To register the first #SetActions, you must select the Worksheet / Actions / Add Action / Change Set Value menu.
The #SetActions can also be defined within the “Dashboard” menu. In this exercise, I preferred to use the definition at the “Worksheet” level to facilitate the understanding of the steps to follow in each of the levels of the hierarchy.
Choose for “Source” the option “Orders (Sample – Superstore)” and the option that all Set values should be removed when the selection is cleaned, when defining the #SetActions.
FOR LEVEL 2 OF THE HIERARCHY
Step 5: Definition of the Dimension of the Drill Down, Set and Set Action of the second level of the hierarchy.
Note: From the second level of the hierarchy, the definition of the Drill Down dimension of the level will be determined by: 1) The value selected by the user in the corresponding parameter and 2) The value of the members of the Set of the previous levels (“IN” / “OUT”).
FOR LEVEL 3 OF THE HIERARCHY
Step 6: Definition of the Dimension of the Drill Down, Set and Set Action of the third level of the hierarchy.
FOR LEVEL 4 OF THE HIERARCHY
Step 7: Definition of the Dimension of the Drill Down, Set and Set Action of the third level of the hierarchy.
FOR LEVEL 5 OF THE HIERARCHY
Step 8: Definition of the Dimension of the Drill Down, Set and Set Action of the third level of the hierarchy.
TO SELECT THE MOST DETAILED LEVEL MARKS
The next steps are to identify the maximum level of detail or depth of each record and the maximum level in the data source, which will help us to select and graph exclusively the “Marks” of greater level of detail.
Step 9: Create the “Level” dimension.
To identify the maximum level of open hierarchy that is being handled in each of the records of the data.
Step 10: Create the “Max Level” dimension.
To identify the value of the maximum level in the data source.
Step 11: Create the “Filter” dimension.
To compare the level of each record vs. the maximum level in the data source. (TRUE / FALSE).
Step 12: Create “Last Dimension” dimension.
According to the level identified for each record, assign the new variable “Last Dimension” the corresponding value. This variable is the one that will display the name of each bar in the chart.
CREATE THE MAIN CHART
Step 13: Create the bar chart.
Move to the shelves of:
- Columns ⬅ “Sales”
- Rows ⬅ “ DD Dim1”, “DD Dim2”, “DD Dim3”, DD Dim 4” and
“Last Dimension” each sorted descending by sales
- Color ⬅ “Level”
- Filter ⬅ “Filter” = TRUE
“Reset” = NULL (to exclude the additional record in Null)
Hide the “Header” in the dimensions of “Rows”, except “Last Dimension” which will be the only visible dimension.
DEFINITION OF THE OPTIONS OF THE DRILL UP MENU
It’s important to remember the request requests that the menus of Drill Up, should be dynamic depending on: 1) order of consultation defined in the parameters and 2) of the maximum level of consultation that is being worked on.
To achieve this, a worksheet must be created for each personalized menu option and each of them will impact a specific Set Action. These worksheets will work exclusively with the additional record with null values that was added to the data table in the first steps of the exercise.
Step 14: Customize the titles for the menu options.
According to the order selected in the hierarchy and in the maximum level used in the hierarchy.
Step 15: Define the worksheets for the menu options.
A worksheet will be defined for each of the options within the menu for the Drill Up. That is, 4 worksheets, one less than the maximum level of consultation allowed.
Remember that in these worksheets you should only select the additional record with null values (Reset = 1), to use the null values to execute #SetActions and to clean the values of the Sets.
Step 16: Update the definition of the Set Actions.
The definition of the “Source Sheets” within the #SetActions will be modified:
I hope you enjoy this exercise and that this blog has been useful for you.
If you have any questions about the blog, don’t hesitate to contact me on twitter (@rosariogaunag)
Diagram with an example of execution of the five levels of Drill Down: