Post reply

SSRS 2008 R2 Dev question

  • August 2, 2011 at 10:29 am

    #241565

    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

    #1362329

    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

    #1362343

    I am getting the parameter values from the Store proc for @runyear and also data!!!!

  • August 2, 2011 at 10:52 am

    #1362349

    I don't understand your problem with that!

  • August 2, 2011 at 12:13 pm

    #1362459

    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

    #1362472

    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

    #1362550

    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

    #1362559

    Join on dt (date).

  • August 2, 2011 at 3:22 pm

    #1362685

    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

    #1362694

    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

    #1362725

    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

    #1362732

    sure add a new column similar to this :

    IIF(date > whatever, Value, 0)

  • August 3, 2011 at 8:17 am

    #1363078

    Hi ...can you please be more specific....? i did not understand??

  • August 3, 2011 at 8:22 am

    #1363082

    In the dataset and a new column. Hit expression. Then put the IIF()

  • August 3, 2011 at 8:34 am

    #1363099

    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