Fore Color Expressions

Hi all, I can't seem to find an example anywhere on the net or BOL on this one. I assume you can set the color of a calculated members dynamically in the Fore Color Expression property but I can't find any code samples for that. Can someone provide me a simply one for changing the color to red if a condition is met?

I really appreciate it!

Brian

Hi Brian,

Here's a recent query for the AS 2000 Foodmart Sales cube - you should see some cells highlighted in red when this is run in the MDX Sample App:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/3a1eccc9d291c961

>>

With Member [Measures].[testmetric] AS
'[Measures].[Store Sales Net]-[Measures].[Store Sales]',

FORE_COLOR ='iif([Time].currentMember.Level is [Time].[Quarter] And
([Time].CurrentMember.PrevMember,[Measures].[testmetric])
- CalculationPassValue( [Measures].[testmetric], -1,Relative )

> Abs(([Time].CurrentMember.PrevMember,Measures.[testmetric])) * 0.025,

rgb(255,0,0), rgb(0,0,0))',
FORMAT_STRING = 'Currency'

SELECT
{[Measures].[Store Sales], [Measures].[Store Sales Net],
Measures.[testmetric]} ON COLUMNS ,
CrossJoin({[Product].[Product Family].[Drink],
[Product].[Product Family].[Food],
[Product].[Product Family].[Non-Consumable]},
{{[Time].[Year].[1997].CHILDREN}}) ON ROWS

FROM [Sales] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

>>

|||

Thanks, so it sounds like I'd want to do something like this at runtime in the OLAP client application and not in the SQL Server 2005 Color Expressions of a Calculated Member?

Brian

|||

You can do this using Calculations tab of Sql Server Busyness Intelligence Studio too. When you press the button on the right side of a color expression textbox (there are 2 of those – for foreground and background color) there will be a color chooser dialog box to pick the desired color. When you have selected the color its code will be inserted in the text box. You can subsequently pick another color and it will add it to the text box again. Thus the contents of the text box will have 2 color codes with comments.

Now you can modify the contents of the textbox to have an expression using the codes you obtained. Something like:

iif([measures].currentmember > 0, 4227327 /*R=255, G=128, B=64*/, 8388608 /*Navy*/)

If you like editing MDX scripts in the plain text format then you might find convenient usage of the debugger as a helper to write the script. When you are on the calculations tab you can hit F5 and the tab will load the debugger. In the text box for the script you can type your MDX statements and click toolbar buttons to select colors and fonts. The codes will be inserted at the cursor of the script text box. You can also drag the templates of MDX statements from the tree views on the left side of the debugger.

Having constructed your statement, you can execute it (F10) and immediately test with the pivot table or MDX query you like (there are 4 tabs for custom queries). Pressing F10 subsequently will step over till the end and restart from the beginning of the script. Breakpoints also help.

Once you are done you can select Debug | Stop menu command and you will be offered to keep your script or discard. Thus you can use the debugger to try something and throw away (MDX prototyping).

|||Thanks Andrew, that worked like a champ.sql