Querying Dynamics CRM using PowerBi - Query Folding

When you use the Common Data Service connector, then PowerBi will attempt to fold your query into a single call.

As an example:

When you look at the query in the Advanced Editor, this is what it shows:

let
    Source = Cds.Entities("https://xxxx.api.crm.dynamics.com/api/data/v9.1/", [ReorderColumns=null, UseFormattedValue=null]),
    entities = Source{[Group="entities"]}[Data],
    territories = entities{[EntitySetName="territories"]}[Data],
    #"Filtered Rows" = Table.SelectRows(territories, each [createdon] > #datetimezone(2019, 10, 1, 0, 0, 0, -6, 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"createdon", "name"})
in
    #"Removed Other Columns"

And this is the query that goes out (removed url encoded characters for legibility):

/api/data/v8.2/territories?$filter=createdon gt 2019-10-01T00:00:00-06:00&$select=territoryid,createdon,name&$top=1000

As you can see, even though the query was broken out over 4 steps, there was only one call that went out to CRM, which is very cool!

Merge options provided by Visual Studio DevOps

I really like how Visual Studio (devOps) shows a visual hint for each type of merge that can be done via its interface. Here they are:

Merge (no fast forward) Nonlinear history preserving all commits git merge pr where “pr” is the name of the branch.
Squash Commit (in AWS CodeCommit this is called Squash and Merge) Linear history with only a single commit on the target Deleting the source branch when squashing is recommended git merge pr --squash
Rebase (in AWS CodeCommit this is called Fast Forward Merge) Linear history with all commits rebased onto the target git rebase master git merge pr --ff-only
Semi-linear merge (in AWS CodeCommit this is called 3-way Merge) Rebase source commits onto target and create a two-parent merge git rebase master git merge pr --no-ff

For more info: 

Azure: https://devblogs.microsoft.com/devops/pull-requests-with-rebase/

AWS: Merge a pull request in an AWS CodeCommit repository - AWS CodeCommit (amazon.com)

PowerBi DataFlows–Filtering by Date

The cool feature of incremental refresh is only available to PowerBi Premium instances. So, what do the poor folk do? Well, we fake it!

The following is based on Dynamics CRM data, which provides the modifiedon field for all entities, but it can be extended to use any date field. You simply use the Table.SelectRows function to filter based on the ModifiedOn field. (the example shows how I did it on the “SystemUser” entity).

let
   Source = Cds.Entities(https://xxxxx.crm.dynamics.com, null),
   #"Navigation 1" = Source{[Group = "entities"]}[Data],
   #"Navigation 2" = #"Navigation 1"{[EntitySetName = "systemusers"]}[Data],
  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 2", {{"modifiedon", type datetime}}),
   #"Filtered By Date" = Table.SelectRows(#"Changed column type", each DateTime.From([modifiedon]) >= DateTime.From("2018-01-01"))
in
   #"Filtered By Date"

Dynamics Ribbons

Base ribbon definitions can be found at: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/customize-dev/export-ribbon-definitions

Account limits for Application Users in Dynamics 365

There has been considerable confusion on whether an "application user" needs a license or not. In June 2018, Microsoft has clarified that Application Users do not count towards the limits and do not use up licenses:

All application users are created with a non-interactive user account, however they are not counted towards the five non-interactive user accounts limit. In addition, there is no limit on how many application users you can create in an instance.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/admin/create-users-assign-online-security-roles#create-an-application-user
https://github.com/MicrosoftDocs/dynamics-365-customer-engagement/commit/9068938aa7ca91f12b8744518bf5071ec4514530#diff-184905bb3786c535b8e1eedb5ba54cf2R258

And for the steps to create the Application user, see: https://blog.aggregatedintelligence.com/2017/02/headless-authentication-against-crm-365.html

Modelling Dynamics 365 SystemUser hierarchy in PowerBI

The SystemUser entity in Dynamics 365 is a self referencing entity, that defines its hierarchy based on the parentSystemUserId field.

So how do you go about showing the hierarchy and utilizing it in report?

Here is one way that I found, that utilizes the DAX Path function.

1. Go to the data view and select the “SystemUser” table.
2. Add a new column and define your first field using the following function.
   ManagerHierarchy = Path(SystemUser[systemuserid],SystemUser[parentsystemuserid])
   
   This will add a column that lists all the managers of a systemuser, delimited by the pipe character “|”
3. Add another new column to show the depth of the current user
   HierarchyDepth = PATHLENGTH ( SystemUser[ManagerHierarchy] )
   
   This will add a column with integer values starting at 1 and going up to the max-depth of the record found in your system
4. Next, add columns, for each of the following functions (I went up to 5, as that was the max I had in my system)
   
   

   [ManagerFirstLevel] = LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 2, TEXT ) )

   ManagerSecondLevel = if (SystemUser[HierarchyDepth] >= 3,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 3, TEXT ) ), SystemUser[ManagerFirstLevel])

   ManagerThirdLevel = if (SystemUser[HierarchyDepth] >= 4,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 4, TEXT ) ), SystemUser[ManagerSecondLevel])

   ManagerFourthLevel = if (SystemUser[HierarchyDepth] >= 5,      LOOKUPVALUE (      SystemUser[fullname],      SystemUser[systemuserid],      PATHITEMREVERSE ( SystemUser[ManagerHierarchy], 5, TEXT ) ), SystemUser[ManagerThirdLevel])

   
   Things to note:
   PathItem is used to get the value at a particular depth from the Path generated field (in this case SystemUser[ManagerHierarchy]). LookUpValue is then used to lookup that value’s fullname, so that it cant make for a pretty display value).
   In my case I had to use PathItemReverse, so that a depth of 2 would give me the first manager’s value and so on. If I just used PathItem, I would have had to reverse my depth values.
