SSRS daily subscription for monthly report

Question

I have a SSRS report which is executed and delivered daily to emails. The report calculates some numbers for the 1st date of the month to the (d-1)th date (d being the day when it is executed). So the default parameter values in this report are :

Start_date :=DateSerial(Year(Date.Now), Month(Date.Now), 1)
End_date :=DateAdd("d",-1,Today())

The problem is on 1st of every new month , the start date evaluates to 1st of new month and the end date becomes the last date of previous month. This makes the report non-sensical on 1st of every month. What should we do to avoid this and force the expression to evaluate 1st of the new month as start and end date ?

Nathan Griffiths Nathan Griffiths · Accepted Answer · 2015-04-21T01:59:28

For the End Date, perhaps check if it's the 1st of the month and then default to the current date (i.e. the 1st) if it is:

=Iif(
    Day(DateTime.Today) = 1,
    DateTime.Today,
    DateAdd("d",-1,DateTime.Today)
    )

But I'm assuming there is a reason your are using today-1 as the end date, so this could mean your report shows nothing for the 1st day of the month...

SSRS daily subscription for monthly report

1 Answers