[Old post reposted as I was not very happy with the formatting]
Have a look at this query
1: WITH
2: MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] – [Measures].[Internet Sales Amount] * .4
3: MEMBER [Measures].[PROFIT] AS [Measures].[Internet Sales Amount] – [Measures].[REFUNDS]
4: MEMBER [Product].[Product].[Top10] AS
5: sum(
6: TOPCOUNT([Product].[Product].[Product].MEMBERS
7: , 10
8: , [Measures].[Internet Sales Amount]
9: )
10: ,
11: ([Measures].CurrentMember)
12: )
13: MEMBER [Product].[Product].[Top10PercOfTotal] AS [Product].[Product].[Top10]/[Product].[Product].[All Products]
14: SELECT
15: {
16: [Measures].[Internet Sales Amount],
17: [Measures].[REFUNDS],
18: [Measures].[PROFIT]
19: } ON COLUMNS,
20: {
21: [Product].[Product].[Top10]
22: ,[Product].[Product].[Top10PercOfTotal]
23: ,[Product].[Product].[All Products]
24: }
25: ON ROWS
26: FROM [Adventure Works]
This yields same result as yours i.e. not a flat cell by cell division
Internet Sales Amount | REFUNDS | PROFIT | |
Top10 | $10,355,525.92 | $6,213,315.55 | $4,142,210.37 |
Top10PercOfTotal | 0.35272454 | 0.211634724 | 0.141089816 |
All Products | $29,358,677.22 | $17,615,206.33 | $11,743,470.89 |
I don’t expect it to produce this result . If the computations was going as planned the output should be 0.35272454 (35 %) for each.
I have altered the SOLVE ORDER (this is the property which determines the order of cell computation) .
Here is modified query
1: WITH
2: MEMBER [Measures].[REFUNDS] AS [Measures].[Internet Sales Amount] - [Measures].[Internet Sales Amount] * .4,SOLVE_ORDER = 1
3: MEMBER [Measures].[PROFIT] AS [Measures].[Internet Sales Amount] - [Measures].[REFUNDS],SOLVE_ORDER = 2
4:
5: MEMBER [Product].[Product].[Top10] AS
6: sum(
7: TOPCOUNT([Product].[Product].[Product].MEMBERS
8: , 10
9: , [Measures].[Internet Sales Amount]
10: )
11: ,
12: ([Measures].CurrentMember)
13: )
14: ,SOLVE_ORDER = 3
15:
16: MEMBER [Product].[Product].[Top10PercOfTotal] AS
17: [Product].[Product].[Top10]/[Product].[Product].[All Products]
18: , FORMAT_STRING = "Percent"
19: , SOLVE_ORDER = 4
20:
21:
22:
23: SELECT
24: {
25: [Measures].[Internet Sales Amount],
26: [Measures].[REFUNDS],
27: [Measures].[PROFIT]
28: } ON COLUMNS,
29: {
30: [Product].[Product].[Top10]
31: ,[Product].[Product].[Top10PercOfTotal]
32: ,[Product].[Product].[All Products]
33:
34: }
35: ON ROWS
36: FROM [Adventure Works]
37:
Here is the output
Internet Sales Amount | REFUNDS | PROFIT | |
Top10 | $10,355,525.92 | 6213315.552 | 4142210.368 |
Top10PercOfTotal | 35.27% | 35.27% | 35.27% |
All Products | $29,358,677.22 | $17,615,206.33 | $11,743,470.89 |
There you go. SOLVE_ORDER determines the series of how cell computation will be formed. One with smallest SOLVE_ORDER will be evaluated first.
Hope this explains SOLVE_ORDER and how it works. For more have a look at MDSN @ http://msdn.microsoft.com/en-us/library/ms145539.aspx
Pingback: 2010 in review « CodePerfect Limited