locked
Help in getting Monthly cumulative total RRS feed

  • Question

  • Hi i would like to get monthly cumulative totals for only past 2 quarters. I am using postgresql.

    The following are the cols which are taken after linking some five tables,

    Grp_name, Count(Attribute1),  Month, Qtr

    How to achieve this ?

    Any one please guide /..

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Tuesday, October 4, 2011 8:25 AM

Answers

  • Hi Radhai,

    I suggest you can use T-SQL to achive your target, and I sugghet you post the question to Transact-SQL forum as below: http://social.technet.microsoft.com/Forums/en-US/transactsql/threads. There are many experts focus on it.

    Thanks,
    Eileen 

    • Marked as answer by Eileen Zhao Friday, October 14, 2011 5:21 AM
    Thursday, October 6, 2011 9:05 AM
  • Hi ,

    In order to generate a Cumulative Total using Inbuilt SSRS functions-

    considering we have the following columns being returned by the Dataset - 1] Amount, 2] Count(Attribute1),  3] Month, 4] Qtr

    1] Use a Matrix Control and create a two Column Groupings one using the 'Month' column and the other using the 'Qtr' field

    In the above figure : I have used Prd_Id in place of Month Field and Qtr to indicate the Quarter

    2] Create a detail records using the Inscope function and the Running Value function available in SSRS

    The running value function is the one which provides you with the Cumulative Total
    The Inscope function is used here inorder to indicate where to use the Running value and where to show actual records.Since the cumulative in my example is shown like  - Jan , Feb , Mar and Q1 =(Jan+Feb+Mar)

    This can be changed by changing your scope defined for the Running value)

    example :

    =IIF( Inscope("GroupingBy_prd_id"),Fields!Amount.Value,RunningValue(Fields!Amount.Value,AVG,"Groupingby_Qtr"))

     where GroupingBy_prd_id, Groupingby_Qtr are column groupings and Fields!Amount.Value is the value that needs to be totalled

    I believe this helps

    thanks

    Umashankar

     

    Thursday, October 6, 2011 12:54 PM

All replies

  • Can you be more brief on your problem ?

    Rakesh M J

    Dont forget to mark it as Answered if found useful

    MCTS,MCITP,MCSS

    http://myspeakonbi.blogspot.com/

    Tuesday, October 4, 2011 8:45 AM
  • Can you be more brief on your problem ?

    Rakesh M J

    Dont forget to mark it as Answered if found useful

    MCTS,MCITP,MCSS

    http://myspeakonbi.blogspot.com/


    Hi,

    i have got one query in my search from the below link,

    http://www.postgresonline.com/journal/archives/47-How-to-calculate-Running-Totals-and-Sums-in-SQL.html

    in similar i need to get the total count of a particular attribute month wise in cumulative for only past 2 quarters.

    my result should show as like the below,

    Grp_name___________Count(Attribute1)________Month__________Qtr___Total

    Grp1________________1_____________________7_____________Q3____1    

    Grp1________________2_____________________8_____________Q3____3

    Grp1________________8_____________________9_____________Q3____11

    Grp1________________6_____________________10_____________Q4____17    

    Grp1________________4____________________11_____________Q4____21

    Grp1________________10____________________12_____________Q4____31

    Why i am finding difficulty is the only field to compare for the month and quarter is "date" field though We do have the Time dimension table.

    Can you please help me ?

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Tuesday, October 4, 2011 9:17 AM
  • Hi Radhai,

    I suggest you can use T-SQL to achive your target, and I sugghet you post the question to Transact-SQL forum as below: http://social.technet.microsoft.com/Forums/en-US/transactsql/threads. There are many experts focus on it.

    Thanks,
    Eileen 

    • Marked as answer by Eileen Zhao Friday, October 14, 2011 5:21 AM
    Thursday, October 6, 2011 9:05 AM
  • Hi ,

    In order to generate a Cumulative Total using Inbuilt SSRS functions-

    considering we have the following columns being returned by the Dataset - 1] Amount, 2] Count(Attribute1),  3] Month, 4] Qtr

    1] Use a Matrix Control and create a two Column Groupings one using the 'Month' column and the other using the 'Qtr' field

    In the above figure : I have used Prd_Id in place of Month Field and Qtr to indicate the Quarter

    2] Create a detail records using the Inscope function and the Running Value function available in SSRS

    The running value function is the one which provides you with the Cumulative Total
    The Inscope function is used here inorder to indicate where to use the Running value and where to show actual records.Since the cumulative in my example is shown like  - Jan , Feb , Mar and Q1 =(Jan+Feb+Mar)

    This can be changed by changing your scope defined for the Running value)

    example :

    =IIF( Inscope("GroupingBy_prd_id"),Fields!Amount.Value,RunningValue(Fields!Amount.Value,AVG,"Groupingby_Qtr"))

     where GroupingBy_prd_id, Groupingby_Qtr are column groupings and Fields!Amount.Value is the value that needs to be totalled

    I believe this helps

    thanks

    Umashankar

     

    Thursday, October 6, 2011 12:54 PM
  • Thanks Umashankar for your clear explanation !!!

     


    --------------------------- Radhai Krish | Golden Age is no more far | --------------------------
    Thursday, October 20, 2011 10:58 AM