SSRS: Specifying a Default Value for a Date/Time Parameter

This blog post covers how to specify a default parameter value for a Date/Time Parameter in a report written using Microsoft SQL Server Reporting Services (SSRS). You can easily modify this to specify default values for other types of parameter.


Assuming you have your report already open in Report Designer and you've just run it. The report we're going to deal with has a single parameter "INVOICE_DATE". Click "Design" at the top left:

Expand the Parameters item in the "Report Data" treeview (on the left):

Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:Highlight "INVOICE_DATE", right-click and then select "Parameter Properties". The "Report Parameter Properties" dialog will now appear:

We're interested in changing the "Default Value" so select that tab:

Select the "Specify values" radio group:

Click "Add":

Then click the function button (highlighted) on the right of the new line:

Enter the expression:

=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

(You're probably wondering where I got this from - type "reporting services first day of the month" into Google - it's the first result).

This expression will give you the first day of the PREVIOUS month. Click "OK". Click "OK". Click "OK" (you should be back to the main designer now with no additional dialogs open).

Click "Run", your report Parameter will now have a default value (and if it's the only parameter your report will now run automatically).

SSRS: Commonly Used Date/Time Functions

The list below is a few simple functions which I've come across for generating various dates based on the current date/time (using the Now() function).

Hopefully they'll prove useful and as new ones come up I'll update this post;

Last day of the current year,
=DateSerial(Year(Now()), 12, 31)

Last day of previous month
=DateAdd(""d"", -1, DateSerial(Year(Now()), Month(Now()), 1))

First date of previous month
=DateAdd(""m"", -1, DateSerial(Year(Now()), Month(Now()), 1))

First day of last year
=DateSerial(Year(Now())-1, 1, 1)

Last day of last year
=DateSerial(Year(Now())-1, 12, 31)

Last day of this year
=DateSerial(Year(Now()), 12, 31)

First day of this year
=DateSerial(Year(Now()), 1, 1)