{"id":1480,"date":"2015-07-20T16:42:02","date_gmt":"2015-07-20T11:12:02","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=1480"},"modified":"2015-07-20T16:42:02","modified_gmt":"2015-07-20T11:12:02","slug":"converting-datetime-based-on-user-timezone-in-ssrs-reports-for-dynamics-crm-online","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2015\/07\/converting-datetime-based-on-user-timezone-in-ssrs-reports-for-dynamics-crm-online\/","title":{"rendered":"Converting Date\/Time based on user timezone in SSRS reports for Dynamics CRM Online"},"content":{"rendered":"

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.<\/p>\n

We have covered a work around to achieve this functionality in SSRS reports for CRM online.<\/p>\n

Scenario:<\/strong><\/p>\n

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.<\/p>\n

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\u2019t show the proper data and the reason was the timzone difference between the user and the UTC timezone.<\/p>\n

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.<\/p>\n

Resolution:<\/strong><\/p>\n

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.<\/p>\n

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

    \n
  1. CRM parameter:<\/strong>\u00a0CRM_UserTimeZoneName<\/li>\n
  2. Assembly reference:<\/strong>Crm.Reporting.RdlHelper<\/li>\n<\/ol>\n

    To incorporate these changes in our report we created a parameter CRM_UserTimeZoneName<\/strong>\u00a0to store the user time zone as shown below:<\/p>\n

    \"report\"<\/a><\/p>\n

    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.<\/p>\n

    To overcome this we need to just paste the following URL in the browse textbox as shown below:<\/p>\n

    URL: Microsoft.Crm.Reporting.RdlHelper, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35<\/strong><\/p>\n

    The above URL is found in the refrences of the Account Summary report.<\/em><\/p>\n

    \"report1\"<\/a><\/p>\n

    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:<\/p>\n

    =CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(DateTime.UtcNow, Parameters!CRM_UserTimeZoneName.Value))<\/p>\n

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

    Note: If we run this report in the dev machine then it gives an error of missing references as shown below:<\/em><\/strong><\/p>\n

    \"report2\"<\/a><\/p>\n

    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.<\/p>\n

    \"report3\"<\/a><\/p>\n

    Summary<\/strong><\/p>\n

    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.<\/p>\n

    Before you move to the next post, have you seen our new\u00a0Click2Export<\/a>\u00a0Solution? A 1 click solution to export reports to Word\/Excel and Pdf. Email us on\u00a0crm@inogic.com<\/a>\u00a0for a trial or if you would like to see a live demo.<\/p>\n","protected":false},"excerpt":{"rendered":"

    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.\u2026 Read More: Converting Date\/Time based on user timezone in SSRS reports for\u2026 »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19,20,21,22,23,58],"tags":[592,1666,1667],"class_list":["post-1480","post","type-post","status-publish","format-standard","hentry","category-dynamics-crm","category-dynamics-crm-2011","category-dynamics-crm-2013","category-dynamics-crm-2015","category-dynamics-crm-2015-update-1","category-ssrs-reports","tag-dynamics-crm","tag-ssrs-reports","tag-ssrs-reports-for-dynamics-crm"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t