How to Get the Time Zone Value from Dynamics 365 to SSRS Reports

Adam Murchison, 15 October 2017

If you’ve ever written custom SSRS Reports then you’ll know the difficulty of converting from UTC to your users desired time zone. There are two ways of retrieving a date field from Dynamics 365 and each way returns a different time-zone. One way is using mag_dateField.Value, this is the user local time but as a string rather than a DateTime object. Using CDate on a string does not work if the string is in the incorrect format, e.g. “12:00 a.m.” rather than “12:00 AM”. This is a common occurrence which can be extremely frustrating and difficult to work around. Using mag_dateFieldValue.Value returns the field in a DateTime object, however this in UTC time, rather than the users local time.

I will dive into how to use the CDate function with a dynamic time-zone parameter. In CRM OnPrem a date can be converted from UTC to the desired time zone using: System.TimeZoneInfo.ConvertTimeBySystemTimeZoneId(Fields!mag_dateField.Value, “New Zealand Standard Time”).

Unfortunately, this is not supported for CRM Online and it is not dynamic because every user could have a different time zone.

To work around this issue, I have found a list of CRM parameters displayed on Microsoft's website and added the appropriate parameter to the SSRS report.

Create the parameter in the report data section in the ‘Parameters’ folder as shown below:

The default value for the CRM_UserTimeZoneName parameter shown above gets overwritten by the  CRM users time zone who is running the report so feel free to leave this blank.

Add this reference to the report properties:

Microsoft.Crm.Reporting.RdlHelper, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
 

Finally, to use this functionality is simply as follows:

CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!mag_dateFieldValue.Value, Parameters!CRM_UserTimeZoneName.Value))

It is important to note that in this case I am using the mag_dateFieldValue.Value which is the UTC time zone. This value will now be dynamically converted to the user’s time-zone who ran this report.

This is an easy way of dynamically converting a time zone from UTC to the user’s local time using CRM Online.