Imagine a case where the customer wants to monitor devices during the hours of the day. All devices can either be on or off and can be in serice or in error. Besides that the devices have boundary setting which are used to determine if a device should be on or off. The customer wants insight into the status, including the boundary setting if the device performs out of the ordinary (sometimes we want to see the boundary settings and sometimes we don’t).

For this customer we found an, I think, clever solution using the matrix visual and the ability to use conditional formatting of the cells background and font resulting in the visual below.

To create this visual we need a couple of things. First we need business rules applied to the underlying data to interpret if a device is on or of, or any of the other status. We then need a translation of the status into the way we want to display that particular status. For instance, if the status is:

  • In Error, it should be displayed as Yellow without text
  • Out of Service, it should be displayed as Gray without text
  • Off ignoring the boundary setting, it should be displayed as White without text
  • On ignoring the boundary setting, it should be displayed as Green without text
  • On because a boundary setting was met, it should be displayed as Green with Black text
  • On when it should be off, it should be displayed as Green with White text
  • Off when it should be on, it should be displayed as White with Green text

Based on these rules we define a table in our model with the rule (id) and the colors we want the background and font to have. When the text should not be displayed, both background and text need to be the same color. You could add a description to be able to create a tooltip to further explain the status.

With this business rule table and a fact table with the RuleId, the device and the boundary value (and other dimensions like date and time) we can create our model. For the visual to work we need to set the relationship between fact and rule dimension to both.

With this model set up we can start to create our visual. Add a matrix to the canvas and add Devices to the Rows, Hours to the Columns and Boundary values to the Values. The visual now looks like this.

And now for the magic. Select Conditional formatting of the boundary values and choose Background color.

In the Format by dropdown choose the field value. Navigate to our Rules table and select the BackgroundHex column. Apply the changes and the background color is visible.

The visual now looks like this:

If you started with the conditional formatting of the Font, which works the same only this time you would select the FontHex field you would see this:

When you apply Conditional formatting to both background and font (and adding the tooltip page) the resulting visual would be:

When the Rule table is part of your datawarehouse in a database you can change the colors of the visual without a need to deploy the visual by just changing the Hex codes in the table. Refreshing the dataset and report will show the new colors in the report. You can even extend this effect to show different colors depending on slicers on the report, just extend the rule table to reflect this. I can imagine this effect being useful in several other cases.

Good luck giving this a go yourself and finding your own application!

You can find the example file here.

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *