locked
Running Total in SSAS RRS feed

  • Question

  •  

    Hi,

    I have a cube to show the below information. I have to add the running total. 

     

    Qty

    Year

    Month

     

     

     

     

     

    2009

     

     

     

     

     

    Type

    1

    2

    3

    4

    5

    6

    In Qty

    40027

    39398

    51205

    54370

    70702

    76658

    Out Qty

    62455

    53118

    48297

    50604

    73617

    52442

     

    I need add the running total like below.

     



    Qty

    Year

    Month

     

     

     

     

     

    2009

     

     

     

     

     

    Type

    1

    2

    3

    4

    5

    6

    In Qty

    40027

    39398

    51205

    54370

    70702

    76658

    Out Qty

    62455

    53118

    48297

    50604

    73617

    52442

    Total

    -22428

    -36148

    -33240

    -29474

    -32389

    -8173


     The formula is -->

    Total = Previous Period Total Qty + Inqty - Out Qty

    Can any one help me please.....

               

     

    Monday, August 9, 2010 7:00 AM

Answers

  • You could create the calculated member in the dimension that is on the rows.

    eg.

    CREATE MEMBER [Qty Type].[Qty Type].[All].[Total] as
        SUM(NULL:[TimeDim].[Month].CurrentMember
             , ([Qty Type].[Qty Type].[In Qty], Measures.[Qty]) - ([Qty Type].[Qty Type].[Out Qty], Measures.[Qty])
        )


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Proposed as answer by Jerry Nee Tuesday, August 10, 2010 6:54 AM
    • Marked as answer by praveen.ars Wednesday, August 11, 2010 2:54 AM
    • Unmarked as answer by praveen.ars Friday, August 13, 2010 6:24 AM
    • Marked as answer by Raymond-Lee Wednesday, August 18, 2010 11:17 AM
    Tuesday, August 10, 2010 4:19 AM

All replies

  • You could use something like:

     

    with member [Measures].[RunningTotal] as
    sum(
     [TimeDim].[Month].CurrentMember.Lag(X) : [TimeDim].[Month].CurrentMember,
     [Measures].[Total]
    )
    

     


    Dirk Wegener http://www.dwexplorer.com
    Monday, August 9, 2010 7:13 AM
  • Hi,

    Thanks for your reply, how ever i'm having problem with this query. 

    I tried to add as New Calculated Member , New Named Set & New Script Command, i am getting syntax error when i tried to execute the query.

    Errors are below

    New Calculated Member  & New Named Set

    Query (2, 5) Parser: The syntax for 'WITH' is incorrect. 0 0


    New Script Command

    Query (6, 1) Parser: The syntax for ';' is incorrect. 0 0

     

    Can you please help me.

    Monday, August 9, 2010 7:53 AM
  • Hi, 

    Please ignore my previous reply. I solved that, but i have another problem.

    The calculated Member i can add only to column , i need this information on rows like shown below. I have two dimension , one with Year & Qty Type(In Qty & Out Qty).

     

    Qty

    Year

    Month

     

     

     

     

     

    2009

     

     

     

     

     

    Type

    1

    2

    3

    4

    5

    6

    In Qty

    40027

    39398

    51205

    54370

    70702

    76658

    Out Qty

    62455

    53118

    48297

    50604

    73617

    52442

    Total

    -22428

    -36148

    -33240

    -29474

    -32389

    -8173


     

    Can you please help me solve this.

    Monday, August 9, 2010 8:33 AM
  • You could create the calculated member in the dimension that is on the rows.

    eg.

    CREATE MEMBER [Qty Type].[Qty Type].[All].[Total] as
        SUM(NULL:[TimeDim].[Month].CurrentMember
             , ([Qty Type].[Qty Type].[In Qty], Measures.[Qty]) - ([Qty Type].[Qty Type].[Out Qty], Measures.[Qty])
        )


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Proposed as answer by Jerry Nee Tuesday, August 10, 2010 6:54 AM
    • Marked as answer by praveen.ars Wednesday, August 11, 2010 2:54 AM
    • Unmarked as answer by praveen.ars Friday, August 13, 2010 6:24 AM
    • Marked as answer by Raymond-Lee Wednesday, August 18, 2010 11:17 AM
    Tuesday, August 10, 2010 4:19 AM
  • Hi Darren,

    it worked, great.

    Many many thanks............

     

    • Marked as answer by praveen.ars Wednesday, August 11, 2010 2:54 AM
    • Unmarked as answer by praveen.ars Wednesday, August 11, 2010 2:54 AM
    Wednesday, August 11, 2010 2:54 AM
  • Hi Darren,

    I made some changes to the above query,  instead of [TimeDim].[Month].CurrentMember   i put  [TimeDim].[Hierarchy].CurrentMember , Hierarchy(Year --> Month). This query is working perfect.  

    Now I'm having problems with performance.

    I added another dimension for DimProd for products to Drill-through the In Qty & Out Qty, and it's taking too much time to show the results.

    Can you please help me solve the performance issue?.

    Friday, August 13, 2010 4:24 AM
  • Running sums like this are pretty "heavy" calculations. For large resultsets performance can be an issue.

    You might want to work throught the performance tuning whitepaper from Microsoft to see if you can improve this
    http://sqlcat.com/whitepapers/archive/2009/02/15/the-analysis-services-2008-performance-guide.aspx


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Tuesday, August 17, 2010 7:47 AM