CRM 2011 Charts – Charting on Related Records - Microsoft Dynamics CRM Team Blog - Site Home

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:

</link-entity>

</filter>

</link-entity>

</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

</measurecollection>

</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>

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

</Legends>

</Chart>

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

</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