SQL Server Performance

Calculating the total sum across different dim

Discussion in 'Analysis Services/Data Warehousing' started by Hemanth, Aug 12, 2004.

  1. Hemanth New Member

    Dear All,

    I have a cube with

    Four Dimensions .
    Location
    Product
    Time

    With measures
    DispKgs
    Ord Kg#%92s
    Lines disp
    Lines ordered
    AIP IN KGS

    Calculated Measures
    Value fill %
    VALUE FILL Numerator
    Ordered Kg Reason,
    Ordered KG Location
    Ordered KG Product

    And
    Value Loss % This is the calculated measure where I am having an issue
    Hence Instead of having one single Calculated measure I have made three calculated measures which are Locn.Loss % and Prdt.Loss and Rsn .Loss.

    The reason is this.




    The Rsn Loss is given by the formula

    (([Measures].[Ord Kgs]*[Measures].[Aip In Kgs])-[Measures].[Value Fill Numerator])/([Measures].[Ordered KG Reason]*[Measures].[Aip In Kgs])

    Where Ordered KG Reason

    sum (descendants(Reason.members),[Measures].[Ord Kgs])/6


    I have divided it by 6 to adjust the values of Ordered KG Reason for Demo purposes.

    The Ordered Kg should be the total sum of Ord KGS for the dimension which is on the row field ( In this example Reason is on the row field) on an html page which uses pivot table (OWC Component) .

    The bottom line being that I want to calculate the sum of Ordered Kg#%92s for whichever dimension is brought on to the row field and replace Prdt Loss ,Reason Loss and Location Loss by a single measure called value loss which depends on the sum of ordered Kg#%92s of the row dimension .

    which will be possible if I can have just one ordered KG Measure instead of Ordered KG for every dimension.

    Lets say instead of reason dimension there was Location dimension the Ordered Kg Measure should be ale to calculate the sum of ordered kg measure for the location dimension. I,e the ordered kg measure should not have Dimension name hard coded in the formula like the one I have calculated.

    sum (descendants(Reason.members),[Measures].[Ord Kgs])/6



    Hope you can help me with this formula

    Thanks in advance for your help.

    Hemanth

Share This Page