CRM 2011 Charts – Charting on Related Records - Story From: Microsoft Dynamics CRM Team Blog

CRM Software Free Trial -

 This blog demonstrates how to create a chart from data in the current entity alongside data from related entities.

Let’s take the following scenario:

A Not For Profit Organization is using CRM 2011 to track the pledges and donations made by their donors.
Each pledge a donor makes has a Pledge Amount and will have 1 to * donations associated to it. Each Donation has a Cash-Value and a Status. The Status will have one of the following values: Outstanding, Processing, or Collected.

The CRM user that is responsible to manage the pledges for a given cause needs to be able to quickly see the status of a given pledge i.e. the original amount of the pledge and the amount collected. One possibility is to show the following chart in the Pledge form:

 

where:

  • Pledged is the Pledge Amount. This value is only available in the Pledge entity (e.g. $1000).
  • Collected is the sum of all the Cash-Value of the Donations that have been collected. (e.g. three donations @ $200 have been collected)

We will cover how to:

  • Display the value of a field (Pledge Amount) from the current entity (Pledge) in the chart. This is not possible out of the box because a chart can only display field values from a related entity (Donation).
  • Display the sum of the values (Cash-Value) of related entities (Donation) having a specific Status (collected).

 

To create this chart and display it in the Pledge form, do the following:

1.       Go to Settings > Customizations > Customize the System.

2.       Create a Pledge entity with the field ‘Pledge Amount’ and a Donation entity with the fields ‘Cash-Value’ and ‘Status’.

3.       Open the Donation entity

4.       Create  a new bar chart

5.       Create a series for the field Cash Value.

6.       Open the Pledge main form

7.       Add a One Column Section

8.       Insert a Sub-Grid. Enter the unique Name and Label. Select the entity (Donations) and the default View. Also check the box ‘Show Chart Only’ in order to hide the sub-grid.

9.       In the formatting tab, set the number of rows to 12

10.   Save, Close, and Publish all the customizations

11.   Open a Pledge form

12.   You will see the following chart.

13.   Add a Global OptionSet in order to be able to change the legends’ labels

  • Go to Settings > Customizations > Customize the System.
  • Click on ‘Option Sets’ in the left pane and click on the menu item ‘New’
  • Add the 2 Options: Pledged and Collected. 

14.   Now we have to manually modify the chart.

15.   Export the chart.

  • Go to Settings > Customizations > Customize the System.
  • On the left pane, select Components > Entities > Donations > Charts
  • Select the chart ‘Pledge Status By Amount’
  • Click on the ‘More Actions’ > Export Chart and save the chart on your hard drive

16.   Open in your favorite XML editor or notepad

17.   Replace the generated <fetch>  with the following:

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

  <entity name=”new_donation”>

    <link-entity name=”new_pledge” to=”new_pledgeid” from=”new_pledgeid” link-type=”outer”>

       <attribute alias=”aggregate_pledgeamount” name=”new_pledgeamount” aggregate=”avg” />

    </link-entity>

    <link-entity name=”new_donation” from=”new_donationid” to=”new_donationid” link-type=”outer”>

       <attribute alias=”aggregate_collected” name=”new_cashvalue” aggregate=”sum” />

       <filter>

          <condition attribute=”statuscode” operator=”eq” value=”100000001″ />

       </filter>

    </link-entity>

    <attribute groupby=”true” alias=”groupby_column” name=”statecode” />

  </entity>

</fetch>

 

  • The first <link-entity does an outer-join between Donation and Pledge and computes the average of all the Pledge Amounts. The problem is, to include the pledge amount attribute in the same FetchXML query that contains other aggregated attributes; we need to specify an aggregation on the pledge amount as well.
    Since there will be one Pledge Amount per record, we need to compute the average in order to get the original Pledge Amount.
  • The second <link-entity does an outer-join between Donation and Donation. This is necessary because we want to filter the rows for a given value of the Status. The Filter clause uses the values of the Option Set that is used by Status (Collected)

18.   Rename the aliases in <category>  to match the ones above

<category>

  <measurecollection>

    <measure alias=”aggregate_pledgeamount” /> 

  </measurecollection>

  <measurecollection>

    <measure alias=”aggregate_collected” /> 

  </measurecollection>

</category>

