The charts created and displayed in Dynamics CRM use the FetchXML syntax to query the underlying Dynamics CRM data source. Users can create comparison charts using the designer, but the comparisons are only based on one entity type. This recipe will create an Account SLA chart that shows how to manipulate the FetchXML
query to pull and compare data from two different entities (Account and Case entities).
This recipe assumes that the reader has reviewed the earlier recipes and is familiar with how to export and import a chart from Dynamics CRM. This recipe will require you to add custom fields to the Account entity and make sure there are some active Case records related to active Accounts.
Carry out the following steps in order to complete this recipe:
Property |
Value |
---|---|
Display Name |
Service Units SLA |
Name |
|
Requirement Level |
No Constraints |
Type |
Whole Number |
Property |
Value |
---|---|
View used for chart preview |
My Active Cases |
Name |
Account SLA Performance |
Chart Type |
Column |
Legend Entries (Series) |
Billed Service Units (Sum) |
Horizontal (Category) Axis Labels |
Customer |
<fetch>
segment near the top of the file:<fetch mapping="logical" aggregate="true"> <entity name="incident"> <attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="customerid" /> <attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="billedserviceunits" aggregate="sum" /> </entity> </fetch>
<fetch>
segment by adding a<link-entity>
condition that pulls in data from the Account entity's Service Unit SLA field, shown as follows:<fetch mapping="logical" aggregate="true">
<entity name="incident">
<attribute groupby="true" alias="_CRMAutoGen_groupby_column_Num_0" name="customerid" />
<attribute alias="_CRMAutoGen_aggregate_column_Num_0" name="billedserviceunits" aggregate="sum" />
<link-entity name="account" from="accountid" to="customerid" alias="ab">
<attribute name="new_serviceunitssla" alias="aggregate_column2" aggregate="max" />
</link-entity>
</entity>
</fetch>
FetchXML
, we can add it to the<measurecollection>
, so that it is included in the calculations:<category alias="_CRMAutoGen_groupby_column_Num_0">
FetchXMLusing, for data combination in chart<measurecollection>
<measure alias="_CRMAutoGen_aggregate_column_Num_0" />
</measurecollection>
<measurecollection>
<measure alias="aggregate_column2" />
</measurecollection>
</category>
<Series>
<Series ChartType="Column" IsValueShownAsLabel="True" Font="{0}, 9.5px" LabelForeColor="59, 59, 59" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" />
<Series ChartType="Line" CustomProperties="PointWidth=0.75, MaxPixelPointWidth=40" LabelForeColor="59, 59, 59" Font="{0}, 9.5px" BackGradientStyle="None" Color="255,0,0" IsValueShownAsLabel="False"/>
</Series>
This recipe covered some of the basics around using FetchXML
to specify what data is being combined in a chart. The following URL to the Microsoft MSDN library provides more detailed information along with examples on how to use FetchXML:
http://msdn.microsoft.com/en-us/library/gg328332.aspx
Learning all the syntax conditions around FetchXML
can be fun for some people. There is a much easier way to build FetchXML
queries in Dynamics CRM 2011 using the Advance Find query tool.
Launch the Advanced Find dialog in Dynamics CRM and build a query that pulls the data that you wish to see in your chart. This can also include data from multiple entities, similar to what we did earlier in this recipe. Once you have tested the query and found the results that you are looking for and edit the column layout to include the fields you need, click on the Download Fetch XML button in the Advanced Find ribbon toolbar, as shown in the following screenshot:
Clicking on that button will download a FetchXML
file that you can then use to replace or augment the FetchXML
query being used by your CRM chart:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="incident"> <attribute name="incidentid" /> <attribute name="billedserviceunits" /> <attribute name="customerid" /> <order attribute="billedserviceunits" descending="false" /> <filter type="and"> <condition attribute="ownerid" operator="eq-userid" /> <condition attribute="statecode" operator="eq" value="0" /> </filter> <link-entity name="account" from="accountid" to="customerid" alias="aa"> <attribute name="new_serviceunitssla" /> </link-entity> </entity> </fetch>
18.223.171.162