5. You now will have a SystemUser table with the names of each manager at the different levels. You could rename the columns, to have actual title names (Manager, Vice President, etc).
6. The final thing I did was to setup the hierarchy on the fields:
   
   By doing this, you can get drill downs working. The hierarchy settings shown above will start at the bottom most level (employee) and as you click the drill down button, it will move up the hierarchy. If instead you wanted it to work the other way, you can just reorder it in reverse.

Things to know:

1. This works only if you know the number of levels you are dealing with. So, the same concept would theoretically break on Accounts, which could be nested many levels deep.
2. This was modeled after this power-pivot sample: https://www.daxpatterns.com/parent-child-hierarchies/

Dynamics 365–Open URL in a new window from SiteMap

The way I have found to be able to do this is by adding a web-resource HTML file with the following code:

<html>      <head>         <title>New Window</title>      </head>     <body>          <script type="text/javascript">              function OpenWindow(url)              {                  debugger;                var newWindow = window.open(url,"_blank");                window.history.back();                if (window.focus) {                        newWindow.focus();                    }              }              debugger;              window.onload=OpenWindow("https://www.bing.com");          </script>          Opening new window….      </body> </html>

Then add a web-resource sub-item onto the SiteMap and point it at the web resource. That should do it!

Note about UCI: in UCI, a URL always opens in a new window, which is a change in behavior from the web-legacy interface and so you dont need the above hack to open the URL in a new window

Embed PowerBi Report in Dynamics CRM system dashboard

Its easy to create a dashboard that either includes the entire PowerBi dashboard (Power BI Dashboard) option.

Or you can include pieces from a PowerBi Dashboard (Dynamics 365 Dashboard and add a PowerBi tile)

The problem with this option is that they are “personal” dashboards and so you have to share them with users. This is not ideal when you have to share the dashboard with all users. Dashboards that can be made available to all users are system dashboards. But system dashboards cannot include PowerBI dashboards or tiles (why! oh! why!)

Here is a less than ideal workaround (not ideal, as you cant share a PowerBi dashboard or tile). You can only embed a full powerBi report (because we will be embedding it as an iframe).

1. First, you need to make your report available for embedding. You do this by opening your PowerBi report online (app.powerbi.com) and then click File >> Embed
   
2. The next dialog will provide you a URL, copy it
   
3. Create a new Dashboard by going to “Customizations” in Dynamics CRM
4. Add an iFrame component onto your dashboard and set the url to the one you copied above:
   

