Can’t Relate to your Charts?

Posted by Sonoma Partners on December 17, 2012  |  commentsComments (4)

Today's guest blogger is Peter Majer, a Senior Consultant at Sonoma Partners

CRM 2011 introduced some very powerful analytical tools including Charts and Dashboards.  With Charts (or Inline Visualizations) you’re able to quickly see a graphical representation of your current record set that’s being displayed in the grid.  You can also drill into components of your chart to slice and dice your data even further. Even better is that the record set in your grid is also updated as you drill in!

CRM 2011 comes with a set of standard charts out of the box for a handful of native entities (e.g., Accounts and Opportunities).  Even better is the fact that end users and administrators are able to create their own charts based on their security settings.  This allows users to define specific analytical charts they can share and use to quickly see a graphical representation of the data that’s important to them.

However, there’s a small limitation with building charts in CRM.  You can only display data from the current entity that you’re on, or from the primary attribute of lookup entities.  For example, if you’re interested in seeing a breakdown of your current open Opportunities Estimated Revenue by Account Industry, this isn’t possible using the native chart designer tools that CRM provides.  This is because Account Industry is a field on the parent Account, not on the Opportunity, and isn’t the primary attribute (Account Name is the primary attribute).

Luckily there’s a way around this.  It involves a little bit of knowledge of XML but not too much that you’d need a developer to complete this for you.  The overall approach is to create a chart using fields on the base entity, exporting it, updating the XML manually, and re-importing the chart back into CRM.  We’ll go into more detailed steps on how to accomplish this below.

In order to create the “Opportunity Est. Revenue by Account Industry” chart displaying data from the related Account entity, perform the following steps:

1. Create a New CRM Chart
First navigate to the entity you’re interested in building the chart off of.  In our example, this is Opportunity.  After you’re in Opportunities, you can click on Charts in the Ribbon, and then New Chart. 

Majer-blog-image1

2. Enter the Basic CRM Chart Details Using Fields on the Opportunity
In our example we’ll use the following settings for our basic Chart which will show Estimated Revenue by Potential Customer. 

Type = Pie
Legend Entries (Series) = Est. Revenue (Sum)
Horizontal (Category) Axis Labels = Potential Customer 

Majer-blog-image2

3. Save Your Chart
Now save your chart using the Save & Close button in the Chart Tools Design tab of the Ribbon.

Majer-blog-image3

4. Export Your Chart
The chart created above was a start, but we wanted to display Est. Revenue by the Industry of the Potential Customer (Account), not the Name.  Therefore we need to export it and modify the XML to get what we need.  To export your chart, click on the Charts tab of the Ribbon, and then click on Export Chart.

Majer-blog-image4

5. Open the Chart XML
Navigate to the XML that was exported above, and open it in any text editor.   You’ll notice something that looks like the following: 

Majer-blog-image5

6. Modify the Chart XML
The section of interest is the <attribute> nodes of the XML in the <fetch> section.  More specifically the one that has the Potential Customer field in it (customerid) is the one that needs to be updated.  Comment out this line by adding in the comment tags for XML at the beginning !-- and at the end -- of the node.  Those go between the begin and end carrots of the node. 

Majer-blog-image6

Now enter in the new XML right above the XML you just commented out: 
 
<link-entity name="account" from="accountid" to="customerid" link-type="outer">

       <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="industrycode" />

</link-entity>

In a nutshell, this is indicating to use a link-entity to join to the Account entity from the current entity (Opportunity).  The link is on the customerid attribute of the Opportunity linking to the accountid attribute of the Account.  Then, after that linkage is done using link-entity, you’re indicating to now use the industrycode attribute for your chart’s groupby.  The XML we commented out previously indicated the groupby was the customerid field which displays the Name of the Account (primary attribute).

In the end, your XML should look something similar to this:

Majer-blog-image7

7. Optional 3D Step
This step is optional and is another neat styling feature that is available with the CRM charts.  Again, this is available only by modifying the XML and not available by using the designer CRM provides you.

If you search the XML for Area3DStyle, you’ll see Enable3D=”false”.  You can update this to true, and save the document, and now you’ll see a fancier 3D chart when you import back into CRM. 

Majer-blog-image8

8. Import Your Chart
After saving the XML, you’re ready to import your chart.  In CRM you can go to the Charts tab of the Opportunity Ribbon, and select Import Chart.  Browse for your XML you just saved. 

Majer-blog-image9

9. Enjoy Your Chart!
After uploading the chart, you can now see your 3D chart that shows Opportunity Estimated Revenue by Account Industry.   

Majer-blog-image10

And that’s it!  You can apply the same steps above to create complex charts by modifying the XML that will create charts based on fields that you’re not able to create using CRM’s native designer.

Not that if you click on Edit Chart in the ribbon, you’ll see that it’s correctly showing you that your chart’s “Horizontal (Category) Axis Label” is the Industry field from the Potential Customer (Account).  

Majer-blog-image11

However, if you do end up changing this value and saving your chart, you won’t be able to change it back to this “non-primary-attribute” related field without going through the steps above to manually edit the XML.

Thanks to Jacob Cynamon-Murphy for assisting with finding out this trick with charts.

Enjoy!

 

Comments

  1. Good solid article, though doesn't deal with the problem that users are forced to make these changes to the raw XML for No Good Reason.

    What part of:

    In a nutshell, this is indicating to use a link-entity to join to the Account entity from the current entity (Opportunity). The link is on the customerid attribute of the Opportunity linking to the accountid attribute of the Account. Then, after that linkage is done using link-entity, you’re indicating to now use the industrycode attribute for your chart’s groupby. The XML we commented out previously indicated the groupby was the customerid field which displays the Name of the Account (primary attribute).

    Isn't actually going to require a developer* in practice?

    * Any sufficiently advanced super-user is indistinguishable from a developer.

    Posted by: Mike Ralphson  |  Dec 19, 2012 6:52:57 AM

  2. I've found perhaps an easier method--one that doesn't involve a developer/advanced superuser. If you create a view on a particular entity (ie - Opportunities), you can also add columns from associated entities (ie - Accounts). Once you include these columns on a view, you can create a chart from that view and it will now include those fields as options for developing your chart. The list of fields always includes the view column field list (which includes fields from associated entities) before the main entity full field list.

    This trick is not documented (as far as I have found)--but should work well in the above example.

    Posted by: Tim Priestley  |  Dec 22, 2012 1:30:04 PM

  3. Of course, haven't yet figured out how to get 3D without editing the report's xml directly :)

    Posted by: Tim Priestley  |  Dec 22, 2012 1:33:22 PM

  4. Mike - you make a good point that updating the XML and knowledge of FetchXML isn't something that everyone has. It's not something that a typical end user would posses, but I don't think you'd need a developer for some of the simpler requests. There are also a handful of tools that are available to help assist you with generating the FetchXML. CRM 2011 also allows you to generate the FetchXML from an Advanced Find view so you can create your view in Advanced Find, and then generate the FetchXML that makes up the view.

    Tim - great find with the alternative approach listed above. As you stated, your approach should accomplish the same thing I detailed above. However, if you wanted to expose some of the other raw power that the charting engine posses but only exposes in the XML (e.g., 3d) then you'll need to modify that in the XML itself. I haven't heard any rumors yet, but maybe some of these features will surface to the CRM UI in future releases.

    Posted by: Peter Majer  |  Jan 14, 2013 9:39:47 PM

Post a Comment

  • *Required

Contact Us for a Quote, or Personalized Demonstrationof Microsoft Dynamics CRM for Your Business.

Contact Us