Ulrik (CRM Chart Guy)
interested in alll things visualizing data, particularly with charts in Dynamics CRM
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.
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)
Type in the URL and it will only retrieve the data you specified.
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.
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.
Click on Load Entities and select the entity you want to work with. In this case, I am choosing the account entity.
Then click on Load Attributes and Relationships.
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.
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
Select the query on the left and make sure the applied step “Source” is selected on the right.
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. Follow @CRMChartGuy
Posted in Uncategorized on January 11, 2017
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:
- Significantly faster querying times in Power BI
- Can aggregate multiple sources into one database
- More flexibility with the data and can use SQL queries
- Less impact on your production environment as analytical queries are in a separate database
- 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”.
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.
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/
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.
Click on “Install now”
Select “Dynamics 365 Online” and click “Next”. Note that you could also use an on-premise installation of Dynamics 365.
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.
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”.
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.
You are now connected. Click “Next”.
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.
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.
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.
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.
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.
Open your tab with Azure and refresh it.
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.
Go to “Edit Queries”
Data Source Settings
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”.
Then go to “Edit Permissions”
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.
Click on “Refresh Preview” to ensure it is working. You should now see the preview data populate.
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.
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.
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. Follow @CRMChartGuy
Posted in Power BI on December 18, 2016
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.
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.
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.
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”
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.
Wait for tables to merge and rename the table from “Append1” to “owner”.
Close and Apply the changes.
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
One new column with the name of the user or team.
One new column which contains the GUID of the user or team.
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.
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.
Alternatively, click Manage Relationships and run the autodetect feature.
Go back to reports and use the new owner table in some of your visualizations and verify that it is working.
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.
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.
Thanks for reading, and as always, please follow me on Twitter Follow @CRMChartGuy and sign up for my email newsletter to get the latest tips and tricks to get the most out of your Dynamics 365 data.
Posted in Commentary on December 18, 2016
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.
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.
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 Follow @CRMChartGuy and sign up for my newsletter.
Ulrik B. Carlsson (CRMChartGuy)
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.
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.
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
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.
Scroll down on the dashboard and you get to the Competitor Strength chart.
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
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.
As always, samples are provided for fun and training purposes only. Test properly prior to using in a production environment.
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 Follow @CRMChartGuy
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:
Rather than this standard labeling:
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.
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.
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.
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.
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
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
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.
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.
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.
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.
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 Follow @CRMChartGuy
Posted in Charts on Forms on January 24, 2016
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.
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.”
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.”
Name the new form “Sales Dashboard.”
Open it and insert a new three-column tab.
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.
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.
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.
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.
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.
Go to a sales person’s user record and switch the form to “Sales Dashboard.”
Enjoy the same sales dashboard, but specific to the user you are looking at.
Wow, a post with no XML editing!!!
Thanks for reading. Please sign up for my newsletter and follow me on Twitter Follow @crmchartguy