Report parameter default value to 1st and last day of previous month

Problem

For a report that selects data in a period with a start and end date, I needed to create a subscription that is sending an email the beginning of each month with the data from the previous month.

The report has 2 parameters @FromDate and @ToDate. The dataset of the report uses a select statement with a where condition: … WHERE Reportdate BETWEEN @FromDate and @ToDate.

The ReportDate is a DATE field (not a Datetime field)

when the user opens the report, he will be shown the 2 parameter fields with a Datetime picker to select a date from a calendar. But with a subscription the parameter fields need to have a default value. In this case the default value must be the 1st day and the last day of the previous month.

Solution with SQL Date field

The FromDate parameter has an expression as default value. The expression calculates the 1st day of the previous month:

=DateSerial(Year(DateAdd(DateInterval.Month, -1, Today())), Month(DateAdd(DateInterval.Month, -1, Today())), 1)

The ToDate paramet has a similar expression as default value. The expression calculates the last day of the previous month (no matter how many days). It does so by calculating the first day of the current month with DateSerial and then it subtracts one day with DateAdd.

=DateAdd(DateInterval.Day, -1, DateSerial(Year(Today()), Month(Today()), 1))

In case of SQL Datetime field

2 possibilities: either use the CAST function in the select statement, and keep the parameters and default expressions as they are for the Date field:

… WHERE CAST(Reportdate AS Date) BETWEEN @FromDate and @ToDate

Or leave the select statement as it is and change the default value of the ToDate parameter, by not subtracting one Day but the smallest necessary timeinterval. Howver the smallest interval in Reporting Services code is ‘Second’. If your datatime fields contain millisceconds you cannot give an absolute guarantee that it will catch all possible values. So only use this if you will never have values between 23:59:59.000 and 23:59:59.999

=DateAdd(DateInterval.Second, -1, DateSerial(Year(Today()), Month(Today()), 1))