Prefilter data for Power BI with OData URLs

When you connect Power BI to Dynamics 365, it pulls all the records from the entities that you have selected. That is generally a good thing, but there are situations where that is very inconvenient.

Maybe you are dealing with large datasets, or you need to filter what records are available for security purposes.

If you have just a few 100k records, Power BI can take over 40 minutes to pull all the records. That is frustrating if you are just going to filter them anyway and only use a small subset of the data.

Or if you are creating a Power BI dashboard intended for sharing, and you want to make sure the dataset does not have records the users are not supposed to have.

You can get around that by amending the OData URL you use in Power BI. Credit goes to my colleague and Dynamics 365 Wonder Woman Michelle Eldridge who told me about this trick recently.

Prefilter records in OData

Here is an example of an OData connection URL for Accounts with parameters added.

odata-url

The $select specifies the fields it will include. The $filter defines the filter that is applied. In this case, only records in the state of California.

Use the URL in Power BI

Click on Get Data and select the OData Feed.
(or New Source if you are already in the Query Editor)
get-data

 

Type in the URL and it will only retrieve the data you specified.
odata-feed-url

There are some details on MSDN on the filters that you can apply in OData URLs.

That part is simple enough, but how did I create that URL?

Use the OData Generator Plugin

If you are not excited about the prospect of creating those URLs manually, then there is an awesome plugin for the XRMToolbox that will do them for you.

It is called the OData Generator Plugin and has been made available by Nizar JLASSI.

You can download the OData Generator Plugin on CodePlex here.

At the time of writing the plugin is not available in the XRMToolBox Plugin Store. You will have to download the .dll file from the link and drop it in the plugins folder where you have your XRMToolBox.

Then open the plugin in the XRMToolBox.
odata-generator-in-xrmtoolbox

 

Click on Load Entities and select the entity you want to work with. In this case, I am choosing the account entity.
load-entities-and-select-account

 

Then click on Load Attributes and Relationships.
odata-generator-selecting-fields-and-filer
In the “Select” column, check all the fields you want to include. In the “Filter” column, check the fields you want to apply a filter to. In the box for the filtering attributes, I added the state field and the equal to “CA” operator and value.

It does take a few seconds for the fields to show up on the right side from when you click on them.

When done, click on Generate OData.
odata-generator-all-details
This will generate the OData URL with all the right parameters. Copy the URL and paste it into the OData Feed in Power BI.

Simple as that.

Tip: If you need to create a URL for a different entity, or need to go back and make changes, then I have had more success with starting over by closing the OData Generator and opening it again, rather than trying to use the clear buttons.

The OData Generator also have some additional features for expanding data from the relationships on the entity.

In Power BI, if you need to make adjustments to the records and the fields the OData URL is retrieving, you can modify the connection URL you created.

Open the Query Editor
pbi-edit-queries

 

Select the query on the left and make sure the applied step “Source” is selected on the right.
review-or-edit-odata-feed-url
In the formula bar, the OData URL is then displayed. From here, you can either add or remove fields from the URL or change the filter. I would recommend only making minor modifications manually unless you are very familiar with the syntax. You can always go back to the XRMToolBox and the OData Generator Plugin and have it create a new URL to replace the one you have here.

You can use the OData URLs in conjunction with the standard OData connection to Dynamics 365 in Power BI. While I have not decided on a preferred method yet, it seems to be the easiest to use the standard OData connection to select most of the entities you need. Then add the entities that specifically require filtering using the approach detailed in this blog post.

Note: If you are using the Data Export Service to replicate your Dynamics 365 data to an Azure database, then you have other means of prefiltering data before it is pulled into Power BI, but that will have to be a different blog post.

That is it for this time. As always please sign up for my newsletter and follow me on Twitter.

Leave a comment

Setup the Data Export Service with a Power BI Solution Template

The Data Export Service is a new and exciting service from Microsoft. It is usually referred to in the context of creating SQL based reports on Dynamics 365 data. However, it is lot more than that and can definitely be leveraged for Power BI as well. I will explore that in this blog post and go through a step by step of the easiest method I have found so far to set it up.

What is the Data Export Service?

The Data Export Service is a cloud-based service that replicates your Dynamics 365 data, to an Azure SQL database and keeps it updated. Here are some benefits for why you might want to do that:

  1. Significantly faster querying times in Power BI
  2. Can aggregate multiple sources into one database
  3. More flexibility with the data and can use SQL queries
  4. Less impact on your production environment as analytical queries are in a separate database
  5. With Power BI Pro, can get closer to real-time analytics with Dynamics 365 data using DirectQuery to the Azure SQL database

Another reason I find the Data Export Service interesting, is that it is also used for the new Azure Customer Insights functions, so when that is finally released, you can use the Data Export Service to see if your data there is up to date too.

I will not get detailed on the setup of the Data Export Service. Scott Durow already posted a great video on how to set it up in detail, and if you are planning on using the Data Export Service in a production environment, you should definitely watch it.

However, the setup does require some skills and you’ll need to fire up PowerShell, install the Azure cmdlets to create an Azure Key Vault, and create an admin user in the Microsoft SQL Server Management Studio. If that is not your thing, and you just want to try it out, then Microsoft has made a Power BI Solution Template available that will let you skip most of those steps.

Setup Data Export Service using Solution Template

My starting point is a trial for Dynamics 365.

I’ll need to add two services to it before I start with the solution template. The Data Export Solution and signing up for a free Azure Trial.

1.  Install Data Export Service

Inside Dynamics 365, go to the AppSource and search for “Data Export Service”.

appsource-data-export-service

Click on “Get it now” and go through all the steps of installing the solution. Basically just click “Next” until it is installed.

Once the installation is complete, navigate to the Data Export Service in Dynamics 365.

data-export-service-in-navigation

There you will need to accept some licensing agreements etc.

2. Sign up for Azure

Open up a new tab in the same browser and go to https://azure.microsoft.com/en-us/free/

azure-free-trial

Click on “Start free” and follow the instructions. You will need to verify your identity with both phone and credit card. No worries, you won’t be charged for a trial unless you explicitly change your subscription. And I am of course not liable for any charges 🙂

When Azure is all set up, just leave it open in its tab. If you accidentally close the tab, you can always get it back on https://portal.azure.com

3.       Power BI Solution Template

Open a new tab again and go to https://powerbi.microsoft.com/en-us/solution-templates/dynamics-crm/

This is the Power BI Solution Template that will setup the Data Export Service and provide a Power BI file with some premade reports.

solution-template-first-page

Click on “Install now”

 

solution-template-now-also-supports-on-premise

Select “Dynamics 365 Online” and click “Next”. Note that you could also use an on-premise installation of Dynamics 365.

 

2 Connect to Org Dynamics 365.png

Click “Connect” and authorize a connection to your Azure subscription. You may be prompted to log in again. If so, use your credentials for the trial environment that you are using.

 

2 Dynamics 365 login choose org and resgroup name CRMchartguy.png

Make sure the right Dynamics 365 organization and Azure subscription is selected. If you have a trial and follow these steps there should only be one option for each. Remember to check the box for authorizing the deployment of the template.

If you click “Advanced” you can optionally name your resource group in Azure. Or if you are ok with a random text string for a name, just leave it and click “Next”.

 

3-connect-to-azure-keyvault

Click “Connect” to connect to and authorize the Azure Key Vault. You may be prompted for login credentials again. This step is really cool as it is the one that lets you avoid firing up PowerShell.

 

3 Connected to Azure Key Vault.png

You are now connected. Click “Next”.

 

4-connect-to-your-sql-database

Next step says “Connect to your SQL Database”, but you have not created one yet. Don’t worry. That is what this step does for us.

Enter any name in “Server Name”. It just has to be unique. Create a SQL Username and password and make sure you remember them.

Click “Check Availability” and if everything looks good click “Next”.

“Advanced” options on this screen will let you set a pricing tier for the Azure database. I will return to what that means later in this post. You can leave the default setting as is as it can always be changed.

Note that you can also change the destination to an existing Azure SQL database if you already have one.

 

5-track-your-progress-2

Now the solution template will do all the heavy lifting and

  • Create an Azure Resource Group
  • Create a SQL server and database with the credentials you gave
  • Create an Azure Key Vault
  • Create an admin user in your database for access
  • Create a replication profile and connect it via the Key Vault
  • Start adding data to your SQL database from Dynamics 365

Pretty nice right?

Once it has gone through all the steps it will start downloading a Power BI template for you.

5 All done waiting for Power BI report.png

When it is ready, download the template.

And that’s it for setting up the Data Export Service with a Power BI Solution Template.

4. Verify the Data Export Profile

Go back to Dynamics 365 and navigate to Settings -> Data Export.

Data Export Profile.png

Click directly on the name of the profile to open it. A double click on the line won’t work here.

This is also the view where you would see your Azure Customer Insights (ACI) connection once that feature is ready.

Data Export Profile Detailed.png

Here you can see the sync status of all the entities included. Initially, you may see some errors due to dependencies on a few entities, but they will resolve themselves as the data gets loaded completely.

Note the buttons on the top of the screenshot for “Manage Entities” and “Manage Relationships”. Use those if you want to include entities that were not already included in the solution template.

5. Azure

Open your tab with Azure and refresh it.

Azure after setup.png

You should see three items. Make a note of the database name. 9bv7irkt3nvg in the example above. No need to do anything with them, but that is where your SQL server, SQL database, and Key Vault is.

6. Power BI

Now that we have an Azure SQL copy of our Dynamics 365 data, we can use that in Power BI.

Open the Power BI templated that you downloaded earlier.

The first report actually has the instruction for getting set up, but they haven’t really worked for me, so here are the steps I use.

pbi-go-to-edit-queries

Go to “Edit Queries”

 

PBI Go to Data Source Settings.png

Data Source Settings

 

PBI Change Source.png

Change Source

 

PBI SQL Server Source.png

In the Server field, replace “servername” with the name you gave the server during the setup. Make sure the .database.windows.net is included at the end.

In the Database field, add the name of the database that was generated. It is something weird like 9bv7irkt3nvg. If you forgot, go back to Azure and copy it from there.

When done click “OK”.

 

PBI Edit Permissions.png

Then go to “Edit Permissions”

 

pbi-edit-permission-edit

Click “Edit”

 

pbi-database-login

Make sure the tab is “Database” on the left-hand side. Enter the database login details you created earlier and hit “Save”. Then click “OK” and “Close” until you are back to Query Editor.

 

pbi-refresh-preview

Click on “Refresh Preview” to ensure it is working. You should now see the preview data populate.

 

pbi-close-and-apply

Click on “Close and Apply” and the data will load into Power BI from your Azure SQL Database rather than from Dynamics 365.

Scaling your Azure database

In the beginning of the blog post, I mentioned a number of reasons why you might want to take this approach. One of them was significantly faster querying times.

Well, how much faster? That depends partly on how much money you decide to throw at your SQL database.

In Azure, you can see the pricing tier for your database. It is measured in Database Transaction Units (DTUs). You can read a lot more about DTUs here.

The short story is that the more expensive it is, the more performance you are getting.

Go to your tab on the Azure portal and click on the SQL database. The one with the randomly generated name.

Azure Pricing Tiers.png

Under “Settings”, click on the “Pricing tier (scale DTUs).”

By default, it is setting you up with the version that costs about 75.02 USD a month. Don’t worry, it’s a part of the 200 USD you got for free with the trial.

You can select any of the other tiers and click “Select” and the database will immediately scale to the new settings. It only takes a few minutes but varies depending on the amount of data.

So how much of a difference does it make?

Well, here are some tests refreshing from a Dynamics 365 organization with about 262K records.

time-to-refresh-in-seconds

All times are of course approximate and can vary depending on the amount of fields per record etc. The initial refresh might also take a little longer than the following refreshes.

The standard OData connection directly to Dynamics 365 took about 2700 seconds to refresh. That is 45 minutes!

From the Azure SQL database and at the cheapest rate of 4.99 USD a month at 5 DTU it took about 120 seconds. Or around 2 minutes.