That will make your entire PowerBi dashboard available on the Dynamics CRM dashboard.

PowerBi–Dynamics 365 (Online) Connector vs Common Data Service for Apps Connector

Its been a while since I have looked at the CDS connector, so I thought I would go looking and here are some things I found:

PowerBi desktop version: December 2018

The CDS connector is still in beta!

And still provides the warning:

The one big thing I noticed right of the bat is that the dialog doesnt allow the usage of parameters (more on a work around later).

There are 2 new settings (that I havent seen earlier): Reoder columns and Add display column. Setting them both to true leads to a couple of very awesome improvements over the the Dynamics Online connector:

First, the columns are all alphabetized! If you have ever had to look for a column with the old connector (we have 200 fields on account), you know how amazing having ordered columns is!

Second, is that you now get the display value in addition to the actual value! hallelujah!

Once you select your entities and load your source, you see the next nice difference, tables are named with their singular names instead of the plural names. This used to drive me crazy and I like using the singular names, so in my opinion – cool!

In the image above, the first set of tables was loaded using the Dynamics 365 (online) connector. The 2nd one was created using the Common Data Service for Apps connector. Something to note is that you can still access the old named tables under the “System” node when you are selecting the entities.

Back to the issue of not being able to use parameters:

If you click on the “Advanced Editor”, you can update the connection to use a parameter:

