Usually, a hierarchy in a WebI report “occupies” as many columns in a table as there are hierarchy levels. It means that if you want to show in a table a measure let’s say on a yearly, quarterly and monthly level, there are 3 columns in front of a column that shows the measure – one column for the [Year] dimension, one for the [Quarter] dimension and one for the [Month] dimension. Something like this simple report based on the eFashion universe:
If a hierarchy goes deeper and deeper (10+ levels deep hierarchy of products is not so unusual) then there are more and more columns in a table. In such case, displaying the hierarchy in just 1 column does not only save the space in the horizontal direction but may be much more appealing to report users. So have about the above table turned into this:
Let’s have a look how to achieve it using breaks and formatting.
Simple report at the beginning
Create a new WebI document. Use the eFashion universe and select these objects in the query:
Run the query and the result is a table similar to the one shown on the first picture above.
Few report level variables
We want to have the Quarter and the Month Name nicely indented so the result is displayed as a hierarchy. Therefore we need to create 3 new report variables:
This is a parameter that will be used in the next 2 formulas. It represent the number of spaces that will be added to the Quarter and Month Name. It’s better to have it as a separate parameter rather than hard-coding the value into the formulas. The advantage is that when you want to change its value – so the indent is bigger or smaller – you do it only once in this variable and you don’t need to change every variable where it’s used.
=Fill(" ";1*[v_Indent]) + [Quarter]
The formula adds 8 spaces in front of the Quarter.
=Fill(" ";2*[v_Indent]) + [Month Name]
The formula adds 16 spaces in front of the Month Name.
Create a new table using [Year], [v_Quarter_indent], [v_Month_Name_indent] and [Sales revenue] objects. Insert a break first on [Year] and then on [v_Quarter_indent]:
We are now ready for more funnier part 🙂
Formatting the breaks
We will do all changes in the structure view. It’s easier to explain there what we are doing. You can, of course, switch to results view anytime you want to check how the preliminary result looks like.
In the properties of both breaks, remove their break footers and break headers. Then add a table header. This is done in the properties of the table. Rename the header of the 3rd column to Period:
Show the break header of the break applied to the v_Quarter_indent. Copy the v_Quarter_indent variable to the break header but in the 3rd column. Copy also the [Sales revenue] variable into the break header in the 4th column. Then apply same format from the 2 cells (columns 3 and 4, row 3) to the 2 break header cells (columns 3 and 4, row 2):
Do the same with the break on [Year] – show its break header, copy the [Year] variable to the [Year] break header in the 3rd column, copy the [Sales revenue] variable into the [Year] break header in the 4th column, apply the format:
Now we have something in the table that we don’t want it to be there – the first 2 columns. We can’t simply remove them because the 2 breaks are applied there. So let’s have them disappear using white text color, white background and no cell borders:
And we are almost there. If you have not switched from the structure view back to the result view yet you can do it now 🙂
After few finishing touches – resizing the width of the first 2 columns to 4 pixels, removing the alternate row colors in the table, changing font size/bold/italic, and sorting the month names – we have achieved displaying the 3-level hierarchy in 1 column:
The fold-unfold functionality nicely works and enables to collapse any level of the hierarchy:
A) Try changing the value of the v_Indent variable and check its effect in the way how the hierarchy is displayed.
B) Instead of using break headers, try using break footers to achieve that the hierarchy is displayed this way:
Let me know in comments how you usually display a more deeper hierarchy in a table.