[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

About these ads