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.


4 Comments

  • Hi I'm looking for a while to embed charts like this in CRM. However Radar charts are often used to visualize different fields for each axes and applied on 1 record. For example: I have a Contact, including 5 fields: Soft Skills, Managament Skills, Technical Skills, Teaching Skills, … Each field is a number between 0 and 10. I would like to see a persons overall picture using a Radar chart. I can't find any sample that actually does this. Any help is welcome.
    All the samples I find use 1 field (Category) and it creates an axis for each possible value.

    Thx

    • Hi Johan,

      Thanks for stopping by.

      This is a very good point and I understand exactly what you mean. In fact the first radar chart I've ever came across was in an issue of Nintendo Power, in an article about the skills for the character classes in the Dragon Warrior III role playing game (way back in the day).

      I am not sure if this is possible, but I recommend you to check the links I suggest in the end of this post. Also if you're stuck, I suggest you post this a question on the Microsoft forums at http://msdn.microsoft.com.

      Hope this helps!

      Cheers,
      P.

  • Pedro,

    I'm attempting to create a chart that measures the fit between an opportunity and corporate capabilities. I have an Opportunity, including 5 fields: Past Performance, Management Skills, Technical Skills, Team, … Each field is a number between 0 and 10. I would like to see an opportunity's overall picture using a Radar chart.

    I thought i could start with your sample and modify the code.

    I copied your xml code into XML Notepad 2007 and attempted to import the chart definition. I got this error:

    "The specified XML file "radar chart defination2.xml" is either not valid XML or does not conform to the chart schema"

    Do I have to create the charts Deals Won vs. Deals Lost By Fiscal Period and Sales Progress by Territory beforehand?

    Can you think of anything I might be doing incorrectly?

    Thanks,

    Matt

    • Matt,

      Whether your xml has an errors or not, I think the first point to consider here is what are you trying to chart. By the looks of it you are trying to do something like Johan has mentioned above.

      I think there is a limitation on Dynamics CRM for what you are trying to achieve. Try to break down your chart components by considering which fields (aka variables) contain your categorical values (the axis of your chart) and which one is the one you are trying to measure on this category. You will notice that unfortunately, Dynamics CRM doesn't allow such dimension of charts.

      If we break down the example I gave above, you will notice that the categorical variable (the axes of the chart) is the values of the Territory entity. In the example above it only shows 5 axes because we only have 5 records of territories. If we had 20 territories, up to 20 axes could be shown in the chart (it would depend on the records displayed in the view).

      Now for the variables we are measuring in the chart. The green in the actual value field of opportunities that have been won, while the orange is the estimated value field of the opportunities that have been lost.

      Say that we also wanted the value of opportunities in progress to be displayed in grey. For that we would use the value of the estimated revenue field for opportunities that are open.

      So far I didn't find a way to make each axis on the radar chart to be the value of a different field. It is a shame because I'd love to have such chart as well, but I don't think this is possible using the charts feature of Dynamics CRM.

      Regards,
      P.