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

http://RichardLees.com.au