19.   Replace the generated <Series> with the following: Note the attribute ‘Name’ which is telling to use the Option value of the OptionsSet as labels for the legends.

      <Series>

        <Series Name=”o:new_pledgestatus,100000000″ ChartType=”Bar” IsValueShownAsLabel=”True” Font=”{0}, 9.5px” LabelForeColor=”59, 59, 59″ CustomProperties=”PointWidth=0.75, MaxPixelPointWidth=40, DrawingStyle=Cylinder”>

          <SmartLabelStyle Enabled=”True” />

        </Series>

        <Series Name=”o:new_pledgestatus,100000001″ ChartType=”Bar” IsValueShownAsLabel=”True” Font=”{0}, 9.5px” LabelForeColor=”59, 59, 59″ CustomProperties=”PointWidth=0.75, MaxPixelPointWidth=40, DrawingStyle=Cylinder”>

          <SmartLabelStyle Enabled=”True” />

        </Series>

      </Series>

 

 20.   Add the <Legends> node manually at the end of the Chart node.

  <Legends>

    <Legend Alignment=”Center” LegendStyle=”Table” Docking=”right” IsEquallySpacedItems=”True” Font=”{0}, 11px” ShadowColor=”0, 0, 0, 0″ ForeColor=”59, 59, 59″ />

  </Legends>

</Chart>

21.   Remove the label ‘Active’ on the Status Axis by adding the Enabled attribute.

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

  <MajorTickMark LineColor=”165, 172, 181″ />

  <MajorGrid LineColor=”Transparent” />

  <LabelStyle Enabled=”false” Font=”{0}, 10.5px” ForeColor=”59, 59, 59″ />

</AxisX>

22.   Save the file and import the chart.

  • Go to Settings > Customizations > Customize the System.
  • On the left pane, select Components > Entities > Donations > Charts.
  • Click on the ‘More Actions’ > Import Chart and browse to the modified chart on your hard drive. Click OK.
  • Import will find that the chart is a duplicate. When asked, select ‘Replace’.

23.   Publish all the customizations.

24.   Open a Pledge entity.

Final result

Summary:

We have showed you how to manually modify CRM 2011 charts in order to:

  • Display the value of a field from the current form
  • Display the filtered value of the field from a related entity
  • Rename the legends’ labels

For more information on how to further modify the CRM 2011 charts, see also the blogs

 “CRM 2011 Charts – Know the Real Potential”, Part 1 and Part 2.

Christian J. Betrisey

 Here is the full chart XML that we modified in this example:

<visualization>

  <visualizationid>{AB00D3AF-49E9-E011-B89B-00155DA96A01}</visualizationid>

  <name>Pledge Status by Amount</name>

  <primaryentitytypecode>new_donation</primaryentitytypecode>

  <datadescription>

    <datadefinition>

      <fetchcollection>

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

          <entity name=”new_donation”>

            <link-entity name=”new_pledge” to=”new_pledgeid” from=”new_pledgeid” link-type=”outer”>

              <attribute alias=”aggregate_pledgeamount” name=”new_pledgeamount” aggregate=”avg” />

            </link-entity>

            <link-entity name=”new_donation” from=”new_donationid” to=”new_donationid” link-type=”outer”>

              <attribute alias=”aggregate_collected” name=”new_cashvalue” aggregate=”sum” />

              <filter>

                <condition attribute=”statuscode” operator=”eq” value=”100000001″ />

              </filter>

            </link-entity>

            <attribute groupby=”true” alias=”groupby_column” name=”statecode” />

          </entity>

        </fetch>

      </fetchcollection>

      <categorycollection>

        <category>

          <measurecollection>

            <measure alias=”aggregate_pledgeamount” />

          </measurecollection>

          <measurecollection>

            <measure alias=”aggregate_collected” />

          </measurecollection>

        </category>

      </categorycollection>

    </datadefinition>

  </datadescription>

  <presentationdescription>

    <Chart Palette=”None” PaletteCustomColors=”149,189,66; 197,56,52; 55,118,193; 117,82,160; 49,171,204; 255,136,35; 168,203,104; 209,98,96; 97,142,206; 142,116,178; 93,186,215; 255,155,83″>

      <Series>

        <Series Name=”o:new_pledgestatus,100000000″ ChartType=”Bar” IsValueShownAsLabel=”True” Font=”{0}, 9.5px” LabelForeColor=”59, 59, 59″ CustomProperties=”PointWidth=0.75, MaxPixelPointWidth=40, DrawingStyle=Cylinder”>

          <SmartLabelStyle Enabled=”True” />

        </Series>

        <Series Name=”o:new_pledgestatus,100000001″ ChartType=”Bar” IsValueShownAsLabel=”True” Font=”{0}, 9.5px” LabelForeColor=”59, 59, 59″ CustomProperties=”PointWidth=0.75, MaxPixelPointWidth=40, DrawingStyle=Cylinder”>

          <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″ 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”>

            <MajorTickMark LineColor=”165, 172, 181″ />

            <MajorGrid LineColor=”Transparent” />

            <LabelStyle Enabled=”false” Font=”{0}, 10.5px” ForeColor=”59, 59, 59″ />

          </AxisX>

        </ChartArea>

      </ChartAreas>

      <Titles>

        <Title Alignment=”TopLeft” DockingOffset=”-3″ Font=”{0}, 13px” ForeColor=”59, 59, 59″></Title>

      </Titles>

      <Legends>

        <Legend Alignment=”Center” LegendStyle=”Table” Docking=”right” IsEquallySpacedItems=”True” Font=”{0}, 11px” ShadowColor=”0, 0, 0, 0″ ForeColor=”59, 59, 59″ />

      </Legends>

    </Chart>

  </presentationdescription>

  <isdefault>false</isdefault>

