Conditional formatting for regular (non-calculated) measures?

The examples of conditional formatting I have found have all related to calculated measures -- i.e., ones defined on the "calculations" tab of the cube designer. On the calculation definition panel there is a "format string" (which deals with currency symbols and thousands separators and such), "color expressions," and "font expressions." Examples in various places show using an IIF to set color based on the calculated measure's value. I assume that any valid boolean-yielding expression could be used in the IIF, that there is no scope limit that restricts the test to the measure's value.

Okay. Is there some way, for display within the SSAS cube browser, to do conditional formatting for regular (non-calculated) measures?

In the properties panel of such measures I see a "format string" property and a separate "format" property but no "color expressions" or "font expressions" and I have seen no examples of putting IIFs into a regular measure's properties. Perhaps this is only a user-interface issue and not an architectural issue. Perhaps there is some MDX way to set the "fore color" property programmatically and have the color assignment be visible in the SSAS cube browser. Perhaps if one puts certain magical strings into the "Format" property conditional formatting will occur.

I have tried to RTFM. If this is all answered in documentation someplace, just point me to it.

CW

You can do this in the Calculations script for the cube, using a Scope assignment and the Fore_Color and Back_Color properties.

For example, in the Adventure Works sample, I added this to the Caculations script for the cube to set the foreground color of the [Internet Sales Amount] measure (a standard measure) to red:

Scope

(

[Measures].[Internet Sales Amount]

);

Fore_Color ( This ) = "255";

End Scope;

The following link in BOL explains (somewhat) the values to use for setting the Fore_Color and Back_Color properties (using standard RGB numeric values):

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/mdxref9/html/ff8f40cb-2ac4-4fc2-9761-7f1b14c17c8c.htm

HTH,

Dave F.

|||

Or, a simpler syntax (and also fixing the color to be int, not a string)

Fore_Color([Measures].[Internet Sales Amount]) = 255;

|||

Okay, using this info and the supplemental info from Mosha I have been able to turn a relevant measure red.

However, I am an MDX noob and am not making progress so I am back with a follow-up question. Let me know if this is more appropriately posted as a new thread.

My goal is to use the SCOPE technique to set the fore color and back color of all measures associated with a dimension's members ("items") that have a certain attribute ("Is Exception") with a value of "True." The true/false attribute is assigned in the source database and is merely loaded into the cube. My success was that I was able to set the fore color of at least one measure but I see the redness only if I drag the "Is Exception" attribute into the browser as a row and expand it. "Is Exception" measure is red but the "Total" measure is black as is everything else. But I want the measures associated with the item to be red without having to drag the true/false attribute into the browser.

Anyhow, here is a sample (sanitized) query that correctly lists the items that have the flag as "True":

SELECT {[Item Dimension].[Is Exception].[True]} ON 0, NON EMPTY {[Item Dimension].[Item Name].Children } on 1 FROM ItemCube where {[Measures].[Item Distinct Count]}

When I run this I see the item names as row labels, "True" as the column label, and 1 as the cell value, and the only items listed have the Is Exception attribute value of True.

But I haven't been able to turn this into a SCOPE that colors the measures associated with the Item member.

Here's my SCOPE thingy:

SCOPE( [Item Dimension].[Is Exception].[True]);

Back_Color(This) = 255;

END SCOPE;

CW

|||

I came up with a SCOPE piece of code that seems to do what I want. Here it is, sanitized. With yellow instead of blazing red.

SCOPE(

Filter([Item Dimension].[Item].Members, [Item Dimension.[Item].Properties("Is Exception", TYPED) = True), [Measures].Members

);

Back_Color(This) = 8454143; /*R=255, G=255, B=128*/

END SCOPE;

CW

|||

I was able to use the above approach to format each account in a parent/child accounts dimension. My cube only has a single measure and the accounts dim has members that require different formatting including percentages (with 0 to 2 decimals), integers, currency (with 0 to 2 decimals). This approach works, but it is a pain in the neck to implement as I have over 500 accounts.

I have a "custom rollup" column in my parent child table that I use to specify calculations. It seems I should be able to pick a "format_string" column in the same way. Is this possible? If not, what is the best way to format an accounts dimension in a cube that has a single measure?

TIA,

-Jon