December 5, 2016 at 8:01 am
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
December 5, 2016 at 9:17 am
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.
Larnu.uk
December 5, 2016 at 2:01 pm
Thanks, going to try this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply