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

MDX - Color Formatting on All Measures at Once

In a post a while back I wrote about how you can create conditional color formatting on the Analysis Services measures you create.  Recently I found a way to apply this same color formatting to all your measures.  Why would you want this?  Well let's say you want to all measures that have a value of less than 0 to show red to draw attention to negative numbers. 

To do this you will create a SCOPE statement.  You do this on the calculation tab of the by selecting a Script Command and using the following MDX.

SCOPE ([Measures].allmembers); 

    FORE_COLOR(THIS) = IIF([Measures].currentmember <0 ,'255','0'); 


It does matter what order code appears in the calculation list and so make sure this is on the very bottom to apply to all calculations.  Next time you drag in any calculation or measure and it's value is less than 0 it will show red to your users.


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).


No comments.

Leave a Comment

Please register or log in to leave a comment.