How To Default The Start And End Date of Any Month in SQL Reporting Services

It seems, from experience, that most of the Microsoft SQL Reporting Services reports I write these days, have a requirement for a date range set via parameters. Whilst you can just let your users select the dates they want the report to run, you can also provide default values that are then changeable or not. So I thought I would explain in this very short post how to default a StartDate parameter to be the 1st of the current month and an EndDate parameter to be the last day of the current month. In Visual Studio select Report > Report Parameters.

StartDate

Click Add and enter the following:

Name: StartDate
Data type: DateTime
Prompt: Start Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=CDate(“01/” & Month(Now) & “/” & Year(Now))

EndDate

Click Add and enter the following:

Name: EndDate
Data type: DateTime
Prompt: End Date

In the Default values section select the Non-queried radio-button and enter the expression below in the textbox next to it:

=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, Parameters!StartDate.Value))

Click OK.

All we are doing here is taking the previously calculated StartDate parameter (now, for example, 01/10/2008) and adding a month to it, before subtracting one day. This allows us to get the last day of the month irrespective of how long the month is or whether it is a leap year or not.

Final Word Of Caution Re Defaults

Default parameters make life a lot easier for end users but they must be used very carefully in SQL Reporting Services because, a report will automatically execute if all the required parameters are defaulted. As such if the users generally are not accepting your defaults then they should be remove as it is placing an extra query against the database for no reason. Alternatively, add another parameter that is not defaulted so that, whilst the dates pre-populate, the report wont run until that parameter is set.

Hope this was of help. As always, feedback (positive or negative) is always welcome.

• parameters
• SQL Reporting Services