The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

CRM 2011 Charts – Know the Real Potential ~ Part I

CRM 2011 Charts – Know the Real Potential ~ Part I

  • Comments 6

clip_image002Microsoft Dynamics CRM 2011 brings to you extensive charting and dashboard capabilities, helping the users to analyze their data quickly and effectively.

In this blog series I will be discussing the enriched chart features which will help you create the complex business charts to gain real time insights.

Before diving deeper into the CRM charting potential we need to first understand some key charting concepts and terms which I will be referring in the blog going forward:

The Chart components - The chart control is rendered as a single image. A chart can contain one or more series where each series is a collection of the data points.

For instance, in the chart to the right you have two series- Estimated Revenue, Actual Revenue and one category – Potential customer:

First series has chart type column and the second one is configured as a line. Thus to have multiple lines in the chart, you would define one series for each line.

The chart designer allows you to create only single series chart with one category. But through import and export feature, you can customize your chart definitions and create any complex chart – personal or system if you have the required privileges.

To understand the chart definition better let’s look at the exported Xml of an OOB chart.

1. Navigate to the Accounts entity

2. Click the collapsed chart pane .Select the chart Accounts by Industry.

3. Click on the Charts tab in ribbon.

4. Click export Chart.

You will get a XML like this:

image

image

A chart is defined using two important aspects – data and appearance. The XML subpart highlighted Yellow is the DataDescription for the chart which defines the underlying data used to render chart.

The other subpart highlighted Green is called as the Presentation Description for the chart which defines the presentational aspects (chart type, color, legends) of the chart.

The CRM 2011 business charts leverages the Asp .Net Chart Controls (available in .Net 3.5 >) and hence the presentation description is exactly similar to the .net chart controls serialized xml.

To understand it better let’s try adding a new dimension to the above chart J . Yes, you heard it right the 3D charts are available in CRM 2011 –

Add the Area3DStyle node under ChartArea in the exported xml:

<ChartAreas>

   <ChartArea>

      <Area3DStyle Enable3D="true"/>

You might have caught the hint that CRM charts have all the capabilities that asp .net chart controls possess. You just need to tweak the xml and import it back!!

Time to explore the wide array of charting features! Here are some business users’ requirements and we can achieve these and many more using CRM charts-

1. Multi Series Charts - I want to compare the revenue earned with the discount provided.

2. The Stacked, Pyramid charts look cool .Can I create such charts?

3. Comparison Charts or Multi category- How can I compare my company's sales for 2010 with sales for the year 2009

4. Large differences in data ranges and the chart looks ugly – do charts support a scale break?

5. Can I customize the series name?

To help with you an example let’s try to create a multi series chart -Estimated revenue v/s Actual revenue

1. Navigate to Opportunities.

2. Click Charts tab in ribbon.

3. Click New Chart.

4. Select Estimated Revenue as Series, Sum as the aggregate.

5. Select Potential Customer as Category.

6. Save and Close.

At this point you have created a single series chart which depicts Estimated Revenue for each Potential Customer.

clip_image004

7. Now Click Export and save the Xml. You will get the exported Xml like:

<visualization> <visualizationid>{A3A9EE47-5093-DE11-97D4-00155DA3B01E}</visualizationid> <name>Sum(Estimated Revenue) by Owner</name > <description>Shows the estimated revenue by owner.</description> <primaryentitytypecode>opportunity</primaryentitytypecode> <datadescription> <datadefinition> <fetchcollection> <fetch mapping="logical" aggregate="true"> <entity name="opportunity"> <attribute alias="aggregate_column" name="estimatedvalue" aggregate="sum" /> <attribute groupby="true" alias="groupby_column" name="ownerid" /> </entity> </fetch> </fetchcollection> <categorycollection> <category> <measurecollection> <measure alias="aggregate_column" /> </measurecollection> </category> </categorycollection> </datadefinition> </datadescription> <presentationdescription> <Chart> <Series> <Series ChartType="Column" IsValueShownAsLabel="True" Color="55, 118, 19" BackGradientStyle="TopBottom" BackSecondaryColor="41, 88, 145" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40"> <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" IsReversed="False"> <MajorGrid LineColor="239, 242, 246" /> <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" IsReversed="False"> <MajorGrid Enabled="False" /> <MajorTickMark Enabled="False" /> <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" /> </AxisX> </ChartArea> </ChartAreas> <Titles> <Title DockingOffset="-3" Font="{0}, 13px" ForeColor="59, 59, 59" Alignment="TopLeft"></Title> </Titles> </Chart> </presentationdescription> <isdefault>false</isdefault> </visualization>

