locked
SSRS LYTD Calculations RRS feed

  • Question

  • I have the following matrix report layout:

                                |    YEAR
                                |   Program

     SalesRep | Month | Sum(Last Year Stuff) | Sum(This Year Stuff)

    How do I calculate YTD for 2013, 2014?

    • I tried this expression below, it worked on  month level (showing Jan) but YTD Total showed 12 month total.

    = round(Sum(iif(Fields!month_date.Value>DatePart(DateInterval.Month,Parameters!date.Value),0, (Fields!Prev12MonthStuff.Value))))

    • I tried to put sum(ReportItems!TextboxStuff.Value) into YTD (group total) row, but got an error:
    Aggregate function can be used only on report itemscontained in page headers and footers”



    • Edited by BrBa Wednesday, January 28, 2015 4:10 PM
    Wednesday, January 28, 2015 3:46 PM

Answers

  • Hi BrBa,

    Per my understanding that you want to calculate the YTD value of each year based on the Calendar date parameter, right?

    I have some doubt about what the expect value of YTD you mean, for example, you have mentioned that if Parameters!date.Value='1/27/15' , you will expect to see LYTD sales up to 1/31 for 2013,2014,2015 ,If that means get the value of the Month Jan of each year like below, right?

                  2013                          2014                  2015   
    YTD  17,813   24,184         24,184   14,814      31,486   51,616

    If you select "2/01/2015", it will calculate the YTD for each year of (Jan + Feb) ?

    I have tested on my local environment that using the expression as you have provided can get the aggregate value of the months based on the data in the parameter as below:

    = round(Sum(iif(Fields!month_date.Value>DatePart(DateInterval.Month,Parameters!date.Value),0, (Fields!Prev12MonthStuff.Value))))



    You can see the expression works fine as you expected.

    Please provide the sample data of all the field of the table in the DB and also more details information about the YTD values you want to get if I have some misunderstanding.

    Any problem, please feel free to ask.

    Regards
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Vicky_Liu Wednesday, February 4, 2015 1:17 AM
    • Marked as answer by Vicky_Liu Thursday, February 5, 2015 12:59 AM
    Thursday, January 29, 2015 3:35 AM

All replies

  • I don't think I understand. Year to Date for a year that is completed will be the total for that year (12 months). What are you expecting in that case?

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Wednesday, January 28, 2015 5:33 PM
  • Let's call it "Last Year to date"

    Say, for Parameters!date.Value='1/27/15' I'd expect to see LYTD sales up to 1/31 of the respective years : 1/31/2013,1/31/2014,.. .

    I was wondering if I could filter somehow on report level and yes - the expression worked for group detail. but it does not work for group total/footer.

    Sum(iif(Fields!month_date.Value>DatePart(DateInterval.Month,Parameters!date.Value),0, (Fields!Prev12MonthStuff.Value))))

    • Edited by BrBa Wednesday, January 28, 2015 5:58 PM
    Wednesday, January 28, 2015 5:43 PM
  • Hi BrBa,

    Per my understanding that you want to calculate the YTD value of each year based on the Calendar date parameter, right?

    I have some doubt about what the expect value of YTD you mean, for example, you have mentioned that if Parameters!date.Value='1/27/15' , you will expect to see LYTD sales up to 1/31 for 2013,2014,2015 ,If that means get the value of the Month Jan of each year like below, right?

                  2013                          2014                  2015   
    YTD  17,813   24,184         24,184   14,814      31,486   51,616

    If you select "2/01/2015", it will calculate the YTD for each year of (Jan + Feb) ?

    I have tested on my local environment that using the expression as you have provided can get the aggregate value of the months based on the data in the parameter as below:

    = round(Sum(iif(Fields!month_date.Value>DatePart(DateInterval.Month,Parameters!date.Value),0, (Fields!Prev12MonthStuff.Value))))



    You can see the expression works fine as you expected.

    Please provide the sample data of all the field of the table in the DB and also more details information about the YTD values you want to get if I have some misunderstanding.

    Any problem, please feel free to ask.

    Regards
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Vicky_Liu Wednesday, February 4, 2015 1:17 AM
    • Marked as answer by Vicky_Liu Thursday, February 5, 2015 12:59 AM
    Thursday, January 29, 2015 3:35 AM