Posts Tagged dynamics CRM

Power BI and mixed Team/User Ownership in Dynamics 365

In Dynamics 365 it is easy to assign cases, opportunities, etc. to a Team. This is a very convenient feature and when working with the built-in charts and dashboards, all the charts nicely puts either the team or user name on the legend, depending on what type the owner happens to be.

However, Power BI doesn’t play so nice right off the bat. The reason for that is that the owner field in Dynamics 365 can point to either the User table or the Team table. That means you will need to let Power BI know which table to look at for each record.

As always, there are many ways this challenge can be resolved, but I would like to highlight a few ways that I’ve been working with.

The most important part of this post is simply to recognize that Power BI behaves in this way, and act accordingly. Otherwise, you may end up with weird looking visualizations, or worse, a wrong understanding of what your data really looks like.

I’ll use the Case entity as an example as it is commonly assigned to both users and teams.

Add Type of Ownership and Owner to table

The most simple approach is to add two extra fields on the Case table. One to indicate if ownership type is “User” or “Team”. The other with the name of the user or the team.

The system name for the Case table is of course “incidents”, so that is what will be used throughout these formulas. The examples assume no changes has been made to the column or table names in Power BI from when they were imported from Dynamics 365.

Create a new column and enter this formula or copy and paste from below the image.

Power BI for Dynamics 365 Dynamics CRM and new column to define Owner Type for team and user ownership in Dynamics 365 Dynamics CRM

OwnerType = IF(incidents[_owningteam_value]="","User","Team")

It the field for the owning team is empty, the value will be set to “User”, if it has a value, it will be “Team”. This defines the ownership type.

Create another new column and enter this formula. It is similar to the formula just used, but it inserts the name from the user table or team table instead.

Owner = 
        IF(incidents[_owningteam_value]="",
        LOOKUPVALUE(systemusers[fullname],systemusers[systemuserid],incidents[_owninguser_value]),
        LOOKUPVALUE(teams[name],teams[teamid],incidents[_owningteam_value]))

When entering this formula in Power BI, it would normally be on one line. I added line breaks so it is easier to read and to copy and paste from.

Now, the two new columns can be used in visualizations so you can easily drill down on the specific type of ownership.

Adding two columns in Power BI to better handle mixed ownership between users and teams in Dynamics 365 Dynamics CRM

Click to enlarge animation adding both columns.

Create an Owner Table

Rather than bringing the data in to the Case table, we can create one “Owner” table with all the users and teams in it, along with their unique ID in one column. This table can then be used for all the entities that have both user and team ownership. This method can also be used if you have more attributes on the ownership that you want to categorize by. For example, the manager of the user or the administrator of the team, can all be brought in to this table.

To create this one table with ownership details, I will need to append the User and Team tables.

Click “Edit Queries” to open the “Query Editor”

Edit Queries in Power BI

Click on Append Queries and choose”Append Queries as New”
Append Queries as New in Power BI

The difference between “Append Queries” and “Append Queries as New”, is that “Append Queries as New” will create an additional table with our data that we can work with separately. “Append Queries” will bring data in to an existing table. I am choosing the “as New” option so I still have the user and team table to work with separately if needed.

In the dialog, select the two tables to append and click ok.

Append Queries as New select tables dialog in Power BI - Dynamics 365

Wait for tables to merge and rename the table from “Append1” to “owner”.

Close and Apply the changes.

Close and Apply Changes in Query Editor in Power BI

Find the new Owner table and add two columns indicating the ownership type and the name of the user or team. Similar to the first method described, except simpler since all the values you need are now in the same table.

One new column with the type to indicate if it is either a user or a team

Type = IF(owner[teamid]="","User","Team")

 

One new column with the name of the user or team.

Name = IF(owner[teamname]="",owner[fullname],owner[teamname])

 

One new column which contains the GUID of the user or team.

_ownerid_value = IF(owner[teamid]="",owner[systemuserid],owner[teamid])

I called this column “_ownerid_value”. Not the easiest to type, but it is the same as the schemaname for the mixed user/team owner field. Giving it this name will make it much easier for the autodetect relationships feature to give us some good results.

At the end you should have these three new columns in your owner table.

New columns added in Power BI with Ownership Type, Name of user or team and the GUID.

Finally, go to Relationships and add the relationship between cases and the new owner table matching _ownerid_value to _ownerid_value. Quickest way to do that is to drag _ownerid_value from incidents to the corresponding field on the owner table.

Power BI relationship editor - drag and drop owner id from one table to the other.

Alternatively, click Manage Relationships and run the autodetect feature.

Power BI Manage Relationships and use Autodetect feature.

 

Go back to reports and use the new owner table in some of your visualizations and verify that it is working.

Use new appended owner table with Dynamics 365 Users and Owners in Power BI visulizations.

One consideration for this approach is that you double the amount of data in Power BI consumed by the user and team table. However, these are generally the smallest tables in a Dynamics 365 environments, so the impact is likely limited. I would be cautious of using a similar approach to handle the Customer field that can point to either an Account or Contact.

Other considerations

With the DAX formulas used in Power BI, there are always options to make it more advanced. In my first example, I imported both the user ID and the Team ID into the cases table and based my logic in the DAX formula off of those. I could have imported only the one ownerid and have had the DAX formula base its logic on the content of the user and team tables, rather than the extra columns within the same table. That will save a few columns when importing data and you’ll have fewer columns to navigate.

The DAX formula that brings in the Team or User name, with the logic based on data from other tables would look like this.

Owner = IF(ISTEXT
    (LOOKUPVALUE(teams[name],teams[teamid],incidents[_ownerid_value])),
    LOOKUPVALUE(teams[name],teams[teamid],incidents[_ownerid_value]),
    LOOKUPVALUE(systemusers[fullname],systemusers[systemuserid],incidents[_ownerid_value]))

 

Thanks for reading, and as always, please follow me on Twitter  and sign up for my email newsletter to get the latest tips and tricks to get the most out of your Dynamics 365 data.

, , ,

Leave a comment

Time to expand the scope of the CRM Chart Guy blog

Dynamics 365 has now arrived, and as any other update to the Dynamics CRM/365 product for the past five years, it pretty much contained zero updates to the built-in charting engine. The good news there is that all of my blog posts from the past five years are still valid and I won’t have to update any of them. That, of course, is also the bad news. I believe there are a few areas where Microsoft could have enabled more features on the charts. Features like properly formatting keywords and adding these to annotations. Something the .NET charts are capable of, but for mysterious reasons, are not enabled in Dynamics 365. Enabling this would, for example, allow you to add a nicely formatted total value to a funnel or pie chart.

It is clear, that the direction Microsoft is taking for analytics in Dynamics 365, is to take the data out of Dynamics 365, create visualizations and KPIs elsewhere, and then display the results back inside Dynamics 365. This has been the case with Power BI, and it is also the case with the new Customer Insights and Relationship Analytics features.

The best example of that, which is available now, is the Connected Field Services solution that allows you to embed a record specific Power BI tile inside a Dynamics 365 form, proving a much tighter integration than what we have seen previously.

Power BI tile, record specific in Dynamics 365 form.

Power BI Tile in Dynamics 365

Selector for Power BI tile in Dynamics 365 form

Selection options for Power BI Tile in Dynamics form.

Coming soon is the Relationship Analytics solution, powered by Azure Customer Insights, which also contains visualization and KPIs that are record-specific and is another example of where analytics and charting in Dynamics 365 are headed.

relationship-analytics

Relationship Analytics, powered by Azure Customer Insights

I have recently written a longer piece detailing these different solutions including a comparison matrix. You can find that on the Elev8 Solutions blog here.

While I have very much enjoyed writing a blog focused exclusively on getting the most out of the built-in analytical features in Dynamics, it is time to expand the horizon. Power BI is no longer an external reporting tool and there are many exciting new features coming with Customer Insights. All intended to be an integral part of the Dynamics 365 offering so I will start to include these new tools in my blog posts.

In other words, Dynamics 365, and getting the most out of the data in there, will still be the primary focus for this blog, but the toolset is changing.

This does not mean that I will stop writing about the built-in charts and the XML. This area still has some distinct advantages and I certainly intend to keep my “Ultimate Sales Dashboard” project open as long as people are downloading it.

Hope everyone else is as excited for the new features, and the new scope for my blog as I am.

As always, thanks for reading and please follow me on Twitter  and sign up for my newsletter.

Best,
Ulrik B. Carlsson (CRMChartGuy)

, , ,

Leave a comment

All things Pie Chart in Dynamics CRM

Somehow I have managed not to write about the Pie Chart in Microsoft Dynamics CRM. What you can do with the pie chart is fairly limited since only one series is supported.

However, there are a number of custom properties we can use to tweak the appearance of the pie chart.

Custom properties can be found at the end of the series properties. Note that unlike standard properties, custom properties are all added within the quotations of the CustomProperties.

CustomProperties in Pie Chart XML for Dynamics CRM

The custom properties we can use on a pie chart in Dynamics CRM are:

  • PieDrawingStyle
  • PieStartAngle
  • PieLabelStyle
  • LabelsHorizontalLineSize
  • LabelsRadialLineSize
  • PieLineColor
  • MinimumRelativePieSize
  • CollectedThreshold
  • CollectedThresholdUserPercent
  • CollectedColor
  • CollectedSliceExploded
  • CollectedLabel
  • CollectedLegendText
  • CollectedToolTip

I’ll go through each of these properties and add some notes along the way. There’s an XML sample of a series at the bottom of the post with all the custom properties added.

PieDrawingStyle

  • Default (Default)
  • SoftEdge
  • Concave

Changes the appearance of the pie chart. Yes, the default value is called Default.

Dynamics CRM chart. Chart type Pie. PieDrawingStyle Default

PieDrawingStyle=Default

Dynamics CRM chart. Chart type Pie. PieDrawingStyle SoftEdge

PieDrawingStyle=SoftEdge

Dynamics CRM chart. Chart type Pie. PieDrawingStyle Concave

PieDrawingStyle=Concave

For simply changing the appearance, another option is to change the Area3DStyle to true. Although this is not a custom property.

Dynamics CRM chart. Chart type Pie. 3D enabled

3D Enabled

PieStartAngle

The PieStartAngle is a value between 0 and 360 degrees depending on where you want the first value to start. Microsoft Dynamics CRM pie charts by default start in the 3 o’clock position. That means the 3 o’clock position is 0 degrees and any changes have to take that into account.

Dynamics CRM chart. Chart type Pie. PieStartAngle 270 degrees Custom Property

PieStartAngle=270 start the first slice in the 12 o’clock position

PieLabelStyle

  • Inside (Default)
  • Outside
  • Disabled

PieLabelStyle moves the label inside, outside or disables them completely.

Dynamics CRM chart. Chart type Pie. PieLabelStyle Inside Default

PieLabelStyle=Inside

Dynamics CRM chart. Chart type Pie. PieLabelStyle Outside

PieLabelStyle=Outside

The following four custom properties only apply to pie charts where the PieLabelStyle is set to outside.

LabelsHorizontalLineSize
Specifies the size of the horizontal segment of the callout line, as a percentage of the chart area.
Value range 0-100. Default is 1. Set to 0 to hide it.

LabelRadialLineSize
Specifies the size of the radial segment of the callout line, as a percentage of the chart area.
Value range 0-100. Default is 1. Set to 0 to hide it.

PieLineColor
Any named color, RGB, ARGB or HEX value can be used. See more about colors in MS Dynamics CRM charts here. Default is Black.

MinimumRelativePieSize
Specifies the minimum pie size. Value range 10 – 70. Default is 30. This property has no effect unless the outside label style is used.

Labels and Keywords

When creating a pie chart, the IsValueShownAsLabel=”True” is already added to the chart XML by default. This is what shows the values inside the chart. These values can be formatted by adding the LabelFormat property to the series. Go here for formatting of values in CRM charts.

If you want to customize the content of the label, IsValueShownAsLabel must be set to “False”.

The two properties on a standard pie charts that can hold custom text and keywords are Label and LegendToolTip. Both of these properties must be added in the series. This is included in the sample XML series at the bottom of post.

Here are the keywords that can be used in pie charts. They should be rather self-explanatory. While the last three work, they are not very useful on a pie chart.

  • #LEGENDTEXT
  • #VAL
  • #PERCENT
  • #TOTAL
  • #MIN
  • #MAX
  • #FIRST
  • #LAST
  • #INDEX

Here’s a sample of what the Label and LegendToolTip could look like with keywords added. You can combine both text and keywords as needed.

Label="#LEGENDTEXT #PERCENT  -  #VAL out of #TOTAL total" 
LegendToolTip="#LEGENDTEXT #PERCENT  -  #VAL out of #TOTAL total"

