none
Calcualte results based on a date range SSRS expression RRS feed

  • Question

  • =IIf(dateadd("m",-1,dateserial(year(Parameters!StartDate.Value),month(Parameters!StartDate.Value),1)) >= dateadd("m",0,dateserial(year(Parameters!EndDate.Value),month(Parameters!EndDate.Value),0)) , “p”,-(Sum(CInt(Fields!CITATION_ISSUED.Value))))

    In the above expression - I'm trying to add the final sum of a field - but based on the following date range.  It's not working as it only sums the current month.

    Friday, September 20, 2019 10:59 PM

Answers

  • Hi Kitty,

    Thanks for the response.

    You could get:

    1. First Day From Last Month.

    =dateadd("m",-1,dateserial(year(Today),month(Today),1)) 

    2. Last Day From Last Month.

    =dateadd("m",0,dateserial(year(Today),month(Today),0))

    So, to implement these two expression, you need to replace the "Today" with your "from" paramter, then it should work as you want.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 13, 2019 8:30 AM

All replies

  • I have a report that runs by date from and to.  It normally includes a month of previous results.

    If I run the report, I want to also give the previous month result.  Eg.  I run it for August 1st - 15th but I want results for a special field to display all results from July 1- 31st.

    Eg.  Signs Repaired = Count(Signs Repaired) for August 1st - 15th = 30   Now I want last months result for all Signs Repaired in the month of July = 45.    The parameter start and to date is just for August 1st - 15th.  July's result is through SSRS expressions. 

    I got as far as this...

    =IIF(IsNothing(Parameters!EndDate.Value), "",DateAdd(DateInterval.Month,-1,Today()))

    Wednesday, September 11, 2019 7:20 PM
  • Hi KittyCat,

    One question about your description,  e.g. when the parameter is 9/12/2019, you want to get additional data from 8/12/2019 to 8/31/2019 or  you want to get date from 8/1/2019 to 8/31/2019 ? Or else?

    If what you want is second one, you expression seems not fit this situation. 

    Also, could you share us, how would you like to use this calculated previous month value? Maybe some screenshot helps.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 12, 2019 2:09 AM
  • Lukas,

    I would like the second situation.   Want to get date from 8/1/2019 to 8/31/2019.

    Eg.

    Signs Repaired for August (1st-15th)            (XYZ % increase from July)

    I'm trying to create a expression that compares the current month's data against the last months data.

    Thursday, September 12, 2019 3:38 PM
  • Hi Kitty,

    Thanks for the response.

    You could get:

    1. First Day From Last Month.

    =dateadd("m",-1,dateserial(year(Today),month(Today),1)) 

    2. Last Day From Last Month.

    =dateadd("m",0,dateserial(year(Today),month(Today),0))

    So, to implement these two expression, you need to replace the "Today" with your "from" paramter, then it should work as you want.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 13, 2019 8:30 AM
  • =IIf(dateadd("m",-1,dateserial(year(Parameters!StartDate.Value),month(Parameters!StartDate.Value),1)) >= dateadd("m",0,dateserial(year(Parameters!EndDate.Value),month(Parameters!EndDate.Value),0)) , “p”,-(Sum(CInt(Fields!CITATION_ISSUED.Value))))

    In the above expression - I'm trying to add the final sum of a field - but based on the following date range.  It's not working as it only sums the current month.

    Friday, September 20, 2019 12:03 AM
  • Hi, 

    In you expression, seems it only judges if the startdate is earlier than EndDate. It hasn't do anything to the sum() function or its scope, so the sum won't change accordingly. I don't know what is this citation_issued field. You need to provide more information so that we could understand you scenario and offer possible help.

    Regards.

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 5:57 AM
  • Lukas, I found a simpler solution by going through SQL. Thankyou for trying. I think I got lost with the scripting and it didn't do what I wanted it to.  

    Select *

    from Test

     where [STOP_DT] between DATEADD(MONTH, -1, @StartDate) 
     and  DATEADD(MONTH, DATEDIFF(MONTH, -1, @EndDate)-1, -1)

    I can then pull from this dataset the values that are 1 month old.

    Tuesday, October 1, 2019 11:52 PM