Dynamics CRM: Creating a radar chart

From all of the new features in Dynamics CRM 2011, the one that got me thrilled the most is charts. Dynamics CRM comes with a selection of charts for opportunities, leads, accounts and so on. It also allows users to create five different chart types straight from the Dynamics CRM interface: Column, Bar, Line, Pie and Funnel.

However, there is more to charts than it meets the eye. The charts functionality in Dynamics CRM leverages the ASP.NET chart controls, which means that we have at our disposal over 20 different chart types we could use. I decided to put the theory into practice and I created my very first radar chart for Dynamics CRM: Deals Won vs. Deals Lost by Territory.

In Dynamics CRM, charts are based on a proprietary language query called FetchXML. The chart design tool can be quite handy to design simple charts, without the need to meddle with FetchXML code. However, the design tool only allow the creation of five different chart types (column, bar, line, pie and funnel) and it also doesn’t allow the creation of charts with multiple series and group-by attributes*. For more complex charts or charts based on other chart types which aren’t one of the “canonical fives” previously mentioned, we must take our design efforts beyond the chart design tool. In this case, we must do some coding in FetchXML.

Update Roll-Up 5: With the release of Update Roll-Up 5 9 (see KB 2567454), Microsoft included enhancements to the chart designer, including multiple series and the ability to create area charts, bringing the number of chart types supported in the designer from five to six.

When viewing charts in Dynamics CRM, we have the option to export the chart definition as a XML file so we can edit it with our favourite text editor (I use Notepad++). We can then upload our modified XML charft definition back into Dynamics CRM.

For this exercise, I downloaded the XML definition of two charts which I used as reference: Deals Won vs. Deals Lost By Fiscal Period and Sales Progress by Territory. Based on those two chart definitions, I got enough information to go ahead an create my own chart, and I came up with a radar chart entitled Deals Won vs. Deals Lost By Territory.

Here is the code for this chart. Feel free to give it a try and modify it at will.

<visualization>
  <visualizationid>{8BADBB34-01E6-E011-80C0-00155D18443B}</visualizationid>
  <name>Deals Won vs. Deals Lost By Territory</name>
  <description>Shows the amount of revenue for won deals versus lost deals by territories.</description>
  <primaryentitytypecode>opportunity</primaryentitytypecode>
  <datadescription>
    <datadefinition>
      <fetchcollection>
        <fetch mapping="logical" aggregate="true">
          <entity name="opportunity">
            <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
              <attribute alias="sum_won" name="actualvalue" aggregate="sum"></attribute>
              <filter>
                <condition attribute="statecode" operator="eq" value="1" />
              </filter>
            </link-entity>
            <link-entity name="opportunity" from="opportunityid" to="opportunityid" link-type="outer">
              <attribute alias="sum_lost" name="estimatedvalue" aggregate="sum"></attribute>
              <filter>
                <condition attribute="statecode" operator="eq" value="2" />
              </filter>
            </link-entity>
            <link-entity name="account" from="accountid" to="customerid" link-type="outer">
              <link-entity name="territory" from="territoryid" to="territoryid" link-type="outer">
                <attribute groupby="true" alias="groupby_territory" name="name" />
              </link-entity>
            </link-entity>
          </entity>
        </fetch>
      </fetchcollection>
      <categorycollection>
        <category>
          <measurecollection>
            <measure alias="sum_won" />
          </measurecollection>
          <measurecollection>
            <measure alias="sum_lost" />
          </measurecollection>
        </category>
      </categorycollection>
    </datadefinition>
  </datadescription>
  <presentationdescription>
   <Chart Palette="None">
      <Series>
        <Series ChartType="radar" Name="o:opportunity_statecode,1" Color="149, 189, 66" IsValueShownAsLabel="False" BackGradientStyle="TopBottom" BackSecondaryColor="112, 142, 50" Font="{0}, 9.5px" LabelForeColor="59, 59, 59">
          <SmartLabelStyle Enabled="True" />
        </Series>
        <Series ChartType="radar" Name="o:opportunity_statecode,2" Color="255,124,31" IsValueShownAsLabel="False" BackGradientStyle="TopBottom" BackSecondaryColor="235,98,0" Font="{0}, 9.5px" LabelForeColor="59, 59, 59">
          <SmartLabelStyle Enabled="True" />
        </Series>
      </Series>
      <ChartAreas>
        <ChartArea BorderColor="White" BorderDashStyle="Solid">
          <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181">
            <MajorGrid LineColor="239, 242, 246" />
            <MajorTickMark LineColor="165, 172, 181" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisY>
          <AxisX LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181">
            <MajorGrid Enabled="False" />
            <MajorTickMark Enabled="False" />
            <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />
          </AxisX>
        </ChartArea>
      </ChartAreas>
      <Titles>
        <Title Alignment="TopLeft" DockingOffset="-3" Font="{0}, 13px" ForeColor="0, 0, 0"></Title>
      </Titles>
      <Legends>
        <Legend Alignment="Center" LegendStyle="Table" Docking="Bottom" Font="{0}, 11px" ForeColor="59, 59, 59"></Legend>
      </Legends>
    </Chart>
  </presentationdescription>
  <isdefault>false</isdefault>
</visualization>

Copy the above code and paste it into your favourite text editor, then save the file with as an XML file (make sure the file terminates with the .XML extension, and not something else such as MyChart.TXT or MyChart.XML.TXT). Now you can go back to Dynamics and import your XML chart definition file.

Import as a personal chart (only available to you): Navigate to the Opportunity entity by clicking on Opportunities at the left-hand side navigation. Then click on Import Chart button located in the Chart ribbon.

Import as a system chart (available to the whole organisation: Navigate to Settings, then Customizations and then select Customize the System. In the Default Solution window that appears, expand Entities, then Opportunity and select Charts. Click on More Actions in the menu bar and select Import Chart to import your chart definition. Once the file is imported, make sure you click on Publish All Customizations.

Hope you can find this chart useful. If you need further information on FetchXML, check out the following recommendations:

Further information: The following sites can be of help for those starting with charts in Dynamics CRM 2011:
Microsoft Developer Network: Library entry on FetchXML Schema
Microsoft Download: Microsoft Chart Controls for .NET Framework Documentation
Microsoft Developer Network: Samples Environment for Microsoft Chart Controls
Microsoft Dynamics CRM Blog: CRM 2011 Charts – Know the Real Potential ~ Part I
Microsoft Dynamics CRM Blog: CRM 2011 Charts – Know the Real Potential ~ Part Deux
Gareth Tucker: Quick Reference for Common CRM 2011 Chart Customisations

For help in choosing colours when creating charts for Dynamics CRM, refer to my Office Colour Scheme in the next post.