Defaul Date Parameters When it's the 1st of the Month

  • mbrady5

    SSCrazy

    Points: 2835

    Hi,

    I am looking for a method to default my date parameters in a report.

    Ideally, I'm looking to run a Month to Date Report.

    Every morning the report runs on subscription that shows MTD through the end of "yesterday". There seems to be an issue on the 1st of the Month that is messing-up the results.

    I am looking to replace my start and end date parameters so it works on the first of every month.

    When it's December 1st and the report runs at 8AM, I want to see the results for entire Month of November.

    My current Params that are not working as I had hoped are;

    Start: =DateSerial(Year(Now()), Month(Now()), 1)

    End: =IIF(DATEPART(DateInterval.Day,Today())=1,Today(),DATEADD(DateInterval.Day,-1,Today()))

    Thanks for any help/Simplification

  • Thom A

    SSC Guru

    Points: 98522

    Personally I use the same logic in SSRS that I use in SQL, which is:

    DateFrom

    =DATEADD("M", DATEDIFF("M", "01/01/2000", DATEADD("d", -1,TODAY())), "01/01/2000")

    DateTo

    =DATEADD("d", -1,TODAY())

    Today, that would return 01-Dec-2016 and 04-Dec-2016 respectively. Running on 01-Dec-2016 would return 01-Nov-2016 and 30-Nov-2016.

    Edit: In case you want to understand the logic as it doesn't make sense at first:

    --Get Today's Date

    05-Dec-2016

    --Minus one day

    04-Dec-2016

    --Difference in Months from 01 January 2000 to 04 December 2016

    203

    --Add 203 Months to 01 January 2000

    01-Dec-2016

    /*

    So, for 01 December 2016

    */

    --Get Today's Date

    01-Dec-2016

    --Minus one day

    30-Nov-2016

    --Difference in Months from 01 January 2000 to 30 November 2016

    202

    --Add 202 Months to 01 January 2000

    01-Nov-2016

    The DateTo should make sense to you! 😎

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • mbrady5

    SSCrazy

    Points: 2835

    Thanks, going to try this.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply