Posts Tagged CRM Chart

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

Display only complete weeks or months in charts

Here’s an issue that can be rather annoying. You want to display activities by week. You set up your chart and have a view that includes data from the last 5 weeks.

MS CRM chart with incomplete week highlighted

Incomplete week highlighted in chart

The only problem is, that a view showing the last 5 weeks doesn’t show the last 5 weeks. It is showing you the last 5 x 7 days, or 35 days. If those 35 days end on a Thursday, your chart, showing you information by week, will only have data from two days of that week. In other words, the chart will (inaccurately) present you as a slacker that week. We all know that is not the case, so we will need to make sure that those charts have an accurate representation of weekly data.

To correct this, I will create a view with 6 weeks of data, but limit the chart to show only the last 5 weeks. That will ensure that all columns in the chart represent a complete week’s data.

Note: This approach also works for months. However, if your fiscal periods are aligned with months, a View with the last 5 fiscal periods, will actually be the last 5 full months. A view with the last 5 months, will be the last 5 x 30 days = 150 days.

First, I’ll create the weekly chart in the editor and then export the chart xml.

I’ll start by adding an order clause, and reverse the standard order. The descending=”true”, means the most recent week will now be the first item, rather than the last.

Add order clause to MS CRM chart xml. order alias="start" descending="true"

To remove the sixth straggler week, I’ll add Maximum=”5.75” to the Axis X. This will ensure that I am only displaying the first 5 items and not the 6th. This is why I had to change the order of the weeks.

Maximum added to X axis in the MS CRM Chart xml

Why set the maximum at 5.75 and not just 5. If set to 5, the maximum property would cut the chart exactly at 5, which would be the middle of the 5th column at the interval mark, highlighted below. If maximum was 5, only half a column would be displayed.

MS CRM Chart activities by week, displaying only complete weeks by modifying the chart xml.

Now we have a chart where all the weeks have a complete set of data. The exception of course, is the current week, which depending on the day might not be complete. That however, makes much more sense to the user.

If you want the oldest week to the left, add IsReversed=”True” to the X axis properties.

X Axis is reverased by adding IsReversed="true" to the xml for the MS Dynamics CRM chart.

Thank you for reading. Please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

, , , , ,

4 Comments

Is your “Regarding” field on activities set correctly?

MS Dynamics CRM gives you a plethora of options for setting your activities regarding a specific record. Most companies have guidelines on which entity certain activities should be set to. For example, an often recommended approach is to set activities regarding the opportunity it is about. However, if a sales person simply clicks on the track button in Outlook, rather than “Set Regarding,” and go through the process of selecting the right record, the email will be associated with the contact, not the opportunity. In the defense of sales people, clicking the track button does seem like the obvious thing to do.

In order to follow up on whether or not activities are set regarding to the correct entity, we can create a chart that show the count of activities per type, and which entity they have associated them with.

MS Dynamics CRM chart - Activities by regarding entity.

The chart tells us which type of entities the users are focusing on. There should be a healthy balance between activities on leads and opportunities depending on how you are using MS CRM.

Create an “Activities View” with the needed entities

First, open up Advanced Find and select the Activities entity and open Edit Columns.

Open the Record Type option set and scroll down to the section that begins with Regarding.

Add Columns with Regarding entity to MS Dynamics CRM for chart customization.

This is a list of all the entities that activities can refer to.

Add a column for each of the entities that you would like to include in your chart.

After you select a regarding entity, you have to select a field. Choose a field that you know always is populated. I like to use the field “Created On”.

View with Regarding: Created on field for each if the chosen entities in MS Dynamics CRM. CRM Chart.

To this view I have added the “Created On” field for Leads, Accounts, Contacts and Opportunities. Each column will be populated with the “Created On” date. The view in itself, is not very helpful, but the important part is that there is only data in the Account column, if the Activity is related to an Account. I cannot see which is which, but that is OK for now.

View with data Regarding Created On in MS Dynamics CRM.

Save your view and navigate back to the activities section in MS Dynamics CRM and open your new view.

Use chart editor to create the base

Open the chart editor and start adding the “Regarding” fields to your legend entries. If we had not added the fields to the view first, they would not have been accessible in the chart editor.

Chart editor with Regarding fields added to prepare for xml chart customizations in MS Dynamics CRM. CRM Charts.

Make sure that the aggregate is set to Count:Non-empty, so we only count the records that have a value in that field. This means the chart only counts for the field when the activity is regarding an entity of that type.

Set the category to either Activity Type or Owner depending on your needs.

Chart from editor before xml edits. Activity by entity and type in MS Dynamics CRM. CRM chart customizations.

Now we have a chart that shows us the amount of activities per type and the regarding entity.

Clean up chart xml

Unfortunately, not even the person who made it can decipher which series belongs to which entity, so we’ll export the CRM chart xml and do some clean up.

First, we now have the almost mandatory step of removing the secondary Y axis.

Find this sucker Y Axis Type Secondary MS Dynamics CRM chart customizations.in one or more of the series and delete it.

For good measure, although not required, remove the AxisY2 section.

AxisY2 section to be removed from MS Dynamics CRM chart. Customization in the chart XML.

Luckily we do not have to remember the order of how we added the different entities. That information is present in the <fetchcollection>.

fetchcollection for MS Dynamics CRM chart. Adjusted alias for each entity for CRM chart customization.

I’ve renamed the aliases in the example above so it is easier to read. If you do that, remember to update them in the <measurecollection> as well.

measurecollection for MS CRM chart xml. Alias renamed according to entity names.

Finally, in the series, add the LegendText so it will make sense to the user. The series follow the same order as the <measurecollection>.

Series with LegendText. Added to MS CRM Chart xml.

Import the chart back in to MS Dynamics CRM.

Final Chart after chart xml modifications. MS Dynamics CRM activities per entity and per type chart.

 

While the example used Activity Type as the category, this can easily be replaced with Owner, to see which entities users focus their activities on. It is also a very good indicator if users set the Regarding field according to the company guidelines.

Thank you for reading. Please sign up for the newsletter to be up to date on new blog posts and follow me on Twitter 

, , , ,

3 Comments

Create a Gantt Chart in MS CRM

Until recently I thought that Gantt charts couldn’t be done in MS Dynamics CRM without turning to some serious development, which I found unfortunate because this is a popular type of chart and users find them easy to read. Turns out you can, and it’s not that difficult to create a simple Gantt Chart in MS CRM.

EDIT regarding CRM2011: This post relies on changing the aggregate of a date field to “min”, which seems to be exclusive for CRM2013. CRM2011 will give you an import error when you make that change to the xml. However, a reader has been kind enough to send me the details of how make it work for CRM2011. In the fetchxml you will need to remove the aggregate=”min” from each of the attributes, and also remove the groupby=”true” along with its corresponding alias in the categorycollection. Thank you Nils for sharing.

In this post, I’ll create a simple Gantt Chart for the opportunity entity, that shows when the opportunity was created and when the Est. Close Date is. It is a good visual indicator for how long the opportunity is planned to be open, rather than looking at the dates. After that, I’ll share some ideas for more advanced versions. Everything of course, is made with only xml modifications for standard MS CRM charts.

Here’s the simple version which I’ll create step by step in this blog post, showing Created On and Est. Close Date.

Gantt Chart in MS Dynamics CRM

Chart Editor – Create the base for the Gantt Chart

Since there is no Gantt Chart type in MS CRM, we have to use the standard bar chart instead and make some modifications to the xml so it looks like a Gantt Chart. What I am really doing is creating two bar charts, one bar to reach Est. Close Date, and then one bar to “cover” a part of the bar up till the Created On date. This post uses a custom Created On date field to use in place of the system Created On field, just so it’s easier to create demo data.

As always, I’ll use the chart editor to do most of the work.

Chart Editor set up for creating Gantt chart in MS Dynamics CRM

Note that the order of dates here are important. The first date on the chart, in this case Created On, should be on the bottom. The chart type is Bar, not StackedBar.

Let’s have a look at the result.

Chart based on setup in chart editor - step 1 for creating MS CRM Gantt chart

Basically this looks nothing like a Gantt chart. The chart is just counting the number of dates. That is because count aggregates are the only available options in the chart editor.

Chart XML edits

Let’s export the xml and make some edits.

  1. Change the aggregates to “min” (this is the secret sauce)
    aggregate min
  2. Add the custom property DrawSideBySide and set it to False
    DrawSideBySide false
  3. Remove the secondary Y axis
    3 Secondary Y axis delete Count Distinct

Let’s import it back into CRM and have a look.

Chart after first modifications - Gantt chart in MS CRM

Much better. We still need to do some xml modifications, but let’s take a look at what happened.

Changing the aggregates to “min” changed the Y axis to a date format, and the values relative positioning are correct in comparison to the dates. Very unlike a date grouping which only includes the values present in the data set on the axis. You get the same result using the “max” aggregate, so you can pick either.

The DrawSideBySide=False ensures that the two bars are drawn on top of each other, rather than next to each other. Looking at the image above, the blue bar, Est. Close Date goes all the way back to the X Axis, but a part of it is covered by the orange “Created On” bar. If a DrawSideBySide property is added to one series, it is automatically applied to all series of the same type, so we only need to add it on one of them.

Finally, we need to remove the secondary Y axis, because it’s not needed in this scenario (as usual).

Let’s make the next set of modifications to the chart xml. Change the color for the Created On series to white, by adding Color=”White” to the series.

Color White

White is also the background color and border color. This will make it appear as if there’s no second bar on the chart, and we only see that start and Est. Close Date for the opportunity. At the same time I’d recommend removing the series from the legend and/or modify the legend text to something more suitable.

Gantt chart in MS CRM 2013

Final Gantt chart in MS CRM 2013 showing Created On dates and Est. Close Dates for open opportunities.

Notes

If you need to force a specific start date on the Y axis, then this has be inserted as a number. The chart is using the same date number system as Excel where Jan 1 2014 is 41,640. So if we wanted the Y axis to begin on January 1 2014, we would insert Minimum=”41640″ in the AxisY properties. These numbers are easy to get by entering the date in Excel and then formatting the cell as a number.

The chart type RangeBar, originally seemed like a more suitable option to create a Gantt chart with, since it has two Y-values for start and finish. Unfortunately, the RangeBar chart does not allow a secondary date aggregate of “min” or “max”.

Advanced Options

Color code the whole bar

It is possible to add more series to the bar chart so you can color the bars differently depending on which phase in the pipeline they are, or you can group the estimated revenue into different categories. See this post for how to create and work with multiple filtered series in bar and column charts.

Gantt Chart for Opportunities color coded according to value

Gantt Chart for opportunities color coded according to estimated value

This chart was made creating 3 series, filtering the opportunities into different categories based on their estimated value, but could for example also be color coded according to current pipeline phase.

Divide the bar into multiple sections

We could also add more dates to the Gantt chart. Since it consists of a layered bar chart, we can have up to 9 different dates, which is the most allowed by the fetchxml. The fetchxml allows up to 10 attributes, one of which needs to be the groupby.

A Gantt chart for opportunities, where the end date for each stage in the sales process is registered in a separate field, could look like this.

Gantt Chart Sales Stages

Gantt Chart showing how much time was spent in each sales stage. Note that this was made only to show the capability of adding multiple date fields to the Gantt chart so each bar can have multiple sections. Further refinement on the chart above is certainly needed.

The user can then overview the progress on opportunities and how much time is actually spent in each phase and compare it to other opportunities. The tricky part is ensuring the dates are set in the right order in the xml, as the bars overlap each other.

Hope you liked the post. Please sign up for the email newsletter and follow me on Twitter  

IMPORTANT UPDATE: A lot of people are experiencing issues when creating this chart. This is due to MS Dynamics CRM, for whatever reason, no longer supports dates on the Y axis. You can get around it by adding this to the Y axis in your chart xml.

Format

And then optionally you can make the labels transparent. It forces Dynamics CRM to not render a date on the Y axis, which is where it fails. Now it just writes some text instead. That dates are no longer supported on the Y axis is very unfortunate. The Y axis is basically just blank. The best option is to keep the date on some of the labels so you at least can see them inside the chart. Hopefully they’ll come back soon, but who knows. Please write a comment if you see it working with dates again.

, , , , , , ,

69 Comments

LabelFormat Cheat Sheet

Formatting the labels on CRM charts can be a little tricky at times.

That’s why I’ve created a little cheat sheet, so I can always find a custom formatting option that fits, and copy it in to my CRM chart xml. Jump to the bottom of this post, if you want to go straight to the cheat sheet.

LabelFormat, or Format, can be used in two places. Either for the values displayed inside the chart, i.e. to indicate the exact value of a column, or the values on the axes, usually the Y axis. The formatting controls how values are displayed in charts. See examples below.

Before LabelFormat is applied on MS CRM Charts for MS Dynamics CRM 2011 in the chart xml

Standard labels for currency values in MS CRM Charts

After LabelFormat is applied in the chart xml for MS CRM 2011 Dynamics charts

With a custom format added to the chart xml, the values shorter and easier to read.

At the end of this post, I will explain where to insert the formatting and which property to use, depending on whether you want to format the values on the axis, or the values inside the chart.

I’ve tried to “format” my examples as code, so they can be cut and pasted directly into a crm chart xml. Hopefully I succeeded.

Disclaimer: This post contains a ton of commas and decimal points, and I’m sure I misplaced some of them. As Murphy’s Law will have it, it will be in the one you might want to copy. So always check that the results are what you want.

First I’ll go through how the formats are structured.

Important LabelFormat Characters

0     – Zero placeholder. Shows all digits incl. zeros.
#     – Digit placeholder. Shows all digits except zeros, unless the zero is significant.
.      – Decimal point
,      – Thousand separator and number scaling
;      – Section separator

The difference between # and 0 is how zeros are handled. Note the difference in the following two formats when displaying the value “0.30”

LabelFormat="#.##"        = .3
LabelFormat="0.00"        = 0.30

Or for example, the zeros in 30 and 105 are significant, but they are not significant in 0.50 and 11.00 (and 0 itself) and therefore not displayed.

Number Scaling – The Thousand Separator and Decimal Point

The thousand separator, or number scaler, is helpful if you want to shorten large numbers. Such as showing numbers in millions. Adjusting the amount of thousand separators allow you to adjust how much the number is scaled. Adding a decimal point and a placeholder, allow you to control the amount of decimals after the number has been scaled. See the following examples for the value 3,456,852.50

LabelFormat="#,#,,"        //  = 3
LabelFormat="#,#,,.##"     //  = 3.46
LabelFormat="#,#,,.###"    //  = 3.457
LabelFormat="#,#,.#"       //  = 3,456.9

Now the same formats for the value 456,852.5

LabelFormat="#,#,,"            // =  (nothing)
LabelFormat="#,#,,.##"         // = .46
LabelFormat-"#,0,,.###"        // = 0.457
LabelFormat-"#,0,.#"           // = 456.9

Note the # placeholder was changed to a zero in the last examples. That way the value can have a leading zero instead of just a dot.

Values are always rounded.

Add Text or Characters

You can add characters, like currency symbols, and spaces to the format so it is easier to read. Example value 3,456,852.50

LabelFormat="$#,#,,M"           //  = $3M
LabelFormat="$#,#,,.##M"        //  = $3.46M
LabelFormat="$ #,#,, M"         //  = $ 3 M
LabelFormat="United States Dollars #,#,,.## M"  //  = United States Dollars 3.46 M

Caution: There’s no check that you are adding the right number of thousand separators or adding the correct currency symbol to the format. In a multi-currency setup, you could easily create a chart that displays 500 Euros as “$ 500 k”.

Sections

The semi-colon can be used to add more sections to your format. You can have up to three section in your format string.

  • 1st section  –  Positive Values
  • 2nd section  –  Negative Values
  • 3rd section  –  Zero Values

This means you can format positive, negative and zero values individually and/or suppress some of them. So yes, that means it is possible to suppress zeroes in MS CRM charts.

If no extra sections are added, all values are displayed using the one format provided. Negative values are displayed with a minus sign -.

Example: Positive value is displayed with two decimals, negative values are in a parenthesis, and zero values are just a single zero.

LabelFormat="#,0.00;(#,0.00);0"

Value 24.5 shown as 24.50
Value -24.5 shown as (24.50)
Value 0.00 shown as 0

If you do not add a parenthesis or a minus sign, or something, to the negative value, it will display just like the positive values with no indication of being negative aside from its position on the axis.

Suppress Zeros

You can suppress zeros, or the negative or positive values depending on your needs. Just add two single quotes in the appropriate section.

Suppressing Zeros

LabelFormat="#,0.00;(#,0.00);''"

Suppressing Negative values and zeros

LabelFormat="#,0.00;'';''"

When adding text such as “$” and “k” to your format, it is good use all three sections and handle the zero separately. Otherwise zero values can appear as “$ k”.

Currency

