SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using SSAS MDX Calculation Color Expressions

Analysis Services calculations are great for storing formulas that your users need to see on a regular basis.  They also have another little feature that adds just a little more wow when your end users browse the cube. 

By building a color expression on a calculation you can change either the fore color or background color of a measure value.  Here you can write an MDX expression that will change the font or background color of a cell when a user selects the calculation.  A very basic example is below. 

This example is actually not dynamic but here is exactly what it does:

IIF([Measures].[Profit Margin] < .40, 255 , 0)

IIF( boolean expression, true part, false part)  ßVery similar to an IIF statement in SSRS expression language. 

 So if the profit margin is less than 40 percent I want my text to be red (represented by the 255) otherwise I want the text to be black (represented by the 0).  The color panel to the right of the expression window is where you can select the color codes.

Here is what the result looks like in the cube brower:

The great thing is the text formatting is also carried over to Excel shown below:

Unfortunately, this formatting is not carried over to reporting services but luckily to simulate this same formatting in your report the expression looks very similar:

=IIF(Fields!Profit_Margin.Value < .40, "Red", "Black")

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by dbowlin on 22 March 2010

Great tip for helping people using Excel for cube access and analysis to see data more easily.  Thanks.

Leave a Comment

Please register or log in to leave a comment.