Saturday, November 29, 2008

Elaborate your cubes with conditional formatting

SQL Server Analysis Services cubes have supported conditional formatting of measures since 2000. Now that Excel supports conditional formatting, they really are a "must have" to make some cubes more readable. For example, my PerfMon cube has the same “Value” measure, which could be “Bytes of Virtual Storage” or “Seconds per Read IO”. You really want to format the number depending on what it is. For example, a 9 digit number probably wants to be formatted as #,#, while a number of about 0.001 probably wants 6 significant digits.
This is all very easy with Analysis Services, simply put a condition on the FORMAT clause. For example
FORMAT_STRING =
iif(ROOT(DateTime),[Measures].[Value])>100,"#,#",
iif(ROOT(DateTime),[Measures].[Value])>0.1,"#,#0.000","#,#0.000000"))
I like to add colour formatting, so that the number formatting stands out. For example, on the same measure, I might add
FORE_COLOR=
iif(ROOT(DateTime),[Measures].[Value])>100, RGB(0,0,0),
iif(ROOT(DateTime),[Measures].[Value])>0.1, RGB(0,155,0), rgb(180,180,180)))
I use the ROOT() function so that the formatting is not specific to any particular cell, which would be annoying to the eye. Rather all cells for any one counter will be consistent in their format. For an demonstration of conditional formatting at work (on a live cube using the cube browser I wrote, ThinSlicer) go to http://richardlees.com.au:8080/iislog/tsPerfmon.aspx Just clicking “Display” you will see the numbers in a light grey colour, since these numbers don’t mean anything. Once you drill down one of the counter hierarchies the true number and colour formatting come into effect. It works exactly the same in Excel. Unfortunately, Reporting Services does not support these conditional formats yet, although, one could argue that the need for RS to honour conditional formatting is less. Performance Point dashboards support the conditional number formatting. For a demonstration you can see the same live cube through a PPS dashboard on http://RichardLees.com.au/sites/Demonstrations/Shared%20Documents/Windows%20Performance%20Monitor/Interactive%20Chart-Grid.aspx

Not all cubes have the need for conditional formatting, since the numbers in any one measure tend to be homogenous. However, cubes that contain company accounts (GL cubes etc) really need this sort of conditional formatting, as the number you are looking at could "Turnover", "Average Tax/Transaction" etc. These really need conditional formatting.

Richard




3 comments:

Nick Barclay said...

"Unfortunately, Reporting Services does not support these conditional formats yet"

I thought this was true too until a couple of months ago, I found out by accident. When you create an SSAS query with the SSRS report designer the cell proprty flags are automatically added to the query (CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS).

As long as these (or those that you want) are referenced in the MDX code you can set the appropriate properties of a specific report object to pick up whatever has been referenced in the query. e.g. =Fields!Internet_Order_Count.BackgroundColor.

Intellisense in the Expression editor will bring through whatever properties have been referenced in the MDX.

Cheers,
Nick

Richard Lees said...

Thanks Nick. I will be using this in RS. It's a pity RS doesn't just take AS formatting by default. You can spend a bit of time doing tedious formatting that is already in the cube.

Sam Kane said...

Here are this and some other articles on Conditional formatting:

http://ssas-wiki.com/w/Articles#Conditional_formatting