SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

How to default SSRS date parameters to the first and last day of the the previous month

Populating default dates in SSRS can be helpful to save the user from having to constantly input the date range they normally would use. When a report is pulled for last month’s information, defaulting the date fields for the user can help streamline their usage of the report, instead of them manually selecting with the date-picker control in SSRS.
The formula’s I used were:

'Beginning of Current Month (EOM)
DateSerial(Year(Date.Now), Month(Date.Now), 1)

'Beginning of Last Month (BOM)
DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

'End of Last Month (EOM)
DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

To set the default date of the parameters:
1. First open up the Report Data Window, and choose your date parameters.

2. Navigate to Default values, and click the Fx button to edit the expression for the field.

3. Paste the formula into the expression field and save.

Result: Your default dates should now show last month’s date range. You can apply your own rounding or date types if you wish, this provides the time as well, since I was working with smalldatetime, datetime, and datetime2 datatypes.

Sheldon Hull

After working in the mortgage industry for 7 years, I transitioned into Business Intelligence and began learning SQL and .NET. My goal is to integrate my business knowledge into my development to intelligently analyze and find solutions to problems. Blogging offers me an option to share what I've learned as well as receive feedback on better practices and solutions.


Leave a comment on the original post [www.bitbarbarian.com, opens in a new window]

Loading comments...