Archive for May, 2013

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