I have a problem with the results returned by an MDX query which I think can
be solved by changing the solve order, but I need some help.
The results represent the performance of a set of Australia/New Zealand
retail outlets at a given week in the quarter. The results contain 5
columns:
1. The actual sales up to the given week, for a particular outlet
2. The targeted sales up to the given week, for a particular outlet
3. The % the Actual to Date is of the Target to Date
4. The total Target for the Quarter
5. The Projected actual sales for the whole quarter (% Target to Date *
total Target for the Quarter)
There is a row per outlet and a row for the region (ANZ)

Here is a sample:

ACT TRGT %TRGT QTR QTR
TD TD TD TRGT PROJ
AUK 126 123 102% 480 492
WEL 277 220 126% 890 1121
MLB 85 47 181% 324 586
PER 304 267 114% 915 1042
SYD 26 24 108% 115 125
ANZ 818 681 120% 2724 3272

The problem is that we want the Quarter Projected (last column) Total for
ANZ to equal the sum of the outlets, that is 3366 not 3272. 3272 is the
result of applying the above formulas horizontally across the ANZ row
(818/681 = 120%, then 120% * 2724 = 3272).

The outlets and the region come from a dimension called Outlets with levels
Region and Outlet. Note it is not an option to create ANZ as a calculated
member in the MDX becuase we want it to always come from the dimension.

Here is the MDX query:

With

Member [Measures].[QTD] as
'Sum( PeriodsToDate( [StatDate].[Quarter], StatDate.CurrentMember),
[Measures].[Value] ) ' , solve_order = 1

Member [Statistic].[Sales].[Net Act] as
' [Statistic].[Sales].[Sold] - [Statistic].[Sales].[Returned] + 0 ' ,
solve_order = 2 , format = '0;-0;0;0'

Member [Statistic].[Sales].[Net TRGT] as
' [Statistic].[Sales].[Targeted] + 0 ' , solve_order = 2 ,
format = '0;-0;0;0'

Member [Statistic].[Sales].[%TRGT] as
' PosNegDivision ( [Statistic].[Sales].[Net Act] ,
[Statistic].[Sales].[Net TRGT] , "0.0%" ) ' , solve_order = 3 ,
format = '0%'

Member [Statistic].[Sales].[Net Proj] as
' ( StatDate.[2004].[1] , [Statistic].[Sales].[Net TRGT] ,
[Measures].[Value] ) * ( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net
Act] , [Measures].[QTD] ) / ( StatDate.[2004].[1].[5] ,
[Statistic].[Sales].[Net TRGT] , [Measures].[QTD] ) ' , solve_order = 4
, format = '0'

Select
{
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Act] ,
[Measures].[QTD] ) ,
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Trgt] ,
[Measures].[QTD] ) ,
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[%Trgt] , [Measures].[QTD] )
,
( StatDate.[2004].[1] , [Statistic].[Sales].[Net Trgt] ,
[Measures].[Value] ) ,
( StatDate.[2004].[1].[5] , [Statistic].[Sales].[Net Proj] ,
[Measures].[Value] )
}
ON COLUMNS ,

{
Hierarchize( [Outlets].Members , POST )
}
ON ROWS

FROM Stats

The problem does not lie in solve orders, rather in how
[Statistic].[Sales].[Net Proj] is defined, since it does not sum up the
contributions for each outlet. Try this:

'Sum(Descendants([Outlets],CurrentMember,,LEAVES),
(StatDate.[2004].[1], [Statistic].[Sales].[Net TRGT],
[Measures].[Value]) *
(StatDate.[2004].[1].[5],
[Statistic].[Sales].[Net Act] , [Measures].[QTD]) /
(StatDate.[2004].[1].[5], [Statistic].[Sales].[Net TRGT],
[Measures].[QTD]))', solve_order = 4, format = '0'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!