Introduction:

Converting Date/Time values according to a time zone is quite challenging task in SSRS Reporting. For D365 CRM online we can achieve this using CDate Function. I will demonstrate how to use CDate function with a dynamic time-zone parameter.

Step 1:

Add this reference to the report properties:

Click on “References” and then click on “Add” button under “Add or remove assemblies”.

Browse the following file from your BIDS folder.

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

Click on Ok button to save the changes.

Step 2:

Create the parameter in the report data section in the “Parameters” folder as shown below:

In the “Default Values” section check the click on “Add” Button.

Enter the User Time Zone in the Formula and click on OK to Save the Changes.

Step 3:

To use this functionality is simply as follows:

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

Example:

If the Expression or Formula is as follow:

Last 30 =WeekdayName(Weekday(DateSerial(Year(Fields!msdyn_dateValue.Value), Month(Fields!msdyn_dateValue.Value),”1″).AddMonths(1).AddDays(-30)))

The Replace it with:

Last 30= WeekdayName(Weekday(DateSerial(Year(DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value)))), Month(DateValue(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!msdyn_dateValue.Value, Parameters!CRM_UserTimeZoneName.Value)))),”1″).AddMonths(1).AddDays(-30)))

Note:

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

We have to replace all the formulae which contain the DateTime field value which is retrieved directly from FetchXML with the above mentioned Function to avoid issues in SSRS reports due to Time Zone Conversion.