Tag Archives: Format

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

Microsoft Dynamics 365: The Complete Guide to Getting and Setting Fields Using a Plugin

When extending the Microsoft Dynamics 365 platform, there is often a need to retrieve and/or set field values. In this post, we will cover how to get and set values for the Microsoft Dynamics 365/CRM platform fields using a plugin, developed using the Microsoft C# programming language. You can also develop plugins and perform the operations demonstrated in this post using other .Net platform-supported programming languages. In Dynamics 365, you can create the following types of fields (or datatypes): Single Line of Text, Option Set, MultiSelect Option Set, Two Options, Image, Whole Number, Floating Point Number, Decimal Number, Currency, Multiple Lines of Text, Date and Time, Lookup and Customer.

Plugin Set Up

Here is a plugin setup you can use to implement the field specific code in this post.

using System;
using System.Collections.Generic;
using Microsoft.Xrm.Sdk;

namespace ItsFascinating_Plugins_D365
{
    public class PreUpdateAccount : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            string pluginMessageUpdate = "update";
            Entity primaryEntity = new Entity();

            if (serviceProvider == null)
            {
                throw new ArgumentNullException("localContext");
            }                

            #region Plugin Setup Variables
            IPluginExecutionContext context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            IOrganizationServiceFactory serviceFactory = (IOrganizationServiceFactory)serviceProvider.GetService(typeof(IOrganizationServiceFactory));
            IOrganizationService service = serviceFactory.CreateOrganizationService(context.InitiatingUserId);
            ITracingService tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
            #endregion
            #region Validation Prior Execution
            //Exist if plugin if it's not called at stage 20 of the excution pipeline
            if (context.Stage != 20)
            {
                tracingService.Trace("Invalid Stage: Stage = {0}", context.Stage);
                return;
            }
            //Exist if plugin if it called for any other operation other than Update 
            if (!context.MessageName.ToLower().Equals(pluginMessageUpdate))
            {
                tracingService.Trace("Invalid Message: MessageName = {0}", context.MessageName);
                return;
            }
            #endregion
            #region Plugin Logic
            if (context.InputParameters.Contains("Target") && context.InputParameters["Target"] is Entity)
            {
                try
                {
                    primaryEntity = context.InputParameters["Target"] as Entity;


                    //INSERT THE FIELD SPECIFIC CODE BELOW HERE


                }
                catch(Exception ex)
                {                 
                    throw new InvalidPluginExecutionException(string.Format("Error occured in the PreUpdateAccount plugin: {0}", ex.Message));
                }
                
            }
            #endregion
        }
    }
}               

Field Specific Code

To use the field specific code below, in the plugin above, copy the code below and paste it below the line “//INSERT THE FIELD SPECIFIC CODE BELOW HERE”, in the Plugin Setup Code section.

Single Line of Text

Here is an example of a Single Line of Text field on a Dynamics 365 form:
Single Line of Text

Here is the C# code for getting and setting the value of a Single Line of Text field (Display Name: “Account Number” | Database Name: “accountnumber”):

//Get and Set Single Line of Text field value
// Display Name: "Account Number" | Database Name: "accountnumber"
string accountNumberFieldLogicalName = "accountnumber";
Object accountNumberObj;
string accountNumber;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(accountNumberFieldLogicalName, out accountNumberObj))
{
	//Get value of Single Line of Text field
	accountNumber = Convert.ToString(accountNumberObj);

	//Set value of Single Line of Text field
	primaryEntity[accountNumberFieldLogicalName] = "ACC-1000-TV-2019-ON";
}

Multiple Lines of Text

Here is an example of a Multiple Lines of Text field on a Dynamics 365 form:
Multiple Lines of Text

Here is the C# code for getting and setting the value of a Multiple Lines of Text field (Display Name: “Description” | Database Name: “description”):

//Get and Set Multiple Lines of Text field value
// Display Name: "Description" | Database Name: "description"
string descriptionFieldLogicalName = "description";
Object descriptionObj;
string description;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(descriptionFieldLogicalName, out descriptionObj))
{
	//Get value of Multiple Lines of Text field
	description = Convert.ToString(descriptionObj);

	//Set value of Multiple Lines of Text field
	primaryEntity[descriptionFieldLogicalName] =
		"To be, or not to be, that is the question: \n" +
		"Whether \'tis nobler in the mind to suffer \n" +
		"The slings and arrows of outrageous fortune, \n" +
		"Or to take Arms against a Sea of troubles, \n" +
		"And by opposing end them: to die, to sleep; \n" +
		"No more; and by a sleep, to say we end \n" +
		"The heart-ache, and the thousand natural shocks \n" +
		"That Flesh is heir to? \'Tis a consummation \n" +
		"Devoutly to be wished.To die, to sleep, \n" +
		"perchance to Dream; aye, there\'s the rub, \n" +
		"For in that sleep of death, what dreams may come, \n" +
		"When we have shuffled off this mortal coil, \n" +
		"Must give us pause.";
}

Whole Number

Here is an example of a Whole Number field on a Dynamics 365 form:
Whole Number field

Here is the C# code for getting and setting the value of a Whole Number field (Display Name: “Number of Employees” | Database Name: “numberofemployees”):

//Get and Set Whole Number field value
// Display Name: "Number of Employees" | Database Name: "numberofemployees"
string numberOfEmployeesFieldLogicalName = "numberofemployees";
Object numberOfEmployeesObj;
int numberOfEmployees;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(numberOfEmployeesFieldLogicalName, out numberOfEmployeesObj))
{
	//Get value of Whole Number field
	numberOfEmployees = Convert.ToInt32(numberOfEmployeesObj);

	//Set value of Whole Number field
	primaryEntity[numberOfEmployeesFieldLogicalName] = 120000;
}

Decimal Number

Here is an example of a Decimal Number field on a Dynamics 365 form:
Decimal Number field

Here is the C# code for getting and setting the value of a Decimal Number field (Display Name: “Exchange Rate” | Database Name: “exchangerate”):

//Get and Set Decimal Number field value
// Display Name: "USD/CAD Exchange Rate" | Database Name: "hos_usdcadexchangerate"
string exchangeRateFieldLogicalName = "hos_usdcadexchangerate";
Object exchangeRateObj;
decimal exchangeRate;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(exchangeRateFieldLogicalName, out exchangeRateObj))
{
	//Get value of Decimal Number field
	exchangeRate = Convert.ToDecimal(exchangeRateObj);

	//Set value of Decimal Number field
	primaryEntity[exchangeRateFieldLogicalName] = 1.312775;
}

Floating Point Number

Here are some examples of Floating Number fields on a Dynamics 365 form:
Floating Point Number field

Here is the C# code for getting and setting the value of a Floating Number field (Display Name: “Address 1: Longitude” | Database Name: “address1_longitude”):

//Get and Set Floating Point Number field value
// Display Name: "Address 1: Longitude" | Database Name: "address1_longitude"
string longitudeFieldLogicalName = "address1_longitude";
Object longitudeFieldObj;
float longitudeField;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(longitudeFieldLogicalName, out longitudeFieldObj))
{
	//Get value of Floating Point Number field
	longitudeField = Convert.ToSingle(longitudeFieldObj);

	//Set value of Floating Point Number field
	primaryEntity[longitudeFieldLogicalName] = -79.387054f;
}

Currency

Here is an example of a Currency field on a Dynamics 365 form:
Currency field

Here is the C# code for getting and setting the value of a Currency field (Display Name: “Annual Revenue” | Database Name: “revenue”):

//Get and Set Currency field value
// Display Name: "Annual Revenue" | Database Name: "revenue"
string revenueFieldLogicalName = "revenue";
Object revenueObj;
decimal revenue;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(revenueFieldLogicalName, out revenueObj))
{
 //Get value of Currency field                                               
 revenue = ((Money)revenueObj).Value;

 //Set value of Currency field
 primaryEntity[revenueFieldLogicalName] = new Money(10200500800.78m);
}

Two Options

Here are some examples of Two Options fields on a Dynamics 365 form:
Two Options field

Here is the C# code for getting and setting the value of a Two Options field (Display Name: “Email” | Database Name: “donotemail”):

//Get and Set Two Options field value
// Display Name: "Email" | Database Name: "donotemail"
string dontAllowEmailsFieldLogicalName = "donotemail";
Object dontAllowEmailsObj;
bool dontAllowEmails;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(dontAllowEmailsFieldLogicalName, out dontAllowEmailsObj))
{
	//Get value of Two Options field                                               
	dontAllowEmails = (bool)dontAllowEmailsObj;

	//Set value of Two Options field
	primaryEntity[dontAllowEmailsFieldLogicalName] = true;
}

Option Set

Here is an example of an Option Set field on a Dynamics 365 form:
Option Set field

Here is the C# code for getting and setting the value of an Option Set field (Display Name: “Contact Method” | Database Name: “preferredcontactmethodcode”):

//Get and Set Option Set field value
// Display Name: "Contact Method" | Database Name: "preferredcontactmethodcode"
string contactMethodLogicalName = "preferredcontactmethodcode";
Object contactMethodObj;
int contactMethod;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(contactMethodLogicalName, out contactMethodObj))
{
	//Get value of Option Set field 
	contactMethod = ((OptionSetValue)contactMethodObj).Value;

	//Set value of Option Set field
	primaryEntity[contactMethodLogicalName] = 3;
}

MultiSelect Option Set

Here is an example of a MultiSelect Option Set field on a Dynamics 365 form:
MultiSelect Option Set field

Here is the C# code for getting and setting the values of a MultiSelect Option Set field (Display Name: “Geographical Areas of Operation” | Database Name: “hos_geographicalareasofoperation”) :