Not really a setting, but there are a few things worth mentioning about labels and currency.

  • If no format is added, the chart automatically uses the correct currency symbol
  • When using the base currency field, it shows the value in the base currency
  • When using the normal currency field, the chart shows the values in the Users default currency, and properly calculated based on the exchange rates in the system.

If you do not need to scale the number, you can use “C” followed by a specifier to indicate the amount of decimals. If you do not add a specifier, decimals will be the same as the default in your CRM.

LabelFormat="C"      // Currency with default decimals
LabelFormat="C0"     // Currency with no decimals
LabelFormat="C3"     // Currency with 3 decimals forced

A negative value with “C” format is displayed in a parenthesis, unlike the default format which uses a minus sign.

These cannot be combined with the custom formats, so you can’t use the currency setting and scale the number at the same time. My guess is scaling is the main reason you are reading this post.

The Percentage Sign %

If you add a percentage sign % in your format, the number will automatically be multiplied with 100.

LabelFormat="#%"     // will display 0.5 as 50%

This is different from changing the label property to Label=”#PERCENT”. That is not really number formatting, so I will need to leave “#PERCENT” , “#AXISLABEL” and other keywords for a future blog post.

Cheat Sheet

LabelFormat="$#,0"           // No scaling, No decimals, leading zero
LabelFormat="$#,0,K"         // Thousands,  No decimals, leading zero
LabelFormat="$#,0,,M"        // Millions,   No decimals, leading zero
LabelFormat="$#,0,,,B"       // Billions,   No decimals, leading zero
LabelFormat="$#,0,,,,T"      // Trillions,  No decimals, leading zero

LabelFormat="$#,0.00"        // No scaling, Two decimals, leading zero
LabelFormat="$#,0,.00K"      // Thousands,  Two decimals, leading zero
LabelFormat="$#,0,,.00M"     // Millions,   Two decimals, leading zero
LabelFormat="$#,0,,,.00B"    // Billions,   Two decimals, leading zero
LabelFormat="$#,0,,,,.00T"   // Trillions,  Two decimals, leading zero

LabelFormat="$#,0.##"        // No scaling, Up to two decimals, leading zero
LabelFormat="$#,0,.##K"      // Thousands,  Up to two decimals, leading zero
LabelFormat="$#,0,,.##M"     // Millions,   Up to two decimals, leading zero
LabelFormat="$#,0,,,.##B"    // Billions,   Up to two decimals, leading zero
LabelFormat="$#,0,,,,.##T"   // Trillions,  Up to two decimals, leading zero

LabelFormat="$#,#.##"        // No scaling, Up to two decimals, no leading zero
LabelFormat="$#,#,.##K"      // Thousands,  Up to two decimals, no leading zero
LabelFormat="$#,#,,.##M"     // Millions,   Up to two decimals, no leading zero
LabelFormat="$#,#,,,.##B"    // Billions,   Up to two decimals, no leading zero
LabelFormat="$#,#,,,,.##T"   // Trillions,  Up to two decimals, no leading zero

Inserting the LabelFormat in the chart XML

In a Series

If you want to change the format for the values inside the chart, you have to insert the LabelFormat property in the Series collection.
Series insert LabelFormat
If the IsValueShownAsLabel=”True” not present already, then insert it as well.
Note that this is done per series, so you can have different formatting for each series if you need to.

On an Axis

If you want to change the formatting along the axis, usually the Y axis, insert it in the LabelStyle section in the appropriate axis.
Axis Format Insert
Note that the property here is called Format and not LabelFormat, but the structure of the format string is the same.

Hope you liked the post. Feel free to sign up for the email notification or follow me on Twitter 

The reference for Custom Numeric Format String can be found on the Microsoft website here http://msdn.microsoft.com/en-us/library/aa719871(v=vs.71).aspx

, , , , , ,

49 Comments

Groupby Business Unit – even when not present (i.e. Activities/Opportunities)

In setups where both Users and Teams own records, it can sometimes be useful to group data by the owning Business Unit, rather than by the Users or Teams individually.

Most entities allow you to add an “Owning Business Unit” column in Advanced Find to take care of that, but some do not. Most notably the Activity entities, incl. Phone Calls, Appointments, etc. do not have this option. We can only get access to the “Owner”, “Owning User” and “Owning Team” from the UI. Regardless of which one we use, either we will get Teams separately from Users or we will only see the Business Unit related to either the Team or the User. This is also the case for the Opportunities entity.

