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′);

END SCOPE

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.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s