Sean Miller set this week’s #WOW2022 challenge based on a common requirement – how to allow users to navigate a hierarchy of data while capitalising on the the real estate available to display the data.

The charts required for this challenge are very simple, so I’m not going to spell out how to build these. I created 4 charts

• Trend – Sales by Month line chart
• by Category – Sales by Category horizontal bar chart
• by Sub-Category – Sales by Sub-Category horizontal bar chart
• by Product – Sales by Product Name horizontal bar chart

Now all the remaining functionality to drive the navigation through the hierarchy, how the charts are filtered at each level and whether the chart should display or not, will be driven by parameter actions. So for this we will need 3 parameters

pCategorySelected

string parameter defaulted to <nothing> (empty string)

We need similar parameters for pSubCategorySelected and also pProductSelected.

Controlling the sheet swap & filtering the charts

On a dashboard, add the Trend sheet, then below it add a vertical container.

Within the vertical container add the by Category sheet, the by Sub-Category sheet and the by Product sheet. Remove the title from all these sheets. Show the 3 parameters.

We’ll now set up some calculated fields to determine when each of the bar chart sheets should display or not.

Filter: Show Category

[pCategorySelected]=”

If this parameter is empty, we want to show the category bar chart. Add this field to the Filter shelf of the by Category sheet and set to True.

Type the word ‘Furniture’ into the pCategorySelected parameter box and press return. The by Category sheet should disappear from the dashboard.

We now do a similar calculation for the by Sub-Category sheet

Filter: Show Sub-Category

[Category]=[pCategorySelected] AND [pSubCategorySelected]=”

This field is filtering the bar chart based on the selected category

Add this to the Filter shelf of the by Sub-Category and set to True. The bars should now just display the sub-categories associated to the Furniture category.

Now type the word ‘Chairs’ into the pSubCategorySelected parameter box. The by Sub-Category sheet should also now disappear from the dashboard.

Finally we also need to now ensure the by Product sheet is filtered to the relevant Sub-Category.

Filter: Show Product

[Sub-Category]=[pSubCategorySelected]

Add this to the Filter shelf of the by Product sheet and set to True. Only products associated to Chairs should now be listed.

Now we’ve set all this up, we also need to ensure the Trend sheet is getting filtered based on all the selections being made.

Filter : Trend

([Category]=[pCategorySelected] OR [pCategorySelected]=”)
AND
([Sub-Category]=[pSubCategorySelected] OR [pSubCategorySelected]=”)
AND
([Product Name]=[pProductSelected] OR [pProductSelected]=”)

Add this to the Filter shelf of the Trend sheet and set to True.

Type in an appropriate value into the pProductSelected parameter box (eg Global Task Chair, Black) and see how the trend changes.

Setting the parameters

This will all be done with parameter actions – there’s a few 🙂

+ Drill down to show Subcategories within <Category>

Use the Insert link to add the <Category> field to the action title – this will then be set dynamically based on the bar being selected.

Set the action to apply to the by Category sheet only, and via the Menu option. It should impact the pCategorySelected parameter and retain it’s value when unselected. The Category field should be passed into the parameter.

Delete all the values from the parameter boxes, so they’re all empty. This should reset the dashboard so only the by Category sheet is displayed under the trend. Hover/click on a bar to show the tooltip and click on the link. The pCategorySelected parameter should be populated and the bar chart displayed now changes.

We’re going to create a similar parameter action for the drill down from by Sub-Category to the by Product sheet

+ Drill down to show Products within <Sub-Category>

This time the action applies to the by Sub-Category sheet on the Menu action, and sets the pSubCategorySelected parameter with the Sub-Category value, again retaining the value when cleared.

On this sheet, we also need an action to allow us to ‘drill up’. We need to set the pCategorySelected parameter back to nothing. For this we need an additional calculated field

Level Up : Category

”

Add this field to the Detail shelf on the by Sub-Category sheet.

The back on the dashboard, add a further parameter action

– Drill Up to show all Categories

The action runs on the Menu of the by Sub-Category sheet only, setting the pCategorySelected parameter with the value from the Level Up: Category field. Again the value should be retained when deselected.

Test the functions. The drill down should display the by Product sheet. Then manually delete the value in the pSubCategorySelected parameter, and test the drill up action.

We now need to deal with the actions from the by Product sheet

+ Filter dashboard to <Product Name>

This action runs on the Menu of the by Product sheet only and passes the Product Name field into the pProductSelected parameter. This time though, when the bae is unselected, the parameter should be cleared to ‘blank’.

Next we’ll add the drill up function back to the sub-categories.

Similarly we need to set the pSubCategorySelected parameter back to empty string, so we need

Level Up: Sub-Category

”

Add this to the Detail shelf of the by Product sheet. Also add the Category field to the Detail shelf.

+ Drill Up to show Subcategories within <Category>

The action applies to the Menu of the by Product sheet only, passing the Level Up : SubCategory field into the pSubCategorySelected parameter. The value should be retained when cleared. Note the Category field was required so it could be added to the menu action title.

Test the actions, and verify the behaviour of the parameter boxes as each selection is made.

The dynamic title

The title of the trend line keeps track of the options selected during the navigation. For some reason, I used a separate sheet, but that’s not needed and actually goes against the requirements on 4 sheets only. So I’ll describe how to dynamically set the title on the Trend sheet instead. We’ll need some additional fields

Title – Category

IF [pCategorySelected] <> ” THEN ‘for ‘ + [pCategorySelected] ELSE ” END

Title – Sub-Category

IF [pSubCategorySelected] <> ” THEN ‘-> ‘ + [pSubCategorySelected] ELSE ” END

Title – Product

IF [pProductSelected] <> ” THEN ‘-> ‘ + [pProductSelected] ELSE ” END

Add all these fields to the Detail shelf of the Trend sheet, then update the title

All of this should now mean the core requirements of the challenge have been met.

Bonus – Extending the tooltip width

The bonus step was to extend the width of the tooltip so no word-wrapping existed. I did this by creating a Viz in Tooltip.

On a separate sheet I added Category and Sales to the Text shelf and formatted so they were aligned as required.

This sheet was then referenced from the by Category tooltip where I then adjusted the width to 350 and the height to 75

I repeated this creating similar sheets for Sub-Category and Product Name.

You just now need to tidy up the dashboard – add a text box to act as a title for the bar charts section, format the titles to be grey and remove the parameters from the display. My published viz is here.

Happy vizzin’!

Donna