Speeding up sluggish CRM reports that use date/time parameters

re: Speeding up sluggish CRM reports that use date/time parameters

Sean McNellis — Tue, 15 Mar 2011 16:54:11 GMT

@DoingThisForAWhile - good observation. You're correct, some additional logic could be added to individually adjust each date/time value on the report. Especially relevant with the daylight savings time shift this past weekend. I've seen customers hard code their offsets in the report before or report in UTC to make the reports run quicker and the intent was to offer another way that would be more dynamic on a per user basis. This is not quite a one size fits all solution - and if we can find a way to write a step-by-step posting that could be applied to all reporting in which all the date/times would be masked we'll post one.

I'll look to try and revisit this posting to account for DST in each value - but it will be a much more extensive posting in which I may have to re-write fn_UTCToTzSpecificLocalTime such that it could be used in each SSRS dataset and also recreate this functionality to be used within the report as an SSRS function. Thanks for pointing this out!

re: Speeding up sluggish CRM reports that use date/time parameters

DoingThisForAWhile — Mon, 14 Mar 2011 13:25:19 GMT

This system makes one very important assumption: that all dates shown in the report or needing conversion in the report are in the same daylight savings bias. In the example above, the term January 15th is hard coded into the TimezoneConversions dataset and subsequently into the MinutesToUTC parameter which is used then for conversion. This query should accept an input parameter, perhaps @beginDateTime, as the date which the function uses to calcuate MinutesToUTC. However, it is evident even in this scenario that the beginDateTime and the endDateTime could have two different biases.

In many reports it is common to have multiple dates listed. This is another scenario in which only have one MinutesToUTC parameter is limiting. If one date is in daylight savings time and another is not, one of them will be wrong.

This is a clever idea, and I may implement it in the right circumstances, but I don't think that it is as useful as adverstised. At least not in the countries which observe daylight savings.