August 2, 2011 at 10:29 am
How to get the average and total amount of last three months or quater based on the paramter @runyear
example I have runyear =2011 IN SSRS
i need avg on "PAmount column " for that quater(three months) grouped on cname and LOCdesc
I have a DATA which looks like this
Cname | LOC desc | PMonth | PAmt
A DENT 2011-03-01 300
A DENT 2011-06-01 600
B DENT 2011-03-01 800
C MED 2011-06-01 700
C MED 2011-03-01 300
Needed : Based on given Year(as parameter) 2011 Need totals in Quaters like
Cname | LOC desc | PMonth | PAmt
A DENT 2011-03-01 300
QUATER 1 TOTAL : 300
B DENT 2011-03-01 800
Quater 1 Total :800
C MED 2011-03-01 300
Quater 1 Total :300
A DENT 2011-06-01 600
Quater 2 Total :600
C MED 2011-06-01 700
Quater 2 Total :700
QUATER2 TOTAL : 700
thanks,
Komal
August 2, 2011 at 10:35 am
Make a calendar table. Here's mine : http://www.sqlservercentral.com/Forums/Attachment8839.aspx
Then SELECT * FROM dbo.Calendar WHERE Y = @Year
will give you all the dates for that year. You'll also notice all the groups you can possibly ever want.
Left join the calendar to your final dataset and then group BY whatever you need.
I usually do this for the columns GROUP BY Year, then Quarter then month (3 levels). SSRS makes the final sum for this.
August 2, 2011 at 10:50 am
I am getting the parameter values from the Store proc for @runyear and also data!!!!
August 2, 2011 at 10:52 am
I don't understand your problem with that!
August 2, 2011 at 12:13 pm
Hi...I see the table you sent have month names in diff language ?? Can I get the one with English please???
Or It maight not make difference???
Thanks,
komal
August 2, 2011 at 12:20 pm
Just use datename and update the whole table.
You'll need to set the correct us holidays as well. I've used canadian holidays here so some of them are different.
August 2, 2011 at 1:27 pm
Updated the table.But still did not get how to merge the dataset (created from storeproc) with the calender table? to get as one dataset in ssrs???
On what column to join???
August 2, 2011 at 1:31 pm
Join on dt (date).
August 2, 2011 at 3:22 pm
Hey thank you....I got it by quaterly.Now I need to get average and totals of the past three months "Pamt" for each "cname" across all " LOCDESC".This is Different report.
August 2, 2011 at 3:25 pm
where dt BETWEEN DATEADD(M, -3, GETDATE()) AND GETDATE() (or use @endate if the user needs this as a parameter....
August 2, 2011 at 3:50 pm
I dont want to change anything to storeproc , but add as a seperate column as average : to display average for past three months( from current month) and also totals for past three months ( from current month ) ...just add as an expression??
August 2, 2011 at 3:56 pm
sure add a new column similar to this :
IIF(date > whatever, Value, 0)
August 3, 2011 at 8:17 am
Hi ...can you please be more specific....? i did not understand??
August 3, 2011 at 8:22 am
In the dataset and a new column. Hit expression. Then put the IIF()
August 3, 2011 at 8:34 am
Hey...I know how to add expressions....but did not understand your iff condition!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply