In Microsoft Dynamics CRM 2011, we have introduced Fetch-based custom reports that can be created and run in both On-Premise and On-Line environment. This has fulfilled one of the top ask for Microsoft Dynamics CRM Online. Fetch xml provides a secure, performant way to retrieve data from the CRM. These reports uses data processing extension component of SQL Server Reporting Services to execute report. It enables us to connect to CRM platform and retrieve the data. Refer to Abhijit Gore’s blog to know more about newly introduced fetch-xml based custom reports in Microsoft Dynamics CRM 2011.
I will discuss the different reporting capability available in the custom reports. The first post of this series will discuss how different attribute types are handled in the Fetch-based custom reports. A report writer writing report will use “Raw Value” fields for the computational purposes like aggregation, grouping…; and “Formatted Value” fields for the display purposes. The result returned from querying fetch xml in CRM contains both raw and formatted value. These formatted values are as per the user settings in CRM. The table below illustrates some of the example of raw and formatted value in CRM.
Data processing extension for CRM Reports also supports access to both raw and formatted value of an attribute by creating separate field in the data set corresponding to each value. Some special types like “Lookup” and “Picklist” also support additional fields (A complete list for all the supported types is at the end of this post).
Using Report Authoring Extension for BIDS to create Report Data set:
The following example create a report that shows the opportunity name, estimated revenue (in base), potential customer and the total estimated base revenue of opportunities. The display value in the table should be formatted; however, the value used for aggregation, sorting and grouping should be done on raw values.
1. Start Business Intelligence Development Studio “BIDS” and create a new report which uses “Microsoft Dynamic CRM Fetch” Data source type.
2. Enter the following fetch xml in the Query string. Specify the required attribute in the entity node of the fetch xml.
3. Click next and complete the report wizard.
4. The report wizard automatically populates the entire permissible field in the dataset. Generally, more than one field is generated for a single attribute in the fetch xml. The fields appended with “Value” text denote the raw value of the attribute. There are three different field generated for attribute “customerid” in the fetch xml.
5. Modify the report by adding the necessary columns in the table. Since these columns will be just used to display purposes, we will use fields that correspond to the formatted value.
Est. Close Date
6. For aggregation, we will use the raw value of the attribute. Field “estimatedvalue_baseValue” denote the raw value of attribute “estimatedvalue_base”. Aggregate can be obtained using expression:
Note: The aggregated value displayed here will not be a formatted value. To format the aggregate value report writer can use the culture information passed during report execution as parameter. I will explain this part in the later post.
7. At runtime, Data processing extension for CRM Reports will use the fetch xml to query the CRM platform and get the entity result collection. It then internally parses the result collection and returns the result for different field in data set.
A sample DataSet
A dataset contain separate field entry for the different value in attribute.
“Name” is the name to be used for the field in the report.
“DataField” is the name of field (viz. is logical name of the attribute) in the fetch xml. To access the formatted value, use logical name in the data field. To access the raw value, use logical name + “Value” suffix in the data field.
<Field Name="estimatedvalue_base"> <!--Formmatted value of the estimatedvalue_base attribute-->
<Field Name="estimatedvalue_baseValue"> <!--Raw value of the estimatedvalue_base attribute-->
<Field Name="estimatedclosedate"> <!--Formmatted value of the estimatedclosedate attribute-->
<Field Name="estimatedclosedateValue"> <!--Raw value of the estimatedclosedate attribute-->
<Field Name="customerid"> <!--Formmatted value of the customerid attribute-->
<Field Name="customeridValue"> <!--Raw value of the customerid attribute-->
<Field Name="customeridEntityName"> <!--Entity Name of the customerid attribute-->
<Field Name="opportunityid"> <!--Formmatted value of the estimatedvalue attribute.-->
<CommandText><fetch version="1.0" output-format="xml-platform" mapping="logical">
<attribute name="name" />
<attribute name="estimatedvalue_base" />
<attribute name="estimatedclosedate" />
<attribute name="customerid" />
<attribute name="opportunityid" />
<order attribute="estimatedclosedate" descending="false" />
<condition attribute="ownerid" operator="eq-userid" />
<condition attribute="statecode" operator="eq" value="0" />
List of Attribute types and their supported values:
The following table illustrates the different attribute types and the different fields available for them (raw, formatted). Normally, “logical name of the attribute” in DataField returns the formatted value and logical name + “Value” suffix returns the raw value. However some data types like “Lookup”, “Customer”, “Owner” and “Picklist” provides access to some additional fields.
Following is the sample result set for different data types:
We just discussed how we can access the raw and formatted value of an attribute in fetch based custom reports. I hope this post will help you in authoring and designing the custom fetch based reports. Please let us know what would you like to see about Custom fetch based report in the future posts.
Note that clicking on a picture usually will enlarge it so that it is easier to read. Cheers.
Nice write up Kunal. How do we use the new FetchXML to report all the CRM Contacts that doesn't have any CRM Activities? What is the best approach for this?
How do we use Report Parameters so that we run reports against specific entities? For example, a new Quote report.
Unfortunately, the scenario you are looking is currently not achievable using a fetch xml.
It can be achieved in reporting by creating 2 data sets. In which one data set you can retrieve the contact with activities and then filter out these contact in the other data set. I know this is a lot to do, but due to the limitation of fetch xml we can't do it in a simple way.
I didn't get your question. Did you meant you want to create a report against a particular entity? In that case you would have to create a fetch xml for that entity.
Let me know if you meant something else.