Help in getting Monthly cumulative total

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 | --------------------------

Answer 1

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/

Answer 2

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 | --------------------------

Answer 3

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 

Answer 4

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

 

Answer 5

Thanks Umashankar for your clear explanation !!!

 

---

--------------------------- Radhai Krish | Golden Age is no more far | --------------------------