I am using following MDX query to highlight only those values which are
1% greater than its respective previous value. Only problem i am facing
is that whenever i apply this formula on any calculated Measure which
is using any aggregation formula like SUM, AVG, e.t.c., it won't work.

Any suggestion to modify this Query so it can also handle Calculated
Measures using Aggregation formulas???



With Cell Calculation INDICATOR For '( { [Measures].[Previous Year
Sales Value %]} )' As
'CalculationPassValue(Measures.CurrentMember, -1 , Relative )',
CONDITION =' CalculationPassValue( [Measures].[Previous Year Sales
Value %], -1,Relative ) >
( ([Time].CurrentMember.PrevMember,Measures.[Previous Year Sales Value
%]) * 1.01)' ,
FORMAT_STRING ='##,##.##; (##,##.##) ; \Z\e\r\o', FORE_COLOR ='11004',
CALCULATION_PASS_NUMBER ='2', CALCULATION_PASS_DEPTH ='1'

SELECT {[Measures].[Previous Year Sales Value %]} ON COLUMNS,
{{[Time].[Year].members}} ON ROWS
FROM [Sales]
CELL PROPERTIES [VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

Can you possibly provide a sample Foodmart MDX query illustrating the
problem, because this seems to work:

Member [Measures].[Month Sales Value] as
'Sum({[Time].CurrentMember}, [Measures].[Unit Sales])'
Cell Calculation INDICATOR
For '( {[Measures].[Month Sales Value]} )'
As 'CalculationPassValue(Measures.CurrentMember, -1 , Relative )',
CONDITION =
' CalculationPassValue( [Measures].[Month Sales Value], -1,Relative ) >
( ([Time].PrevMember,Measures.[Month Sales Value]) * 1.01)' ,
FORMAT_STRING ='##,##.##; (##,##.##) ; \Z\e\r\o', FORE_COLOR ='11004',
CALCULATION_PASS_NUMBER ='2', CALCULATION_PASS_DEPTH ='1'

SELECT {[Measures].[Unit Sales],[Measures].[Month Sales Value]} ON
COLUMNS,
Descendants([Time].[1997], [Time].[Month]) ON ROWS
FROM [Sales]
CELL PROPERTIES [VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***