For 15.00 USD a month and 10 DTU it is down to 45 seconds. From then on you don’t win that much for the extra cost, unless you have other reasons to upgrade your database. The 100 DTU version is 150 USD per month.

As you can see, there are some significant differences in the time it takes it takes to query the data. Even at the cheapest version, there’s over a twentyfold increase in speed. In fact, it’s kind of mindblowing. So skip one almond milk latte per month and get yourself an Azure SQL database.

Keep in mind that you can scale the database anytime you like. So if you plan to work in Power BI for a day and test out a lot of queries, you can increase the pricing tier for a couple of hours. Just remember to scale it down afterwards.

Glad you made it to the end. Hope you found something useful. As always please sign up for my newsletter and follow me on Twitter.

6 Comments

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

Crowdsourcing the ultimate Sales Dashboard for Dynamics 365/CRM

When setting up a new Dynamics 365/CRM trial a few standard dashboards are included. However, I feel they lack something, and those who follow me on Twitter know I occasionally like to make fun of particularly the funnel chart. So here’s a hopefully improved version, that you can use, either in your organization or simply to spice up a demo environment.

Well, why not do something about it then. Plus, I’ve toying with this idea for a while anyway.

Crowdsourcing?

I did have the audacity to use the term “ultimate” in the title and the dashboard I provided is clearly not. However, with lots of input, we can make this much better.

Therefore; I would love to hear from you if you have suggestions how to make these dashboards better.

Suggestions could include, but are certainly not limited to:

  • Ideas for new KPIs
  • Ideas for new Charts
  • Ideas to improve formatting and make the charts easier to decode
  • Using charts contextually on record forms
  • Better use of colors between the different charts

My only request is that you elaborate on your suggestion, and I will try to incorporate as much as possible and hopefully have this grow into something very useful.

At the bottom of this post, I’ve included a zip file containing a solution with all the components for the dashboards in this post. The zip file contains both a managed and unmanaged solution that can be imported to Dynamics 365/CRM, and all the chart xml files individually.

Assumptions

It should be possible to import the charts on Dynamics 365/CRM organizations, so I will try to adhere to the following rules and assumptions:

  • Only use the charts within Dynamics 365/CRM*
  • Only use system fields
  • Only use system relationships
  • Avoid using calculated fields for KPIs – unless it is a system field
  • Take as much advantage of the existing setup as possible

*Why am I limiting myself to only using the charts within Dynamics 365/CRM and not including Power BI and Customer Insights. While Power BI and Customer Insights are powerful features, they are both tools external to Dynamics 365/CRM. You cannot as easily, share the charts and KPIs within CRM itself. Charts also have some significant advantages too. They can be contextual to the user logged in, respects the security roles, and they are real-time. “I just won an opportunity. I’ll happily wait an hour and then look at the sales leaderboard to see my position.” said no sales person ever!

 

The Sales Dashboard

Main sales dashboard with the most important KPIs for Dynamics 365/CRM

The Sales Dashboard consists of the following charts:

1. Sales Funnel

Somehow it is not a sales dashboard if there isn’t a funnel on it. It is the universal indicator of a sales process regardless of how wrong it may be. I have done my best to overcome some of those areas.

2. Sales Leaderboard

The Sales Leaderboard is also a standard component on a sales dashboard. This one includes both won and in progress revenue. Users are ranked per the amount won. Current user is emphasized in a stronger color so it is easier to see your own position.

3. Sales KPIs about the open opportunities

KPIs include est. revenue, average value, number of opportunties in the pipeline, next est. close date and date the last opportunity was created. On the dashboard, the user can flip views between “Open Opportunities” vs. “My Open Opportunities” to get both group and personal KPIs.

4. Phase Loss Rate on Est. Revenue

This chart gives you the percentage of revenue lost per phase in the sales pipeline. For example, in the qualify stage, if opportunities est. 100 million entered the qualify stage, but only 45 million of est. revenue made it to the develop phase, then the loss rate for qualify is 65%. The same calculation for the develop phase, but of course excluding all the opportunities that never made it that far. In an ideal world, you would lose more opportunities in the initial stages and have a much higher win percentage towards the end.

Additional charts with variations on how the calculations are made are included in the zip file, including using the number of opportunities rather than the est. revenue.

5. Sales KPIs for Won Opportunities

Like the other KPI chart, but this one focusing on KPIs for the opportunities that were won.

Competitor Strength

Scroll down on the dashboard and you get to the Competitor Strength chart.

Competitor Strength for Open Opportunities in Dynamics 365/CRM

The competitor strength chart lists all your competitors, ranked by how many opportunities you have lost to them.

On top of the lost bar, you can see how many opportunities are currently in progress.

Finally, in another bar, you have the est. revenue of the open opportunities where this competitor is a threat.

Sales Pipeline Analysis

Sales Pipeline Analysis in six charts in Dynamics 365/CRM

As mentioned earlier, I have included all the pipeline analysis charts in one dashboard for review. They all work in the same manner, although the calculations have a different focus as described by their names.

Download

As always, samples are provided for fun and training purposes only. Test properly prior to using in a production environment.

Sales Dashboard by CRM Chart Guy Version 0.0.0.1

Update Notes November 20, 2016.

Pipeline Charts now only uses the beginning number of the step name, so as long as your phases are 1 through 4, then you would not need to make changes. Won opportunities will also be filtered correctly and do not necessarily need to be moved to the close stage. Regardless of the stage on a won opportunity, it is assumed that it has made it through all the stages.

Added some of the pipeline charts to a separate User form to analyze an individual’s performance against how the company KPIs.

Zip file includes all the chart xml files individually and a managed and unmanaged version of the solution file for Dynamics 365.

Solution files for pre-Dynamics 365 environments are no longer included. However, all the chart xml files can be imported separately into previous versions. Presumably, all the way back to CRM2011 although I have not been able to test that.

Added a chart for the Opportunity Products to see what products are in the pipeline. Products less than 10% of the total are grouped into “Other”.

Original Solutions Notes

All the charts that use stages or the pipeline rely on the Opportunity field called Pipeline Phase. Most trial instances have data in this already. Otherwise, it is populated either manually or by a workflow. The chart also assumes that the naming of the phases is kept from the trial so they are called 1-Qualify, 2-Develop etc.

The assumption I made in the calculations is that won opportunities are in the 4-Close phase. Therefore, make sure that the process that updates the pipeline phase puts won opportunities 4-Close. They really should be in that stage anyway if won.

The solution files with Dyn365 in the name are specifically for new trials or environments that have already been upgraded. This solution also includes the Competitor Chart.

Solution files with CRM_8.0 in the name are for CRM2016 and earlier. This version does not include the competitor strength chart as there’s a bug in CRM that does not allow the distinct=true property to be imported in a solution file. This was fixed with Dynamics 365.

All the chart xml files are in the zip file as well, so you can import them separately, make modifications etc.

Thanks for reading. Hope you enjoy and please share and let me know of any good suggestions.

And don’t forget to sign up for my newsletter on this page and follow me on Twitter

, , , , , , , ,

14 Comments

Custom Date Formats in Charts in MS Dynamics CRM

I get a lot of questions on how to change the date format on the labels. A common one is changing the week grouping from “Week 35 of 2016” to “Week of Aug 29.”

Coming from Europe, I think week numbers are an awesome way of keeping track of the weeks, but like the metric system it just never caught on here in the US, regardless of how much sense it makes.

So even though week numbers are the better option, like column/bar charts over funnel charts, let’s have a look at how we can work with the date formatting on labels.

First up, MS Dynamics CRM does not support changing the date labels on an axis. If you change the xml for the labels, the chart will either just show you the standard date formatting, or the chart will show a render error, even though the xml is correct. For this reason, I’ve previously stated that this requirement cannot be met with charts in Dynamics CRM.

However, unlike the axis, you can actually format the dates on the label for the series. So to address the question on changing the week numbers on the axis label, I will try and remove those labels and use a series label instead to show the date in the desired format.

So the end result will look like this:

MS Dynamics CRM Chart with modified xml to display custom date formatting

Rather than this standard labeling:

The system week grouping in MS Dynamics CRM chart created in the chart editor.

It is not perfect, so if you can live with the drawbacks I mention throughout this post, it will probably work for you.

Build the Base Chart

Let’s create the foundation. In the chart editor, we will need two series, and of course, the week we are grouping by.

Chart Editor in MS Dynamics CRM. Two series and group week to prepare for adding custom date formatting in the chart xml.

We need two series. One to show the actual count of opportunities, and one to hold the label for the custom date formatting.

Export the Chart XML

Open the chart xml in Notepad ++ and get ready to make some changes.

In the CRM chart xml below, I have updated all the auto generated aliases to something more meaningful in this context.

  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <attribute groupby="true" alias="groupbyweek" name="estimatedclosedate" dategrouping="week" />
            <attribute alias="dateformat" name="estimatedclosedate" aggregate="min" />
            <attribute alias="countofopportunities" name="estimatedclosedate" aggregate="count" />
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category alias="groupbyweek">
          <measurecollection>
            <measure alias="dateformat" />
          </measurecollection>
          <measurecollection>
            <measure alias="countofopportunities" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>

In the xml, my changes and additions are in green.

Besides updating the aliases, I made one significant change in this section. In the attribute for the “dateformat,” I changed the aggregate to “min” for minimum.

Also note that I have the “dateformat” before the “countofopportunities” in the category collection. This way the count, which is the number we want, is drawn last and on top.

Before you import, scroll down on your chart xml and make this VERY IMPORTANT change.

  <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorGrid LineColor="239, 242, 246" />
	<MajorTickMark LineColor="165, 172, 181" />
	<LabelStyle Font="{0}, 10.5px" Format="CRM Chart Guy" ForeColor="59, 59, 59" />
  </AxisY>

In LabelStyle for the first Y axis, add Format=”CRM Chart Guy” as above.

If you do not add this format you will get a rendering error. This seems to be a bug in how Dynamics CRM renders charts, but this is the way to get around it.

Now you can import the chart and make sure everything is still working.

Chart xml modified to display dates instead of values in MS Dynamics CRM

So far, so good. You should now be able to see that one of the bars contains the date, while the other still has the count.

As you have probably figured out by now, the date that we are going to get may not be the Monday of that given week. It will be the earliest date of that week, where data was present. So use with care as it could be misleading if your first date happens to be the Friday of a given week. However, for daily events, it should be fairly accurate, or at least close enough.

Edit the Series

Go back to the chart xml and locate the two series. We need to make the following updates:

  • Remove the legend items – not needed in this case
  • Draw the bars on top of each other instead of side by side
  • Make the date series bar invisible/transparent – we only want the label
  • Give the bar displaying the count a brighter color so it is easier to read text on it
  • Change the date label format
  • Move the date label so it is left aligned on the chart
  <Series>
	<Series ChartType="Bar" IsValueShownAsLabel="True" Color="Transparent" IsVisibleInLegend="False" LabelFormat="'Week of' MMM dd" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="BarLabelStyle=Left, DrawSideBySide=False, PointWidth=0.75, MaxPixelPointWidth=40">
	  <SmartLabelStyle Enabled="True" />
	</Series>
	<Series ChartType="Bar" IsValueShownAsLabel="True" Color="LightBlue" IsVisibleInLegend="False" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" YAxisType="Secondary">
	  <SmartLabelStyle Enabled="True" />
	</Series>
  </Series>

Here are the additions to the chart xml in the same order as above:

  • IsVisibleInLegend=”False” to both series
  • DrawSideBySide=False to one of the series (this custom property will automatically apply to all the bar series)
  • Color=”Transparent” to the dateformat series
  • Color=”LightBlue” to the countofopportunities series (more on colors in CRM Charts here)
  • LabelFormat=”‘Week of’ MMM dd” on the dateformat series
  • BarLabelStyle=Left to the dateformat series custom properties

Import the xml back in to MS Dynamics CRM

XML for the chart modified custom date format and other cosmetic changes.

Custom Date Format