Of course, the records still have an Owning Business Unit, and luckily, we can still call it in the chart xml directly.

Just copy the <link-entity> tags and the groupby attribute from a CRM chart xml that does support “Owning Business Unit” in the UI. Then use it to replace the attribute groupby line in your Activity chart xml.

Or just grab the code from here.

<link-entity name="businessunit" from="businessunitid" to="owningbusinessunit" link-type="outer">
<attribute groupby="true" alias="REPLACE ALIAS" name="name" />
</link-entity>

Just insert the alias from the original groupby attribute where it says “REPLACE ALIAS”.

Grouping directly by Business Unit allow you to see both User owned and Team owned records as one. That way you don’t have to create 2 charts or create multiple series that go through the “Owning User” and “Owning Team” fields to find the proper Business Unit.

Don’t forget to follow me on Twitter

, , , , , ,

Leave a comment

Groupby on a related entity’s related entity

Are we focusing on the right Accounts and Opportunities?

A common question in sales departments. Accounts are often classified in some kind of A, B, C categorization based on their potential value etc. Ideally, that means you sales activity should primarily be related to A accounts and some B accounts. However, a common setup in CRM, is to set your sales activities regarding an Opportunity.

So if I want to see if sales activities are focused on the right type of Accounts, I need a chart that show me all the Activities related to an Opportunity, but group the data by the A, B, C categorization on the Account. Something that is not possible in the MS CRM Chart Designer.

The tricky part here is that we now have two steps.

Activity -> Opportunity -> Account

Had we only had one step, it would have been fairly easy to do everything in the MS Dynamics CRM Chart Designer. For example, if you wanted an overview of your Est. Revenue in Opportunities based on the type of Account, all you need to do is add the field from the Account entity, to your Opportunities View in “Advanced Find”, save the View, and then use it when you open the chart editor. Thank you to Adam Vero for having brought that very helpful shortcut to my attention.

Since our scenario has two steps, we can’t use this trick. However, we can get Advanced Find to do the most of the dirty work.

In Advanced Find, from the Activities entity, create a filter that goes to the Regarding (Opportunity), to the Potential Customer (Account) and check if the desired field, Classification, contains data.

Advanced Find - Activity to Opportunity to Account

Save the View and download the FetchXML for later as it contains the needed link information in xml format.

Next I’ll create a chart in the chart editor that I can use as a base for my new chart xml. I want my final chart to be a pie chart grouped by the Account classification. Since I need to add that link in the chart xml, I’ll create a similar chart and use another field as a placeholder.

Create base chart xml to modify later. crm chart xml

In this case the field “Priority” is the placeholder for the A, B, C classification.

I’ll export this chart and now I’ve got the pieces I need.

First I’ll take what I need from the FetchXML I downloaded from Advanced Find..

FetchXML - pieces need to modify crm chart xml

Highlighted are the needed lines with the <link-entity> information from Activity -> Opportunity -> Account, as well as the schema name for the Classification field, which we want to group by. We do not want to keep the filter information in this case.

Now that I have the link information, I’ll open the Chart XML and locate the groupby attribute line in the fetchcollection part.

Original fetchcollection for chart xml - locate groupby attribute

I want the <link-entity> tags from the FetchXML to surround this groupby attribute line. Then the placeholder, the prioritycode, should be replaced with the “accountclassificationcode” from the Account entity.

Final version should look like this.

fetchcollection in the chart xml after modifications

Import it back into CRM and use it on a View that only show Activities related to an Opportunity.

CRM Chart showing Sales Activities related to an Opportunity grouped by the Type of hte Potential Customer

Number of Opportunity Sales Activities grouped by Account Type

Hopefully that’ll get you a little closer to knowing if your Sales/Opportunity time is spent on Accounts that are worth it. In this case, more time is spent on B-Accounts than A-Accounts.

So, how many nested <link-entities> can CRM handle? Well, you can go at least 4 entities out. Beyond that, and probably sooner, you’ll hit a point there the chart just doesn’t make sense any longer.

Depending on the volume of records, this is an area where you can tell that these charts load a little slower. Only a few extra seconds, so enough to be noticeable, but it shouldn’t impact usability.

Thanks for reading and please follow me on Twitter

, , , , ,

9 Comments