6 Nov’19

Blog, D365 Sales, Dynamics 365, Dynamics CRM, Others, Power BITime zone Conversion from Microsoft D365 for SSRS Reporting

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.

Written by

Rushank Karekar

Software Engineer

Leave a Reply

Your email address will not be published. Required fields are marked *

Want to streamline your business processes?

  • This field is for validation purposes and should be left unchanged.

Recent Articles

  • Performing Update operation in Microsoft Dynamics NAV through Integration in Scribe Online

    13 November’ 2019

    Introduction: Use the Update Block to update existing records in the source or target datastore. While integrating w...

    Read more
  • Adding Microsoft Dynamics NAV Connection in Tibco Scribe Online

    13 November’ 2019

    Introduction: Establishing a connection is an important step before developing a solution for Integration. While integra...

    Read more
  • Time zone Conversion from Microsoft D365 for SSRS Reporting

    6 November’ 2019

    Introduction: Converting Date/Time values according to a time zone is quite challenging task in SSRS Reporting. For D365...

    Read more
  • How to Synchronize Office 365 and Zoho People?

    4 November’ 2019

    If your organization is using Zoho People and Office 365, you will find this article very useful. In this article we are...

    Read more
  • Check Plugin Timeout issues using Tracing in D365 CE

    31 October’ 2019

    In Dynamics plugin implementations, you might have lengthy complex plugins that makes calls to Dynamics several times. I...

    Read more