The LabelFormat=”‘Week of’ MMM dd” is what gave the label the “Week of Aug 29” label, or whichever date was the lowest for that group.

Here are the most common options you can use:

  • “d” = The day of the month, from 1 through 31
  • “dd” = The day of the month, from 01 through 31
  • “ddd” = Abbreviated name of the day; Mon, Tue, Wed etc.
  • “dddd” = Full name of the day
  • “M” = The month, from 1 through 12
  • “MM” = The month, from 01 through 12
  • “MMM” = Abbreviated name of month, i.e. Jan, Feb, Mar etc.
  • “MMMM” = Full name of month
  • “yy” = The year, from 00 to 99
  • “yyyy” = The year in four digits

Pay attention to the case of the letters. “m” is for minutes, while “M” is for months.

The full set of options can be found on MSDN here.

As in this post, you can combine the options like “dd MMM yy” for “31 Aug 16”.

Also, when adding leading text like ‘Week of’, put it in single quotes to avoid some letters being displayed as a value instead.

Chart Area and Axes Modifications

Ok, let’s fix those axes and the labels that just say CRM Chart Guy.

This is actually really simple. I just deactivated them by adding  Enabled=”False” to both AxisY and AxisX.

<ChartArea BorderColor="165, 172, 181" BorderDashStyle="Solid" BackColor="10, 59, 59, 59">
  <AxisY  Enabled="False" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorGrid LineColor="239, 242, 246" />
	<MajorTickMark LineColor="165, 172, 181" />
	<LabelStyle Font="{0}, 10.5px" Format="CRM Chart Guy" ForeColor="59, 59, 59" />
  </AxisY>
  <AxisX  Enabled="False" LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorTickMark LineColor="165, 172, 181" />
	<MajorGrid LineColor="Transparent" />
	<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
  </AxisX>
  <AxisY2 LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">
	<MajorGrid LineColor="239, 242, 246" />
	<MajorTickMark LineColor="165, 172, 181" />
	<LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
  </AxisY2>
</ChartArea>

Fun fact: I think this is my first post where I left the secondary Y axis alone 🙂

Now, I did a little more than that. Right before the AxisY, I made a few modifications to the ChartArea.

  • Changed BorderColor from “White” to “165,172, 181” (I just copied the gray color from the major tick mark)
  • Added BackColor=”10, 59, 59, 59″ to give the background a little more substance. (Copied the label ForeColor and added a lot of transparency)

Import and your result should look a little like this.

Axes and other items on the chart XML has been cleaned up for the custom date chart in MS Dynamics CRM.

Notice there may be some conflicts between the date label and the actual value, when the value is low. That can be alleviated by either simply removing the value, it will still appear in the hover text, or giving the chart some more horizontal space if it is on dashboard.

The context where I usually use custom date formatting is on Key Figures Charts.

Key Figures Chart in MS Dynamics CRM displaying to the point relevant data where it is needed.

Using the custom date formatting, I can write the dates in whatever format desired and can even leave out irrelevant pieces.

Add Some Corporate Branding

Finally, add this section right before the </Chart> closing tag.

<Annotations>
	<TextAnnotation X="50" Y="93" Text="Elev8 Solutions" TextStyle="Default" Font="Verdana, 30px" ForeColor="LightGray" ToolTip="Elev8 Solutions - Microsoft Dynamics CRM Partner - www.elev8solutions.com"/>
</Annotations>	  	  

Chart with an annotation added in the chart xml for custom corporate branding.

Looks great! You can read more about Elev8 Solutions and our innovative immersive approach to CRM projects here. Which also happens to be where I work.

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

, , ,

3 Comments

Charts on Forms, or User/Account Specific Dashboards

Charts don’t just go on dashboards, they can go on the forms too.

It’s really just a sub-grid that displays a chart instead, but as this seems to be a heavily underutilized feature in MS Dynamics CRM, maybe I can bring some more focus on it with this post.

The primary example where form charts can come in handy is for the “Sales Activity Dashboard” that each sales person can look at and see their current statistics, opportunities, tasks etc.

Sales Activity Dashboard for User in MS Dynamics CRM

