How to get the correct Sub-total and Grand Total for a Cell Calculation

i trying correct sub-total , grand total calculation product of calculated measure represents current running quantity * standard cost of item valid if calculated per site.

tried using calculated measure after lot of research decided go cell calculation works item/site calculation , should honor sub-selects etc opposed scope doesn't.

need sub-total sum of totals per item , site , not re-calculation of formula. able degree total end being individual item/site calculations plus item totals added in double amount. tried using except remove member , several other strategies.

here code:

create cell calculation currentcube.[balanceamount]

'([item].[item groups - items].members,[site].[sites].members,[measures].[balance amount])'
    as
        case
            when [item].[item groups - items].currentmember [item].[item groups - items].[all]
                then
                    9999 // should total on
                   
            when [item].[items].currentmember [item].[items].[all]
                then
                    999 // should total on item group
                   
            when [site].[sites].currentmember [site].[sites].[all]
                then
                    //99 correct
                    sum(descendants([site].[sites].currentmember,,leaves),
                                [measures].[inventory balance qty]*[measures].[standard cost])
                else
                    //9 correct
                    [measures].[inventory balance qty]*[measures].[standard cost]
            end;

   

---

nigel murdoch

why scopes not work sub-selects/multi-selects?

do multi-select on [item], or on [site] ?

i used proposed scope , tested on adventure works , works fine (at least understanding of requirement):

create member currentcube.[measures].[inventory] ( sum({null:[date].[calendar].currentmember},      [measures].[internet order count]) );  scope([measures].[internet tax amount], leaves([product]), leaves([promotion]));     = [measures].[internet sales amount] / [measures].[inventory]; end scope;

[measure].[inventory] [measures].[inventory balance qty] running total on time
[product] , [promotion] similar [item] , [site]

i abused [measures].[internet tax amount] physical measure sum-aggregation

multi-selects on [product] example work fine , aggregated correctly

gerhard

---

- www.pmone.com -

SQL Server  >  SQL Server Analysis Services