CODEWITHSUNDEEP
sql-serverreporting-servicesreportbuilder3.0sql-server-data-tools
James Wilson
James Wilson

Reputation: 5150

Dynamic Date Range in SSRS or SQL Data Tools builder

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.

Upvotes: 0

Views: 764

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

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))))

Upvotes: 3

Related Questions