Category Archives: Dynamics 365 Integrations

Microsoft Dynamics 365 and SSRS Reports: Dates and Time

In this article, we deep dive into the Microsoft Dynamics 365 Customer Engagement Date and Time field type, as well as explore the implications of this field type on SQL Server Reporting Services (SSRS) reports depending on the behavior and format selected. We demonstrate how to make SSRS reports to display Date and Time fields in the user’s time zone. Also, we look at how the different behaviors and formats of the Date and Time field type are stored in the SQL database. The included use case provides a real world scenario of where the information in this article came in handy.

Use Case

Inspiration

Recently on a client engagement, a bug was raised on a couple of SSRS reports I had built a few months ago. A date field on the reports was displaying the next day i.e. if a record was approved yesterday, the report was showing that it was approved today in some cases. Looking at the records from the Dynamics 365 user interface, i.e. views, Advanced Find and forms, the field was displaying the correct date despite the SSRS reports were showing that same date as the next day. This behavior was only observed on some of the records created by the clients.

I thought of sharing the problem solving approach and tools I used to resolve this bug with the hope the it may help another Microsoft Dynamics 365 software developer in the future.

What was going on

The field in question it is a Date and Time field but the reports are configured to show the date part of the field only, as per the requirements. After investigations, it turned out that the SSRS reports were consuming a Date and Time field type (Behavior: User Local. Format: Date and Time), without translating it into the user’s time zone. Therefore, if a user approved a record between 8pm and Mid-night EST, the date of approval would show up on the SSRS report as the following day. This is because if a Date and Time field type’s behavior is set to User Local, the actual data is store in UTC time but translated to the user’s time zone when displayed to the user in the Dynamics 365 user interface. Unlike the Dynamics 365 user interface, SSRS reports do not automatically provide the translation to the user’s time zone but is can be implemented as shown in the SSRS Reports section below. Therefore, the source of the discrepancy between the date seen by users in Dynamics 365 user and what was being shown in the report lies in the fact that the Dynamics 365 user interface was translating the UTC date into the user’s local time while the report was showing the date as it was recorded in the SQL database. As the client is based in the EST, the bug could only be replicated, under the same conditions, between 8pm and Mid-night EST, when EST and UTC exist on two calendar dates.

Some of the client’s employees was creating the data between 8pm and Mid-night EST. As the field’s behavior is set to User Local, in the four hours time window (8pm to Mid-night EST), UTC and EST are on two different dates, and records approved in period will stored on the following day in UTC, which the database time for User Local behavior fields.

The Tools to Debug the Problem

View the Behavior and Format of Date and Time FIelds

Open the Date and Time field in the Dynamics 365 solution and see how it is saved:
Date and Time field's Behavior and Format

Date and Time Fields: Behavior and Format

The table below shows the different behaviors and formats of Date and Time fields in Dynamics 365, and well as their implications.

BehaviorFormatChanging field’s behavior
User Local Note: This is the behavior of all date and time fields in the previous releases.

– The field values are displayed in the current user’s local time.
– In Web services (SDK), these values are returned using a common UTC time zone format.
Date Only – or – Date and TimeIn the user interface (UI), you can change certain out-of-the-box entity field’s behavior from the User Local to Date Only. For a list of entities and fields, see Changing the field behavior to Date Only on upgrade. You can change the custom entity field’s behavior from the User Local to Date Only or to Time-Zone Independent.

Changing the field behavior affects the field values that are added or modified after the field behavior was changed. The existing field values remain in the database in the UTC time zone format. To change the behavior of the existing field values from UTC to Date Only, you may need a help of a developer to do it programmatically. More information: Convert behavior of existing date and time values in the database. Warning: Before changing the behavior of a date and time field, you should review all the dependencies of the field, such as business rules, workflows, calculated fields, or rollup fields, to ensure that there are no issues as a result of changing the behavior. After changing the behavior of a date and time field, you should open each business rule, workflow, calculated field, and rollup field dependent on the field that you changed, review the information, and save it, to ensure that the latest date and time field’s behavior and value are used. You can restrict modifying the field’s behavior, by setting the CanChangeDateTimeBehavior managed property to False. More information: Set managed property to change date and time behavior
Date Only

– The concept of a time zone isn’t applicable to this behavior. The field values are displayed without the time zone conversion.
– The time portion of the value is always 12:00AM.
– The date portion of the value is stored and retrieved as specified in the UI and Web services (SDK).
Date OnlyThe Date Only behavior can’t be changed to other behavior types, once it’s set.
Time-Zone Independent

– The concept of a time zone isn’t applicable to this behavior. The field values are displayed without the time zone conversion.
– The date and time values are stored and retrieved as specified in the UI and Web services (SDK).
Date Only – or – Date and TimeThe Time-Zone Independent behavior can’t be changed to other behavior types, once it’s set.
Source: Microsoft

.

Date and Time fields in Dynamics 365 User Interface vs. SQL Database

Dynamics 365 User Interface: Date and Time fields

Here are the different possible combinations of storing simple dates and time data in Dynamics 365 (on a classic interface form for the Contact entity):
Date and Time fields on a Form

