Interesting SSRS parameter discovery when using subscriptions

Question

I have recently been putting together an SSRS report that will run every 15 minutes for the previous 15 minute 'chunk' of time. In essence a very straight forward and simple report that will run via an automated subscription.

I was using Microsoft SQL Server 12 Report Builder Version 3.

I was alerted to an issue with the output csv when my recipient reported being sent blank files, most odd considering the report generated as expected when run manually.

Long story short, it was the expressions I was using to generate the From and To dates. Manual runs produced data, subscription runs did not.

Original parameters

FromDate

dateadd(DateInterval.Second, (second(now()) + 900) * -1, dateadd(Dateinterval.Minute, (minute(now()) mod 15) * -1, now()))

ToDate

dateadd(DateInterval.Second, (second(now()) + 1) * -1, dateadd(Dateinterval.Minute, (minute(now()) mod 15) * -1, now()))

New Parameters

FromDate

dateadd(DateInterval.Minute, -15, dateadd(DateInterval.Minute, cint(datediff(DateInterval.Minute,today(),now()) / 15) * 15, today()))

ToDate

dateadd(DateInterval.Second, 899,Parameters!FromDate.Value)

Thought I would post this here for two reasons

1. Theories as to why
2. It might help someone in the future

Answer 1

Your original parameters take Now and subtract the minutes to arrive at a 15-minutes time, then they take the seconds of another Now (which is later and could be in the next second or even minute) and subtract that value to arrive at a 0-second time (or a 59-second time). This could already cause a problem when there is a change of seconds between the first and the second Now, which isn't very unikely, as on my test system there were 0.59 seconds between the two evaluations of Now in the FromDate parameter. Also, the Now value is more accurate than just a second, and your formula does not respect that. Therefore, if the records you are trying to process in your report happen to have a time of exactly a quarter of an hour, the first parameter is for sure greater (by maybe 0.01 second) and so the record is ignored.

Your formula for the "new parameters" does not depend on the seconds of Now() and will always return a time with no fraction of a second, so I guess that that's what makes the difference.

The expression for the FromDate could be simplified a little:

=Today.AddSeconds(900*(DateDiff(DateInterval.Minute, Today, Now)\15-1))

If you do not plan to run the report very short before midnight, there should not be a problem caused by a change of the day during the evaluation of Today and Now, and you could calculate the second parameter in a similar way, independently from the first one:

=Today.AddSeconds(900*(DateDiff(DateInterval.Minute, Today, Now)\15)-1)

Answer 2

The original parameter values were being calculated individually which means they would each have slightly different values for Now(). I know this is a long shot, but it's a theory. If the subscription job fired off a fraction of a second before a 15 minute interval, it's possible that the ToDate returned just before the FromDate. This would result in an invalid date range.

With the new expressions, the ToDate is referencing the FromDate which forces them to be calculated in sequence, not in parallel. Not to mention you're adding to the FromDate which also forces the date range to have a consistent length. However, you may still run into a case where you get the same report twice if the FromDate is calculated on the wrong side of a 15 minute cutoff.

One way to test/avoid this issue would be to offset the subscription time so that it doesn't actually try to fire at the exact 15 minute cutoffs. For example, you could have it scheduled to go off 1 minute afterwards.