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:

Hi Devin,
It is possible to use the Analysis Services formatting in Reporting Services by setting the "ReturnCellProperties" extended propery in the dataset connection string:
http://prologika.com/CS/blogs/blog/archive/2007/08/14/how-to-get-extended-properties-with-ssas-ole-db-provider.aspx
cheers, Nathan

Hi Devin,
It is possible to use the Analysis Services formatting in Reporting Services by setting the "ReturnCellProperties" extended propery in the dataset connection string:
http://prologika.com/CS/blogs/blog/archive/2007/08/14/how-to-get-extended-properties-with-ssas-ole-db-provider.aspx
cheers, Nathan

Hi,
I have a sales(SalesID, GoodsType, GrossSale, AverageSale). I was to write two MDX expression in MS Visual Studio SSAS cube's Calculation tab to calculate Sum of GrossSale and Average of GrossSale where GoodsType is "Food". If someone please write the MDX expression for me?
I wrote this expression for sum but it doesn't work!
CREATE MEMBER CURRENTCUBE.[Measures].Sales
AS SUM([Measures].[Gross Sale], [Measures].[Datatype] = "2"),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ;
........................
I have tried in this way also
CREATE MEMBER CURRENTCUBE.[Measures].Sales
AS SUM([Measures].[Datatype].&[6], [Measures].[Gross Sale]),
VISIBLE = 1 , DISPLAY_FOLDER = 'Forecast' ;
I should elaborate bit more, please check bellow,
I have a data table. Where GrossSale and Datatype are columns. Say in GrossSale I have (100, 200, 400, 130, 350) and in Datatype I have (5,2,6,8,2) respectively. Now I want Sum of GrossSale where I have Datatype 2. Then I have GrossSale = 550, that is 200+350. Because for Datatype 2, I have 200 and 350.
If someone can help me please?

Here are this and some other articles on Conditional formatting: http://ssas-wiki.com/w/Articles#Conditional_formatting

Hi Devin,

Please help me on making it dynamic:

Rating Dimension:

(ID, MainVariance, MaxVariance, Rating, StartDate, EndDate)

Rating Dimension values:

SELECT 1 AS ID, 0 AS MinVariance, 0.10 AS MaxVariance, 1 AS Rating, '01/01/1900' AS StartDate, '06/06/2079' AS EndDate FROM ApplDim

UNION

SELECT 2 AS ID, 0.11 AS MinVariance, 0.30 AS MaxVariance, 2 AS Rating, '01/01/1900' AS StartDate, '06/06/2079' AS EndDate FROM ApplDim AS ApplDim_5

UNION

SELECT 3 AS ID, 0.31 AS MinVariance, 1 AS MaxVariance, 3 AS Rating, '01/01/1900' AS StartDate, '06/06/2079' AS EndDate

FROM ApplDim AS ApplDim_4

UNION

SELECT 4 AS ID, 0 AS MinVariance, 0.20 AS MaxVariance, 1 AS Rating, '01/01/2011' AS StartDate, '12/31/2011' AS EndDate

FROM ApplDim AS ApplDim_3

UNION

SELECT 5 AS ID, 0.21 AS MinVariance, 0.50 AS MaxVariance, 2 AS Rating, '01/01/2011' AS StartDate, '12/31/2011' AS EndDate

FROM ApplDim AS ApplDim_2

UNION

SELECT 6 AS ID, 0.51 AS MinVariance, 1 AS MaxVariance, 3 AS Rating, '01/01/2011' AS StartDate, '12/31/2011' AS EndDate

--------

Created a set in Cube as "RatingSet" :

RatingSet

{

[Rating].[Rating].[Rating] *

[Rating].[End Date].[End Date] *

[Rating].[Max Variance].[Max Variance] *

[Rating].[Min Variance].[Min Variance] *

[Rating].[Start Date].[Start Date]

}

Calculation which needs to be made dynamic:-

IIF( ([Measures].[Variance Percent]>=0 and [Measures].[Variance Percent]<= 0.10) –--Variance Percent

,1

,IIF( ([Measures].[Variance Percent]>0.10 and [Measures].[Variance Percent]<= 0.30)

,2

,3 --Rating

)

)

i.e. Variance Percent will be 0, 10, 30, 50 etc… and Rating will be 0, 1, 2 3 etc…

---------

I tried below, but not working:

WITH DYNAMIC SET Test2 as

IIF( ([Measures].[Ownership Variance Percent]>=[Rating].[Min Variance].[Min Variance].members.Item(0)

and [Measures].[Ownership Variance Percent]<= [Rating].[Max Variance].[Max Variance].members.Item(0))

,[Rating].[Rating].[Rating].members.item(0)

,IIF( ([Measures].[Ownership Variance Percent]>[Rating].[Min Variance].[Min Variance].members.Item(1)

and [Measures].[Ownership Variance Percent]<= [Rating].[Max Variance].[Max Variance].members.Item(1))

,[Rating].[Rating].[Rating].members.item(1)

,[Rating].[Rating].[Rating].members.item(2)

)

)

------

Now, How to make it dynamic as above???????

Any help will be appreciated.

Please suggest asap.

Many Thanks in Advance.