SQL Database: Date and Time fields

In this section, we will look at how the date and time fields in the previous section are stored in the database. The date and time fields are on the Contact entity (i.e. ContactBase table in my Dynamics 365 SQL database). Querying for the specified columns in the table where the full name is ‘Peter Parker’:

SELECT TOP (10)
       [FullName]    
      ,[hos_uselocal_dateonly]
      ,[hos_uselocal_dateandtime]
      ,[hos_dateonly_dateonly]
      ,[hos_timezoneindependent_dateonly]
      ,[hos_timezoneindependent_dateandtime]
  FROM [House_MSCRM].[dbo].[ContactBase]
  WHERE FullName = 'Peter Parker'

See the results below:
Viewing Date and Time fields in the datebase

From the database results above, it is worth noting:

  • Behavior of User Local and Format of Date Only: these fields are always stored as 4am in my database in EST, which is 12am UTC.
  • Behavior of User Local and Format of Date and Time: in my time zone, i.e. EST, these fields are always stored 4 hours ahead, i.e. UTC time, in the database. In contrast, in the user interface, this field always shows the data in my time zone.
  • Behavior of Time Zone Independent and Format of Date and Time: stores the data in the database as inputted in the user interface and does not respect time zones.

SSRS Reports: Display Dates and Time in the user’s time zone

There are two ways to create SSRS Reports for Dynamics 365. You can create a report in Dynamics 365 using the Report Wizard, export it and add to your Report Server Project in Microsoft Visual Studio IDE, with the built-in fields and parameters, from Dynamics 365. Alternatively, you can take the opposite path of creating a blank Report Server Project in Microsoft Visual Studio IDE, connecting to Dynamics 365, and adding the built-in fields and parameters as needed. If you take the former approach, you will get the built-in fields and parameters in Visual Studio, similar to the image below:
Visual Studio - built in fields and parameters

To translate UTC date and time data, for User Local behavior, in the SQL database into the user’s time zone, you have to use the SSRS Report formula below, where “CRM_UserTimeZoneName” is a parameter and <SSRSDateAndTimeField> is name of your Date and Time field in SSRS:

=Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(
<SSRSDateAndTimeField>, Parameters!CRM_UserTimeZoneName.Value)

“CRM_UserTimeZoneName” is a parameter passed in from Dynamics 365, containing the user’s time zone. The function “Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime” iconverts UTC time to the user’s time zone. Therefore, when the user runs the report, they will see the date and time data in their time zone, despite it is stored as UTC by default in the SQL database.

Here are some application examples, where “Fields!hos_uselocal_dateandtimeValue.Value” is the SSRS date and time field in Visual Studio:

  • Display User Local Date and Time SSRS data field in the user’s time zone (showing both date and time data):
=Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(
Fields!hos_uselocal_dateandtimeValue.Value, Parameters!CRM_UserTimeZoneName.Value)
  • Display User Local Date and Time SSRS data field in the user’s time zone (showing the date component only without the time component of the date, in the format dd/MM/yyyy):
=Format(CDate(Microsoft.Crm.Reporting.RdlHelper.DateTimeUtility.ConvertUtcToLocalTime(Fields!hos_uselocal_dateandtimeValue.Value,
Parameters!CRM_UserTimeZoneName.Value)), "dd/MM/yyyy")

Dynamics 365 Online: Accessing the contents of your SQL database

In Dynamics 365 Online, you do not have access to the SQL database that hosts your Dynamics 365 organization. However, Microsoft allows you to access the data in your SQL database by duplicating and syncing to Microsoft Azure. From Azure, you can connect to duplicated SQL database and be able to run the SQL script in this article. For more details duplicating and syncing your Dynamics 365 online SQL database to Microsoft Azure, see: Microsoft Dynamic 365 Data Export Service: Duplicating and Syncing SQL database in Azure

How to Integrate Microsoft Dynamics 365 Online with SharePoint and OneNote

In this post, we cover how to integrate Microsoft Dynamics 365 Online with SharePoint and OneNote. We start off with why you should consider integrating Dynamics 365 with SharePoint and OneNote. Secondly, a full step-by-step guide is provided on how to create a SharePoint Site for Dynamics 365 document management. Thirdly, a detailed guide on how to integrate Microsoft Dynamics 365 Online with SharePoint Online is provided. We conclude with an elaborate guide of how to integrate Microsoft Dynamics 365 Online with OneNote.

Why Consider Integrating Dynamics 365 with SharePoint and OneNote?

  • Cost: It is cheaper to store documents in Microsoft SharePoint than in Microsoft Dynamics 365 online.
  • Enhanced document Management functionality: SharePoint online is a Microsoft platform that specializes in document management and hence has more enhanced features focused on document management e.g. creating sub-sites, information rights management, SharePoint business intelligence, etc.
  • Enhance collaboration: teams can retain the great Dynamics 365 collaboration tools while also utilizing the advance document management features of SharePoint.
  • Enhanced productivity: by allowing users to access documents in multiple places. If someone is working in SharePoint, they do not need to go back to Dynamics 365 to access documents uploaded in Dynamics 365.
  • Seamless user experience: Despite the documents are stored in SharePoint after the integration, to a Dynamics 365 Online user, it feels like the documents still reside in Dynamics 365. The user experience is not compromised by this integration.

How To Create a SharePoint Site for Dynamics 365 Document Management

Step 1: Login in to https://admin.microsoft.com as a global or SharePoint administrator. If you see a notification that you have no permission to access the page, you do not have SharePoint administrator rights or Office 365 global administrator permissions in your organization.

Step 2: In the left pane, under Admin centers, select SharePoint. You might need to select Show all to see the full list of admin centers. This will take take you to the SharePoint home page.

Step 3: On the SharePoint home page, click Create site to start the process of creating a SharePoint site.
SharePoint Home Page

Step 4: You will get pop up window, asking you to choose the type of SharePoint site you would like to create. Choose Team site:
Choosing type of SharePoint site to create

Step 5: Enter details about your team site. Take note of Site Address, we will need it later when integrating Microsoft Dynamics 365 with this SharePoint team site. Afterwards click Next.
3.2. Enter details about the SharePoint Site

Step 6: You can add members and/or additional owners of the site. This is not a required step, you can come and add site owners and additional members later after you have created the site. Click Finish to complete the process of creating your SharePoint site.
Add site members and additional owners

Step 7: After Step 6, your new site will be created and it will look similar to:
New SharePoint team site

Integrating Dynamics 365 Online With SharePoint

For this demonstration, we are using version 9.1.0000.9825 of Dynamics 365 (2019 release wave 2 enabled) online. However, the integration process is the same or very similar in other versions of Dynamics 365/CRM online.

Step 1: In your Dynamics 365/CRM instance, login with the System Administrator and/or System Customizer security role. Then navigate to Settings >> Document Management. On this page, click on Configure Server-Based SharePoint Integration.
D365 Document Management Page

Step 2: You will get the following pop up, asking you where your SharePoint sites are located. In this demonstration, we are working with Dynamics 365 online and SharePoint online, so the Online option is selected. Afterwards, click Next.
Choose location of SharePoint site

Step 3: Enter the URL of the SharePoint Site (see Step 5 of section How To Create a SharePoint Site for Dynamics 365 Document Management above). A check will be conducted on whether the URL you have provided is a valid SharePoint online site and if it exists in the same Office 365 tenant as your Dynamics 365 organization. After enabling server-based SharePoint integration, you cannot go back to the client-side integration, the default setting prior to this integration.  If you would like to proceed, click Next.
Enter SharePoint Site URL

Step 4: After your SharePoint site has been validated by Dynamics 365, click Finish.
Validation of SharePoint site by Dynamics 365

Step 5: In Dynamics 365, go to Settings >> Document Management. You will notice that the Configure Server-Based SharePoint Integration option, mentioned in Step 1, is no longer available. This is because we have correctly configured Dynamics 365 with SharePoint. On this page, now click on Document Management Settings to complete the process of integrating Dynamics 365 with SharePoint.
Document Management Settings

Step 6: After executing the previous step, the window below will pop up, where you can select the Dynamics 365 entities you would like to enable for document management in SharePoint. Afterwards click Next to continue the integration process.
Configure Dynamics 365 entities for document management

Step 7: Select the SharePoint folder structure for the entities selected in the previous step. In the example below, I have selected a folder structure based on entities. Afterwards, click Next.
Choose SharePoint folder structure

Step 8: The following window will pop up. Click OK, if you would like to proceed with the creation of document libraries in the provided SharePoint site.
SharePoint Document Libraries Creation

Step 9: Wait until the document libraries have been created. After the document libraries have been created you will get a window similar to the one below, notifying you that the process has been completed. Click Finish.
Document Library Creation Process Complete

You have successfully enabled Dynamics 365 for document management in SharePoint. For the entities enabled, files uploaded for their records will be stored in SharePoint and you can make utilize the full range of document management capabilities offered by SharePoint. Your documents will be accessible in both Dynamics 365 and SharePoint.

To view the SharePoint sites enabled for document management in your Dynamics 365 organization, go to Settings >> Document Management. Click on SharePoint Sites. You will get a window similar to the one below:
Active SharePoint Sites for Dynamics 365 Organization

To view the Dynamics 365 entities’ folders created in SharePoint, navigate to the SharePoint team site you provided above. In the left navigation panel, click on Site Contents. You will get a window similar to:
Dynamics 365 entities' folders in SharePoint

Integrating Dynamics 365 Online With OneNote

Step 1: In Dynamics 365, go to Settings >> Document Management. On this page, click on OneNote Integration.
OneNote Integration

Step 2: After executing the previous step, the window below will pop up, where you can select Dynamics 365 entities you would like to enable for OneNote Integration. Click Finish to complete the process.
Choose the entities to enable for OneNote Integration

You have successfully integrated Dynamics 365 with OneNote. For records of the enabled entities, notes made in Dynamics 365 will be synced with OneNote in SharePoint Online. Also, you can access these notes on your desktop (i.e. OneNote Desktop) and mobile devices (i.e. OneNote Mobile).