</visualization>

 

 

 

CRM Software Free Trial – Your CRM online guide to Web-based Contact Management Software Free Internet Trials and the latest news, article, reviews & comparisons.



CRM Software Free Trial – Your CRM online guide to Web-based Contact Management Software Free Internet Trials and the latest news, article, reviews & comparisons.

Post Footer automatically generated by Add Post Footer Plugin for wordpress.

“We here at the “Web-Based Contact Management Software News, Reviews” are posting for your consumption the latest blog posts from the major Web-Based CRM Software vendors around the world who are focused on the development of products that enhance your CRM experience.” enjoy :)

——————————

Post Name:
CRM 2011 Charts – Charting on Related Records – Story From: Microsoft Dynamics CRM Team Blog
* If Needed – To Read the Full Story: (post’s here may be limited by vendor’s RSS feed) – Highlight above post’s blue title, right click and search with google.

Post Footer automatically generated by wp-posturl plugin for wordpress.

Listed Related posts:

  1. Following and Unfollowing Records in CRM - Story From: Microsoft Dynamics CRM Team Blog With the inclusion of Activity Feeds in the latest CRM update, we introduce the concept of “following” records. When you follow a record, you will see updates related to that record appear on your Personal...
  2. What’s new with Dialogs in Microsoft Dynamics CRM 2011 UR5 - Story From: Microsoft Dynamics CRM Team Blog Introduction Dialogs are the synchronous/interactive processes in Microsoft Dynamics CRM 2011 and Microsoft Dynamics CRM Online that collect and process information by using step-by-step scripts to direct users through a process. Dialogs have been enhanced...
  3. Multi Series Charts – A Quick Look - Story From: Webfortis Blog With the R7 update, you can create multi series charts easily.  What this means is you can have a chart showing up to five fields (y axis) against a single category field (x axis).  An example...
  4. Start 2011 Off By Helping To Save A Life. - Story From: Recent Ideas In early November our own John Greene (http://jgwillkickitsass.blogspot.com/) was diagnosed with Acute Myelogenous Leukemia (AML). He needs a bone marrow transplant to survive.  In an effort to help find a donor, salesforce.com is hosting a Be...
  5. Deep queries for subgrids - Story From: Microsoft Dynamics CRM Team Blog In This Post:• Problem statement• Sample definition• Adding the customizations• Modifications of the Customizations.xml• Conclusion• Further reading Problem statement Currently, Microsoft Dynamics CRM 2011 allows filtered views in sub-grids for the entities directly related to the opened entity. In Microsoft Dynamics for...
  6. Checklist of Essential Stuff When Building Solutions – A Tester’s Perspective - Story From: Microsoft Dynamics CRM Team Blog We as developers and CRM customizers always get excited of the thought of releasing a solution packed with wonderful features that would effectively serve the customer’s needs. When the solution gets handed off to the customer,...
  7. How to do Mentions with Activity Feeds - Story From: Microsoft Dynamics CRM Team Blog During a group discussion, sometimes we want to call the attention of a specific person. Social networking discussions are no different. In Microsoft Dynamics CRM Activity Feeds, we call this a “mention”. This blog covers the...
  8. Dynamics CRM 2011 Dashboards on Contact Form - Story From: Webfortis Blog Many of the industries in which work have been asking for an easy-to-consume interface that allows a CRM User to understand the value of a CRM Contact within a few moments. This post will focus on utilizing Dynamics...
  9. CRM 2011 Data Import Wizard: Importing Related Accounts and Contacts - Story From: Microsoft Dynamics CRM Online Team Blog If you’re new to CRM 2011 you may have imported a sample set of your organization’s data, only to run into the following gray areas: 1. When importing Accounts, how do you map and populate the...
  10. A Brief Introduction to CRM 2011 JavaScript - Story From: Webfortis Blog When CRM 2011 was rolled out, so was a new method of CRM JavaScript. Now available are libraries, solutions, web resources, etc. that you can use to attain fine-grained control over all of your customizations. With the introduction of...