SSRS convert UTC datetime value to local datetime

For any Dynamics 365 developer, at one point in time they might encounter issues with the datetime fields in CRM. The challenge always comes when we working on backend, because there is always a confusion in the mind whether the date time value is UTC or local time.

When working in reports, fetch-xml dataset have two values for each datetime types, the field name with just the attribute name (e.g. createdon) is the formatted value with time zone added, another one contains the UTC time which is attributenameValue (e.g. createdonValue). Formatting the datetime field in the report using the formatted value i.e. createdon will have issues as it will have issue converting the string value to datetime. In this cases, we need to use the UTC datetime field value, but the problem is we need to localize the date in order to show the data correctly in the report.

Lets see how we can achieve converting the UTC datetime to Local Time of the CRM. First thing is to reference the crm utility assembly in the report property.

Step 1: Navigate to the report properties.

Step 2: Click on Add button under the Add or remove assemblies section. Click on the three ellipses to choose the “Microsoft.Crm.Reporting.RdlHelper” assembly and choose that. This assembly is part of the BIDS installation that you carry out during the development environment setup. Then click OK.

The report will work even if you reference the older version of the assembly like 7.0.0.0. The only difference is if you don’t reference the correct version that is installed in your local machine, the report won’t render locally in Visual Studio. It will only work when you import in to CRM.

Step 3: Now we can able to use the custom function from the assembly that we referred in the previous step. Below is the formula to convert the datetime from Utc to Local Time.

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

In the above code, instead of hardcode the time zone string, use the hidden parameter field to get the time zone value.