As many of you may know, when writing custom reports for CRM 4.0 you are required to use the filtered views. The upside is filteredViews make security a snap for reporting against CRM data, and they offer a consistent dataset between the user will see in the CRM UI and what they will see in CRM reports. For instance, when a CRM user has access to CRM data via their role privileges they will see that same data in a report, but if the user doesn't have access to the data in CRM they won't be able to surface that data in a report either. What you may have noticed when writing and running reports against filtered views is a sluggish response when selecting, filtering, or ordering by date/time values in your report. Let's break down what's going on and what you can do about it:
All date/time data in CRM are stored in the database as UTC values. To make sure the times are displayed to users in their local timezone, a SQL User Defined Function (UDF) is used to convert the values from the database tables to local time in the filtered views. When running a query via the filtered views against a large dataset, every record in the dataset must be evaluated by this function to determine the local date/time value, which can add significant overhead to your report.
Because all the date/time data values are stored in UTC format, reports that query against the UTC time will circumvent the UDF, and the query time will be reduced. However, this will alter the data returned by the query and could also impact your conditions if the report contains a date/time parameter . The solution we will explore in this article is:
You can use the same functions that CRM utilizes to convert the date/time values, however, instead of having SQL do this conversion for all the data, we can convert the final dataset making the report run much quicker. The process we're going to use is:
In this sample we have a report that will fetch a list of accounts with the account name, modified date/time, and who modified the record. The user running the report selects a beginning date and ending datetime as a parameter. Your two parameter names in the report are named: "@beginDateTime" and "@endDateTime".
Your current TSQL statement:
To convert the report over to using UTC a few modifications need to be done in the report.
Select DATEDIFF(mi, '1/15/2011 00:00:00.000',dbo.fn_UTCToTzSpecificLocalTime('1/15/2011 00:00:00.000', us.TimeZoneBias, us.TimeZoneDaylightBias, us.TimeZoneDaylightYear, us.TimeZoneDaylightMonth, us.TimeZoneDaylightDay, us.TimeZoneDaylightHour, us.TimeZoneDaylightMinute, us.TimeZoneDaylightSecond, 0, us.TimeZoneDaylightDayOfWeek, us.TimeZoneStandardBias, us.TimeZoneStandardYear, us.TimeZoneStandardMonth, us.TimeZoneStandardDay, us.TimeZoneStandardHour, us.TimeZoneStandardMinute, us.TimeZoneStandardSecond, 0, us.TimeZoneStandardDayOfWeek)) as ToLocalTime, DATEDIFF(mi, '1/15/2011 00:00:00.000',dbo.fn_TzSpecificLocalTimeToUTC('1/15/2011 00:00:00.000', us.TimeZoneBias, us.TimeZoneDaylightBias, us.TimeZoneDaylightYear, us.TimeZoneDaylightMonth, us.TimeZoneDaylightDay, us.TimeZoneDaylightHour, us.TimeZoneDaylightMinute, us.TimeZoneDaylightSecond, 0, us.TimeZoneDaylightDayOfWeek, us.TimeZoneStandardBias, us.TimeZoneStandardYear, us.TimeZoneStandardMonth, us.TimeZoneStandardDay, us.TimeZoneStandardHour, us.TimeZoneStandardMinute, us.TimeZoneStandardSecond, 0, us.TimeZoneStandardDayOfWeek))as ToUTCTimeFROM FilteredUserSettings us where systemuserid = dbo.fn_FindUserGuid()
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.
@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!