Skip to Content
Author's profile photo Neil Mitchell-Goodson

Conditional Formatting when Drilling

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:

Efashion Hierarchy Query.JPG

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.

Efashion Hierarchy Report Design.JPG

Efashion Hierarchy Report.JPG

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:

Efashion Hierarchy Report D1.JPG

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

Using this logic we can craft an alerter:

Efashion Hierarchy Alerter.JPG

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:

Efashion Hierarchy Report D3.JPG

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

Efashion Hierarchy Report D4.JPG

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:

Efashion Hierarchy Report D5.JPG

Efashion Hierarchy Report D6.JPG

Efashion Hierarchy Report D7.JPG

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 🙂

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.