A common question from a sales manager is “That’s great, but I want to see all that same data, but be able to flip through the members of my sales team and see the data that they see”.

Simple enough as all the views can be filtered to a named user rather than “Equals Current User” which is used throughout the example above. Obviously very cumbersome since you would need a saved view for each sales person and you would have to change the views on each chart individually, or create a dashboard for each person on your sales team, etc.

MS Dynamics CRM dashboards do not have good ways of filtering an entire dashboard to a specific user. CRM2016 is getting a little closer, but the filter can only be applied to option sets and only in the new engagement hub.

However, the combination of charts you are looking at does not necessarily need to be on a dashboard.

The example above is the “Sales Activity Dashboard” that comes with Dynamics CRM. There are five charts on it. In this blog, I will go through the steps needed to add all these chart to the user form so the same charts are displayed, but specific to the user being viewed. Then a sales manager can flip through the users, and see the data they see on their “Sales Activity Dashboard.”

Like this:

Sales Activity Dashboard on User form, specific to the USer in MS Dynamics CRM

Same charts as the dashboard, but all user specific and you can circle through the list of users with the arrows in the upper right hand corner.

These record specific charts are a very powerful way of bringing insights onto a specific record. In this example it is a sales person, but we could also be looking at service and case KPIs for a service user. From an account record you can add charts with sales statistics, activity statistics, case and service KPIs, all specific to the account. It would give the account manager a very clear view on the current state of the account.

Putting chart on MS Dynamics CRM forms

Go to your solution editor and open the User form and click “Save As.”

Create New User form for Sales Dashboard in MS Dynamics CRM

Name the new form “Sales Dashboard.”

 

Open it and insert a new three-column tab.

Insert three-columns Tab on user form in MS Dynamics CRM

I chose three columns evenly distributed, because that is the same way the dashboard was. Open the properties on the new tab and rename it to “Sales Dashboard.”

Now it is time to insert some charts.

Select the first section in the new tab and insert a Sub-Grid.

Insert SubGrid in section on user form

In the Name and Label fields, just write the name of the chart. Make sure the Display Label on the Form box is unchecked. Neither of these values will be shown on the form, or need to be as they are already included in the chart.

Subgrid and chart settings to display chart on form in MS Dynamics CRM

In the Data Source section select

  • Records = Only Related Records
  • Entity = Opportunities (Owning User)
  • Default View = Open Opportunities

This is what pulls the right opportunities to build the chart on.

In the entity setting, make sure you select the right relationship to the opportunity, which is the owning user in this case.

For default view, use a similar, but not the same view as the dashboard. In this case, the dashboard uses the view “My Open Opportunities”. To get the expected result on the user form, use the view “Open Opportunities”. The “Only Related Records” is already filtering to only include opportunities for this specific user.

Scroll down and find the chart settings.

Subgrid chart settings on form

Select the same chart as the dashboard for this section, in this case “Sales Pipeline” and check “Show Chart Only.”

Leave the view selector off.

Note: You can choose to include the view selector and check the “Display Chart Selection”. This will enable you to switch views and chart in the same section on the dashboard. This can be very powerful as well, but in this case I am trying to replicate the standard dashboard as much as possible, and those features were not enabled there. I would highly recommend playing with those features as they can significantly improve your charts and dashboards.

Scroll back up on the properties and click the “Formatting” tab.

Formatting Tab 30 rows on subgrid settings

Leave the layout option at “One Column”

In the row layout, add the number of rows you want the chart to take up. This is a place where you need to be generous. 30 rows for the funnel chart will make it about the same size as the funnel chart on the main sales dashboard which we are replicating.

Repeat these steps for the other four charts that are on the sales dashboard.

Save the form and publish it.

Save and Publish form

Go to a sales person’s user record and  switch the form to “Sales Dashboard.”

Switch form on user to display sales dashboard

Enjoy the same sales dashboard, but specific to the user you are looking at.

Full Sales Activity Dashboard on User form in MS Dynamics CRM

Wow, a post with no XML editing!!!

Thanks for reading. Please sign up for my newsletter and follow me on Twitter

,

13 Comments