let
     Source = Cds.Entities(#"Sales CRM URL", [ReorderColumns=true, UseFormattedValue=true]),
     entities = Source{[Group="entities"]}[Data],
     accounts = entities{[EntitySetName="accounts"]}[Data]
in
     accounts

In the example above, "Sales CRM URL" is a parameter that I have defined and points at the CRM api url. And the CDS connection still works. So its just the dialog that doesnt support parameters, but you can still use the parameter in the queries. (Be advised that you wont be able to open the dialog again for the connection.

Another thing I wanted to look at was what the file size looked like using the CDS connector vs the D365 connector and they were pretty much the same (and in fact the CDS connector:

All in all, the new CDS connector works great and also includes many of the entities that didnt show up before (eg: leads). I just wished that it would graduate from beta soon!

Test

Test

Dynamics 365 - Copying Notes from a Lead to an Account

We had a requirement to copy notes from a lead onto an account, when the lead is qualified. There is no OOB way to do this, but I found a Workflow library called “msdyncrmWorkflowTools” that has a step that can do this. Here is how:

The step is called “Clone Children”: https://github.com/demianrasko/Dynamics-365-Workflow-Tools/blob/master/docs/Clone%20Children.md

First install the Workflow library by downloading the solution from https://github.com/demianrasko/Dynamics-365-Workflow-Tools/releases and importing the solution into your CRM solution.

1. Next create a workflow process that is attached to the “Account” entity.
   
   
2. After you save it, open the newly created process.
3. Set the scope to “Organization” (not a required step)
4. Set the execute as to “owner of the process” (not a required step)
5. And set the process to run when the “record is created”
   
6. Next add a check condition to see if the account’s Originating Lead has been set, which would signal that the account was being created from a lead.
   
7. Next add the Clone Children step from “msdyncrmWorkflowTools (1.0.54.0)
   
8. Set the properties to the following values:
   
    
   The relationship name is case-sensitive, so enter it as Lead_Annotation.
   The new parent field, which is used for the old parent field name is objectid.
9. Save and activate the process and test it!

XrmToolbox–useful build settings

1. ILMerge
   Used, when you have multiple assemblies and you need to package them together.
   Stage: Post Build Step
   Sample:
   
   

ilmerge.exe /target:library /targetplatform:v4,"C:\Windows\Microsoft.NET\Framework\v4.0.30319" "/out:CombinedModuleName.dll" "inputModule1.dll" "inputModule2.dll" "inputModule3.dll"

2. Copy the plugin to a plugins folder
   This makes it easier to tell XTB to load the dll you are developing
   
   
if $(ConfigurationName) == Debug (
  IF NOT EXIST Plugins mkdir Plugins
  move /Y  "CombinedModuleName.dll" Plugins
)

3. Debug settings:
   Start external program: path to xrmtoolbox.exe
   Command line arguments:
   /plugin:"NameOfThePlugin" /overridepath:"path to the parent of plugin folder" /connection:"specific connection if you want to use one"
   
   Note: in step 2, we create a folder called Plugins under the debug output folder (eg: {ProjectName}\bin\Debug\Plugins). The path that OverRidePath should point to is the Debug folder. The reason is that XTB will look for a sub-folder called plugins to locate the plugin.

For more information: https://github.com/MscrmTools/XrmToolBox/wiki/Debug-your-plugins-during-development

Xrm.Page is deprecated–what to do in v9+

According to this: https://docs.microsoft.com/en-us/dynamics365/get-started/whats-new/customer-engagement/important-changes-coming, in V9, Xrm.Page is being deprecated. Xrm.Page used to be used in Javascript called by forms as well as the ribbon.

The fix is documented here:

Forms: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/clientapi/clientapi-form-context#using-the-formcontext-object-instead-of-the-xrmpage-object

Ribbons: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/customize-dev/pass-dynamics-365-data-page-parameter-ribbon-actions#form-and-grid-context-in-ribbon-actions

The gist:

Forms:

1. Pass the executionContext from the handler: see https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/clientapi/clientapi-execution-context
2. Add a parameter on your js function to accept the executionContext
3. call getFormContext() on your parameter to get the form-context. See: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/clientapi/clientapi-execution-context

function functionCalledDirectlyByTheForm(executionContext)
{
     var formContext = executionContext.getFormContext(); // get formContext

    // use formContext instead of Xrm.Page 
     var firstName = formContext.getAttribute("firstname").getValue();
     var lastName = formContext.getAttribute("lastname").getValue();
     console.log(firstName + " " + lastName);
}

Ribbon:

1. Pass the PrimaryControl as a CRM Parameter to your javascript function
   see: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/customize-dev/pass-dynamics-365-data-page-parameter-ribbon-actions#form-and-grid-context-in-ribbon-actions
   In Ribbon Workbench:
   
2. Add a parameter on your js function to accept the primaryControl as a parameter
   function OnMyRibbonButtonAction(commandProperties, primaryControl) {
    
   }
3. use the PrimaryControl parameter as though it were the formContext: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/customize-dev/pass-dynamics-365-data-page-parameter-ribbon-actions#form-and-grid-context-in-ribbon-actions

function functionCalledDirectlyByTheRibbon(primaryControl)
{
    // use primaryControl instead of Xrm.Page 
    var firstName = primaryControl.getAttribute("firstname").getValue();
     var lastName = primaryControl.getAttribute("lastname").getValue();
     console.log(firstName + " " + lastName);
}

Gotchas:

Dont use getFormContext in a function called by the ribbon. “getFormContext” is not available in the UCI, and so even though it may work in the legacy web-client, it will break when you begin using the UCI.

If you want a function to work when called from the form as well as the ribbon and you want it to work with the web-client and UCI, do something like this:

//use this to get an object which you can use like the formContext.
//this code can be called from a function that was either invoked from a form or from the ribbon.
function getFormContext(executionContext) {
     var formContext = null;
     if (executionContext !== null) {
         if (typeof executionContext.getAttribute === 'function') {
             formContext = executionContext; //most likely called from the ribbon.
         } else if (typeof executionContext.getFormContext === 'function'
                 && typeof(executionContext.getFormContext()).getAttribute === 'function') {
             formContext = executionContext.getFormContext(); // most likely called from the form via a handler
         } else {
             throw 'formContext was not found'; //you could do formContext = Xrm.Page; if you like.
         }
     }
     return formContext;
}

Others:

Because Xrm.Page is deprecated, you can no longer use Xrm.Page.context. Instead, you should use Xrm.Utility.getGlobalContext(). This method returns an object that supports most of the methods that were available from Xrm.Page.context.

See: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/clientapi/reference/xrm-utility/getglobalcontext

This formContext diagram is extremely useful for determining what is available from the formContext and what is not:

from: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/clientapi/clientapi-form-context

Understand the Client API object model: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/developer/clientapi/understand-clientapi-object-model