Multi-Series Charts on a Single Axis – Dynamics CRM 2011

Paul Nieuwelaar, 27 November 2012

In Dynamics CRM 2011 Rollup 5, Microsoft introduced multi-series charts. Basically this means you can now graph 2 or more fields side by side so you can compare these values visually. One example of this would be Opportunities Est. Revenue vs. Actual Revenue, grouped by Owner. Using this example, we would have one Category (Horizontal) axis; Owner, and 2 Series (Vertical) axis; Est. Revenue and Actual Revenue. When the chart is previewed, the sum of both values for each user will be displayed side by side, to see which users are under or over estimating.

We can see in the image above the chart showing Est. Revenue vs. Actual Revenue, grouping by Owner. In the chart designer, we have defined the revenue fields as Legend Entries (Series), and the Owner as the Horizontal (Category) Axis.

You may have noticed in the first image, that there were 2 different scales, one for each of the series. In this case, the Est. Revenue scale goes up to 300,000 and the Actual Revenue scale to 200,000. This means the values being compared are not a direct comparison, as you can see in the first column the Actual Revenue which appears more than the Est. Revenue is actually less.

Fortunately we can modify the chart XML to allow the chart to use the same scale for both values. First you need to export the chart, which you can do in one of the following ways:

FOR PERSONAL CHARTS:

Expand and view the chart you want to export from an entity grid view. On the Charts tab of the ribbon, click the ‘Export Chart’ button. NOTE: To re-import the chart use the ‘Import Chart’ button above.

FOR SYSTEM CHARTS:

Select the chart from the Customization area/Solution for that entity. Click ‘More Actions’, and then ‘Export Chart’. NOTE: To re-import the chart use the ‘Import Chart’ option above.

When you export the chart, an xml file will be downloaded. Open this file (in notepad is fine). Within the <presentationdescription> tag, you will see a <series> tag containing another <series> tag for each of your series defined in the chart. On the second series, you will see an attribute called ‘YAxisType’. Remove this attribute so that it looks like the <series> tag above it.

  <presentationdescription> 
    <Chart Palette="None" PaletteCustomColors="55,118,193; 197,56,52; 149,189,66; 117,82,160; 49,171,204; 255,136,35; 97,142,206; 209,98,96; 168,203,104; 142,116,178; 93,186,215; 255,155,83">
      <Series> 
        <Series ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"></Series>
        <Series ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" YAxisType="Secondary"></Series>
      </Series> 

Save the XML file, and then import the file using the same method you used to export it. Browse to the XML file, and then click next. You will be prompted to Replace the existing chart, or to Keep Both. Choose ‘Replace’.

The chart will be updated, and you can now check the chart again to view the changes (make sure to publish system charts). You will notice the chart now uses only one axis, and the values are compared accurately.

The drilldown functionality will still be available, so users can click on any of the bars to view the records that make up that section. You can also still modify the chart using the CRM chart designer without effecting the modified XML.