Conditional Formatting on hierarchy levels

Emmeline

Hi,

It would be a great feature to set up conditional formatting:
1. Either, even if summary method is turned to None;
2. Or based on the existence of one (or more) hierarchy levels

Sometimes, users want aggregated levels of a hierarchy to be highlighted on a UX grid.
You can set up a conditional formatting using some line items returning specific values depending on the level of the hierarchy (ex: 1 for level 1, 2 for level 2…). But this conditional formatting only works if summary method is turned on for the line items you want to format.

You can see an example below, where I applied a line item formatted by the dimension Accounting Nature (in row). The last line item, Comment, also has a conditional formatting, but we cannot see it because it is a Text line item with summary method turned off:

When I turn Comment summary method to "last non blank", conditional formatting appears, but I need to set up an additional DCA to prevent users from seeing aggregations of Text on top levels (since it doesn't mean anything):

Problem is that I increase the size of my model by activating useless summaries + creating a DCA line item.

JoanaNdreu

Hi @Emmeline, how did you manage to build this type of conditional formatting on Accountings Levels? I have a similiar issue having a 4 level hierarchy account, but cannot create in one single CF line item more than 2 levels. Thank you for your help!

Emmeline

Hi @JoanaNdreu,

I set up a line item which returns a given number for each item according to its level (cf "C5 Level" on the right):

To do so, you need to create as many line items as there are levels in your hierarchy and apply the following formula for each level: for Level 1 formula = 1, for Level 2 formula = 2…

Make sure, for each technical line item, you set up "Applies To" on the corresponding List Level. Finally, create a technical "One" line item, with a formula = 1 and no dimension as applies to. For each level, configure the summary method as Ratio as following:
For each line item standing for a Hierarchy Level, Ratio numerator = Level above / Ratio denominator = One. Exception for the top level line item, set up Ratio = One / One

Finally you can retrieve this technical line item "C5 Level" in the module you want to apply a conditional formatting on, with a summary method set on Formula.

JoanaNdreu

Thank you very much, it worked!

StellaO

Thanks for this workaround! I was searching for this.