And here is how the pie chart looks with the Label.

Label with Keywords. Dynamics CRM chart. Chart type Pie.

Label=”#LEGENDTEXT #PERCENT – #VAL out of #TOTAL total”

And with the LegendToolTip.

LegendToolTip with Keywords. Dynamics CRM chart. Chart type Pie.

LegendToolTip=”#LEGENDTEXT #PERCENT – #VAL out of #TOTAL total

The LegendToolTip only appears when the mouse hovers over the legend item. Unless changed, the tool tip will show the percentage by default. It’s redundant to show the legend text here, but the option is there.

Label, LegendText, and keywords can be used in the series of other chart types too.

CollectedThreshold

A lot of the pie chart’s custom properties are associated with the CollectedThreshold. The feature combines smaller values into one larger pie slice. This can be useful especially when you have a lot of smaller values  that you can’t tell apart anyway.

When using the CollectedThreshold, you must set IsValueShownAsLabel to False. Otherwise an incorrect value will be shown for the collected slice. Only the value of the first collected item will then be shown.

CollectedThreshold
Any value lower than the CollectedThreshold will be be in the collected slice.

CollectedThresholdUsePercent
If set to true, CollectedThreshold must be between 0-100 and is then defined as a percentage.

CollectedColor
Can be any named color, RGB, ARGB or HEX value. More on colors in CRM charts here.

CollectedSliceExploded
If true, then the collected slice is exploded.

CollectedLabel
Your label for the collected slice. Supports keywords.

CollectedLegendText
The text in the legend. Supports keywords. Customized legend text is only supported for the collected item.

CollectedToolTip
Text in the tool tip for the collected slice. Supports keywords.

Dynamics CRM chart. Chart type Pie. Collected Options used in custom properties

Using collected properties.

Notes on collected

Keyword #MIN is now the collected value, so it cannot be used to show what the minimum value inside the CollectedThreshold is.

Add keyword #TOTAL only to the CollectedLegendText and the total value will appear only once on the chart.

Include a description of how the CollectedThreshold is defined in the either the CollectedLabel or the CollectedLegendText. That way, the user can better decode the information..

Stuff we unfortunately cannot do with Pie Charts in Dynamics CRM

  • LegendText – can’t be changed. Except for collected
  • ToolTip – have no effect
  • LabelToolTip – have no effect
  • Exploded – custom property, have no effect.
  • Keyword values cannot be formatted

Yes, you cannot add formatting to keywords, even though the documentation says you can. You’ll get a chart rendering error in Microsoft Dynamics CRM if you try to add it. This makes keywords pretty useless when dealing with large numbers. I have reported the issue to Microsoft, so if you have a minute, please go vote for the case here, and hopefully we can soon unlock even more chart features.

Note that with IsValueShownAsLabel=”True” label formatting in Dynamics CRM charts can still be used.

The documentation for all the pie chart properties can be found here. http://msdn.microsoft.com/en-us/library/dd456674(v=vs.110).aspx

Sample XML of a Pie Chart series with all custom properties.

<Series	ChartType="pie" ShadowOffset="0" IsValueShownAsLabel="False" 
	Font="{0}, 9.5px" LabelForeColor="59, 59, 59" 
	Label="#LEGENDTEXT #PERCENT  -  #VAL out of #TOTAL total" 
	LegendToolTip="#PERCENT  -  #VAL out of #TOTAL total"
	CustomProperties="PieDrawingStyle=Concave,
			  PieStartAngle=270,
			  PieLabelStyle=Inside, 
			  PieLineColor=Black,
			  LabelsHorizontalLineSize=1,
			  LabelsRadialLineSize=1,
			  MinimumRelativePieSize=10,
			  CollectedThresholdUsePercent=True,
			  CollectedThreshold=10,
			  CollectedColor=Tan,
			  CollectedSliceExploded=True,
			  CollectedLabel=Collected #VAL - #PERCENT,
			  CollectedLegendText=Collected - #VAL out of #TOTAL total,
			  CollectedToolTip=#VAL out of #TOTAL total">
  <SmartLabelStyle Enabled="True" />
</Series>

The sample series can be used to replace the standard series generated by the chart editor.

Thanks for reading. If you have a tip or trick for pie charts that I haven’t found, please include them in the comments. And of course, please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

, , , , ,

19 Comments