Converting Date/Time based on user timezone in SSRS reports for Dynamics CRM Online

By | July 20, 2015

Converting Date/Time values according to a timezone is quite easy in languages like javascript, C#, etc. but achieving this in SSRS reports is a challenging task. For CRM online we can achieve this using CRM parameter in SSRS reports.

We have covered a work around to achieve this functionality in SSRS reports for CRM online.

Scenario:

We had a request from one of our clients in which the client used to run a report on the last day of every month.

The client used to pay commisions to the users as per the goals achieved by them in the month, so he runs the report on the last day of the month. Somehow the report when ran didn’t show the proper data and the reason was the timzone difference between the user and the UTC timezone.

Since the report was made to show records of the current month so when the client ran the report on May 31 it unexpectedly showed the details of June because till that time the date has changed to June 01 in UTC.

Resolution:

After so much googling also we could not find a way to tackle this situation. So we tried to know the principles used in the OOB reports of CRM to handle this situation as they always run without any issues and show the perfect data all the time.

To know what is the logic used in the OOB reports we downloaded the Account Summary report available in system and we found two things that are used to achieve this functionality:

  1. CRM parameter: CRM_UserTimeZoneName
  2. Assembly reference:Crm.Reporting.RdlHelper

To incorporate these changes in our report we created a parameter CRM_UserTimeZoneName to store the user time zone as shown below:

report

After adding the parameter in the report we need to add the assembly refrence, to add the refrence go to Report Properties and click on the References tab. Now click on the Add button, since the assembly we need to add in the report is only available on the reporting server so we cannot browse and add a URL to it.

To overcome this we need to just paste the following URL in the browse textbox as shown below:

URL: Microsoft.Crm.Reporting.RdlHelper, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35

The above URL is found in the refrences of the Account Summary report.

report1

After adding the assembly reference we need to initialize a variable which will contain the user current datetime. To do this we use the following code:

=CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(DateTime.UtcNow, Parameters!CRM_UserTimeZoneName.Value))

In the above code, we have converted the UTC time to user local time using the CRM_UserTimeZoneName parameter so it provides us the flexibility to run the report on the last day of each month.

Note: If we run this report in the dev machine then it gives an error of missing references as shown below:

report2

But when this report is rendered in the online environment then it loads the referenced assembly from the CRM online environment and shows the proper data.

report3

Summary

It is clear from the above example that we can use the CRM parameters to make our reports user-friendly and to show user-specific data.

Before you move to the next post, have you seen our new Click2Export Solution? A 1 click solution to export reports to Word/Excel and Pdf. Email us on crm@inogic.com for a trial or if you would like to see a live demo.