I am making a report that will run on the 1st of every month and on the 16th of every month.
If it is running on the 1st I need the @start_date to be the 16th to the last day of the previous month.
If it is running on the 16th I need @start_date to be the 1st of the month through the 15th of the month.
I can think of a couple ways to do this, but I am curious is SSRS/Report Builder/SQL Data Tools builder has an easy method for setting this up.
My option was to make a SQL query that does what I need then plug that into the Get Balues from a query part of the parameter.
If you run the report in the first part of the month then the parameters should be set to the 16th to the end of the previous month; if run in the second part of the month then the parameters should be set to the 1st to the 15th of the current month.
@start_date Default Value expression:
=IIF(Day(Today) >= 16, DateAdd(DateInterval.Day, 1-Day(Today), Today), DateAdd(DateInterval.Month, -1, (DateAdd(DateInterval.Day, 16-Day(Today), Today))))
@end_date Default Value expression:
=IIF(Day(Today) >= 16, DateAdd(DateInterval.Day, 15-Day(Today), Today), DateAdd(DateInterval.Day, -1, (DateAdd(DateInterval.Day, 1-Day(Today), Today))))