//Get and Set MultiSelect Option Set field value
// Display Name: "Geographical Areas of Operation" | Database Name: "hos_geographicalareasofoperation"
string locationFieldLogicalName = "hos_geographicalareasofoperation";
Object locationFieldObj;
OptionSetValueCollection locationField;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(locationFieldLogicalName, out locationFieldObj))
{
	//Get the collection of value(s) for MultiSelect Option Set field
	locationField = (OptionSetValueCollection)locationFieldObj;

	//Set collection of value(s) for MultiSelect Option Set field (version 1 - single line implementation)
	primaryEntity[locationFieldLogicalName] = new OptionSetValueCollection(new List<OptionSetValue>() { new OptionSetValue(183840000), new OptionSetValue(183840001), new OptionSetValue(183840003), new OptionSetValue(183840006), new OptionSetValue(183840010) });

	//Set collection of value(s) for MultiSelect Option Set field (version 2 - multiple lines implementation)
	/*
	OptionSetValueCollection locationMultiOptionSet = new OptionSetValueCollection();
	locationMultiOptionSet.Add(new OptionSetValue(183840000));
	locationMultiOptionSet.Add(new OptionSetValue(183840001));
	locationMultiOptionSet.Add(new OptionSetValue(183840003));
	locationMultiOptionSet.Add(new OptionSetValue(183840006));
	locationMultiOptionSet.Add(new OptionSetValue(183840010));
	primaryEntity[locationFieldLogicalName] = new OptionSetValueCollection(locationMultiOptionSet);
	*/
}

Date and Time

Here are some examples of Date and Time fields on a Dynamics 365 form:
Date and Time field

Here is the C# code for getting and setting the value of a Date and Time field (Display Name: “Follow Up Date” | Database Name: “hos_followupdate”):

//Get and Set Date and Time field value
// Display Name: "Subscription Start" | Database Name: "hos_subscriptionstart"
// Display Name: "Subscription End" | Database Name: "hos_subscriptionend"
string subscriptionStartLogicalName = "hos_subscriptionstart";
string subscriptionEndLogicalName = "hos_subscriptionend";
Object subscriptionStartdObj;
DateTime subscriptionStart = new DateTime();

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(subscriptionStartLogicalName, out subscriptionStartdObj))
{
	if (subscriptionStartdObj != null)
	{
		//Get value of Date and Time field  
		subscriptionStart = Convert.ToDateTime(subscriptionStartdObj);

		//Set value of Date and Time field   
		primaryEntity[subscriptionEndLogicalName] = subscriptionStart.AddYears(1);
	}
	else
	{
		//If the Subscription Start is null, set the Subscription End as well
		primaryEntity[subscriptionEndLogicalName] = null;
	}
}

Lookup

Here is an example of a Lookup field on a Dynamics 365 form:
Lookup field

Here is the C# code for getting and setting the value of a Lookup field (Display Name: “Account Manager” | Database Name: “hos_accountmanager”) :

//Get and Set Lookup field value
// Display Name: "Account Manager" | Database Name: "hos_accountmanager"
string accountManagerLogicalName = "hos_accountmanager";
Object accountManagerObj;
EntityReference accountManager;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(accountManagerLogicalName, out accountManagerObj))
{
	if (accountManagerObj != null)
	{
		//Get value of a Lookup field and its attributes                                              
		accountManager = (EntityReference)accountManagerObj;
		Guid guid = accountManager.Id;
		string entityName = accountManager.LogicalName;
		string name = accountManager.Name;

		//Set value of a Lookup field and its attributes                        
		primaryEntity[accountManagerLogicalName] = new EntityReference("systemuser", new Guid("A6AF4DAB-10C4-E911-A2E2-005056AE4389"));
	}
}

Customer

Here is an example of a Customer field on a Dynamics 365 form:
Customer field

Here is the C# code for getting and setting the value of a Customer field (Display Name: “Main Customer” | Database Name: “hos_maincustomer”):

//Get and Set Customer field value
// Display Name: "Main Customer" | Database Name: "hos_maincustomer"
string mainCustomerLogicalName = "hos_maincustomer";
Object mainCustomerObj;
EntityReference mainCustomer;

//Check if the specified attribute is contained in the internal dictionary before you you try to Get its value
if (primaryEntity.Attributes.TryGetValue(mainCustomerLogicalName, out mainCustomerObj))
{
	if (mainCustomerObj != null)
	{
		//Get value of Customer field and its attributes                                              
		mainCustomer = (EntityReference)mainCustomerObj;
		Guid guid = mainCustomer.Id;
		string entityName = mainCustomer.LogicalName;
		string name = mainCustomer.Name;

		//Set value of Customer field and its attributes                        
		primaryEntity[mainCustomerLogicalName] = new EntityReference("account", new Guid("2E4D98F9-8EF9-E911-A2E8-005056AE4389"));
	}
}

Extra Details

The plugin demonstrated in this post was developed on Microsoft Dynamics 365 version 9.0.3.7 and Microsoft Dynamics 365 SDK version 9.0.2.12.