Conditional Formatting when Drilling

[former_member201488]

Using the drillfilters() function, we can see where we are in a hierarchy when drilling is enabled. However, sometimes you may want to format report elements or data based on your whereabouts in the hierarchy. This is very easy to do and can be an effective mechanism.

For this example we will be using E-Fashion and the State>City>Store name hierarchy. We create a query returning just these objects:

Without any arguments or parameters, the drillfilters() function will show the selections you have made to drill into your hierarchy (if you haven't yet drilled into your data, this will return a blank cell). However, we can add a Dimension parameter to the function to indicate the value clicked when drilling to the next level in the hierarchy.

In the E-Fashion hierarchy we are using as an example, if we drill down from the State level to the City level by clicking California, then the drillfilters([State]) function will show the value California.

Let's create a simple report to demonstrate this.

When we are at the top of our Hierarchy, we can see that no drilling has yet taken place; the drillfilters() functions are all blank. However, if we drill down a level we get:

..the drillfilters([State]) function now returns a result - the state name we drilled down on.

Using this logic we can craft an alerter:

We're going to change the colour of the text in the header cell depending upon where we are in the hierarchy. In the above example, when the drillfilters([State]) function returns a null - which is the default before we drill into the data - the text will be red. However, if we drill down to City, drillfilters([State]) is no longer blank but drillfilters([City]) is - hence the text becomes green:

..and finally, if we drill down again, the text becomes blue:

This is the bottom of the Hierarchy; we can't click on the Store name to drill any further, hence drillfilters([Store name]) will always return a null.

If you wanted, you could expand on this mechanism to create a very visual indication of where you are in the drill hierarchy:

This last example involves a little bit more work - one alerter for each hierarchy level, to achieve the above result. See if you can figure it out for yourself!

I hope you find useful :smile: