I am using the following MDX query to find out those Measure of the
previous quater which has increased by one percent;

--- Running this query against Food Mart 2000 Test Data base
With Cell Calculation SAPIENCE_INDICATOR4780648 For '(
{Measures.[Amount]} )' As
'CalculationPassValue(Measures.CurrentMember, 0 )', CONDITION ='
CalculationPassValue(Measures.[Amount], 0 ) > ( (ParallelPeriod
(Quarter,1,Time.CurrentMember),Measures.[Amount]) * 1.01)' ,
FORE_COLOR ='11013' ,FORMAT_STRING ='##,##.##; (##,##.##) ; \Z\e\r\o'


SELECT{{[Account].[Level 02].[Net Income]}} ON COLUMNS,
{CrossJoin({Measures.[Amount]},{[Time].[Year].[1997].[Q1].CHILDREN,[Time].[Year].[1997].[Q2].CHILDREN})}
ON ROWS FROM [Budget] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

The problem is that as there is no year above than 1997 in time
dimension and this query strangley changing the values of first quarter
of 1997.

Any idea to handle this issue?

Looks like the issue is that the Account dimension has custom rollups,
which only occur in Pass 1; but the Calculated Cell is using values from
Pass 0 (presumably prior to Custom Rollup). This version seems to work;
but you can tweak the number of passes, as long as the cell calculation
only starts at Pass 2:

{Measures.[Amount]} )' As
'CalculationPassValue(Measures.[Amount], -1, RELATIVE )', CONDITION ='
CalculationPassValue(Measures.[Amount], -1, RELATIVE ) > (
(ParallelPeriod
(Quarter,1,Time.CurrentMember),Measures.[Amount]) * 1.01)' ,
FORE_COLOR ='11013' ,FORMAT_STRING ='##,##.##; (##,##.##) ; \Z\e\r\o',
CALCULATION_PASS_NUMBER = 2, CALCULATION_PASS_DEPTH = 1

SELECT{{[Account].[Level 02].[Net Income]}} ON COLUMNS,
{CrossJoin({Measures.[Amount]},{[Time].[Year].[1997].[Q1].CHILDREN,[Time
].[Year].[1997].[Q2].CHILDREN})}
ON ROWS FROM [Budget] CELL PROPERTIES
[VALUE],[FORMATTED_VALUE],[CELL_ORDINAL],[FORE_COLOR]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Thanks for your prompt response!!
By using your suggested logic, i am getting partial correct results;

--- Query run on Food Mart 2000

with Cell Calculation INDICATOR For '( { [Measures].[Amount]} )' As
'CalculationPassValue(Measures.CurrentMember, -1 , Relative )',
CONDITION =' CalculationPassValue( [Measures].[Amount], -1,Relative )
< ( (ParallelPeriod (Quarter,1,Time.CurrentMember),Measures.[Amount])
* 0.99)', FORMAT_STRING ='##,##.##; (##,##.##) ; \Z\e\r\o', FORE_COLOR
='11004', CALCULATION_PASS_NUMBER ='2', CALCULATION_PASS_DEPTH ='1'

SELECT NON EMPTY { CrossJoin({[Account].[Level 02].[Net
Income]},{Measures.[Amount]})} ON COLUMNS,
{{[Time].[Year].[1997].[Q1].[1],[Time].[Year].[1997].[Q1].[2],[Time].[Year].[1997].[Q1].[3],[Time].[Year].[1997].[Q2].[4],[Time].[Year].[1997].[Q2].[5],[Time].[Year].[1997].[Q2].[6],[Time].[Year].[1997].[Q3].[7],[Time].[Year].[1997].[Q3].[8],[Time].[Year].[1997].[Q3].[9],[Time].[Year].[1997].[Q4].[10],
[Time].[Year].[1997].[Q4].[11],[Time].[Year].[1997].[Q4].[12]}} ON ROWS

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

This query is actually comparing the values from its respective
previous member and if had decreased greater than one percent it
highlights this value with Red color. See the results of Quarter 5th (
Compairing 2nd & 5th Quarters ) and 8th Quarter ( Compairing 5th & 8th
Quarters) , these pairs are not following the specified rule.

Is it relates some thing to the precision limitation??

When I execute this query in MDX Sample App, Months 5 and 8 are not
highlighted in red, which seems correct from data.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

I ran this query on a different OLAP server having the same SQL server
2000 installed on it and it returned correct results but with different
set of data.

Is it possible that Food Mart 2000 Data base have different set of data
in each installed SQL server 2000?