We will change the same xml, add new series and import it back. For adding another series depicting the aggregated actual revenue earned by the salesperson make following changes to the chart definition xml:

Chart Fetch: Add a new attribute in the fetch with required alias (say aggregate_column1) and aggregate operator (say sum)

image

Data Description: Add a new measure collection a.k.a series with the newly added alias in the fetch.

image

Presentation Description:

a) Add a new series in the presentation xml.

image

b) Let’s add legends to identify the two series.

image

You can modify the color and presentation of original series as well. Note the IsValueShownAslabel set to false since I don’t want labels to be displayed with each column. And the color was modified as well.

You have now created the definition for a multi series chart. Follow these simple steps for importing the chart back to the system:

1. Navigate to Opportunities.

2. Click Charts tab in ribbon.

3. Click Import Chart.

4. Select the xml file created above.

5. Click Ok.

6. Choose to rename the new chart and import.

clip_image006

And the multi series chart with your own chart styling is here!

The next post of this blog series will be covering the rest of the exciting chart features. Till then stay tuned..

Cheers,

Sonal Sawhney



  • Nice article, time to spend some time on CRM 2011 charting!

  • Hi

    You can add an example as add more entity.

    I want add more entity to create chart

    thanks

  • Sen,

    Can you clarify what you mean by add more entity? If you mean custom entity then yes you can create a new entity and when you will navigate to its list you will see the charts for the new entity (if any you created )else you can create it from the designer in Ribbon(Click new in charts Tab).

    OR if you want to create chart using multiple entities in fetch then Charts can be created only using related entites.For eg. To create a chart for account using attribute from contact you would have to define contact as the link entity node in the fetch xml for the chart in data description.

    Thanks,

    Sonal.

  • Awesome article!

    Question: how do you format the data labels to display as $MM?

  • JD,

    You can acchieve that by customizing chart xml.Do it through import /export.You can define the format in LabelStyle node under the Chart Series node.

    More details about the format -

    support2.dundas.com/Default.aspx

  • Hi Sonal can you send me the edited XML file please as I'm having some trouble re-importing the chart.

    Here is my version:

    <visualization>

     <visualizationid>{88625A6D-C4B9-E011-9F86-005056A11113}</visualizationid>

     <name>Est. Revenue by Potential Customer</name>

     <primaryentitytypecode>opportunity</primaryentitytypecode>

     <datadescription>

       <datadefinition>

         <fetchcollection>

           <fetch mapping="logical" aggregate="true">

             <entity name="opportunity">

               <attribute alias="aggregate_column" name="estimatedvalue" aggregate="sum" />

               <attribute alias="aggregate_column1" name="actualvalue" aggregate="sum" />

               <attribute groupby="true" alias="groupby_column" name="customerid" />

             </entity>

           </fetch>

         </fetchcollection>

         <categorycollection>

           <category>

             <measurecollection>

               <measure alias="aggregate_column" />

             </measurecollection>

              <measurecollection>

               <measure alias="aggregate_column1" />

             </measurecollection>

           </category>

         </categorycollection>

       </datadefinition>

     </datadescription>

     <presentationdescription>

       <Chart>  

         <Series>

           <Series ChartType="Column" IsValueShownAsLabel="false" Color="149, 189, 66" BackGradientStyle="TopBottom" BackSecondaryColor="112, 142, 50" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40">

           <SmartLabelStyle Enabled="True"/>

           </Series>

           <Series BorderWidth="3" ChartType="Line" Color="49, 171, 204" MarkerStyle="Square" MarkerSize="4" MarkerColor="37, 128, 153" MarkerBorderColor="37, 128, 153">

           </Series>

         </Series>

         <Legends>

          <Legend Alignment="Center" LegendStyle="Table" Docking="Bottom" Font="{0},11px" ShadowColor="0, 0, 0, 0" ForeColor "59, 59, 59">

         </Legends>

         <ChartAreas>

           <ChartArea BorderColor="White" BorderDashStyle="Solid">

             <AxisY LabelAutoFitMinFontSize="8" TitleForeColor="59, 59, 59" TitleFont="{0}, 10.5px" LineColor="165, 172, 181" IntervalAutoMode="VariableCount">

               <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" IntervalAutoMode="VariableCount">

               <MajorGrid LineColor="Transparent" />

               <LabelStyle Font="{0}, 10.5px" ForeColor="59, 59, 59" />

             </AxisX>

           </ChartArea>

         </ChartAreas>

         </Titles>

       </Chart>

     </presentationdescription>

     <isdefault>false</isdefault>

    </visualization>

Page 1 of 1 (6 items)
Leave a Comment
  • Please add 2 and 3 and type the answer here:
  • Post