Custom Report Capabilities in Microsoft Dynamics CRM 2011

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.

• DataField “customerid” returns the display name of the customer.
• DataField “customeridValue” returns the raw value of the customer.
• DataField “customeridEntityName” returns the entity, customer belongs to.

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.

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:

                         =Sum(Fields!estimatedvalue_baseValue.Value).

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.

   <DataSet Name="DataSet1">
      <Fields>
        <Field Name="name">
          <DataField>name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="estimatedvalue_base">  <!--Formmatted value of the estimatedvalue_base attribute-->
          <DataField>estimatedvalue_base</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="estimatedvalue_baseValue">  <!--Raw value of the estimatedvalue_base attribute-->
          <DataField>estimatedvalue_baseValue</DataField>
          <rd:TypeName>System.Decimal</rd:TypeName>
        </Field>
        <Field Name="estimatedclosedate"> <!--Formmatted value of the estimatedclosedate attribute-->
          <DataField>estimatedclosedate</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="estimatedclosedateValue">  <!--Raw value of the estimatedclosedate attribute-->
          <DataField>estimatedclosedateValue</DataField>
          <rd:TypeName>System.DateTime</rd:TypeName>
        </Field>
        <Field Name="customerid"> <!--Formmatted value of the customerid attribute-->
          <DataField>customerid</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="customeridValue"> <!--Raw value of the customerid attribute-->
          <DataField>customeridValue</DataField>
          <rd:TypeName>System.Guid</rd:TypeName>
        </Field>
        <Field Name="customeridEntityName"> <!--Entity Name of the customerid attribute-->
          <DataField>customeridEntityName</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="opportunityid"> <!--Formmatted value of the estimatedvalue attribute.-->
          <DataField>opportunityid</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
      <Query>
        <DataSourceName>DataSource1</DataSourceName>
        <CommandText>&lt;fetch version="1.0" output-format="xml-platform" mapping="logical"&gt;
          &lt;entity name="opportunity"&gt;
            &lt;attribute name="name" /&gt;
            &lt;attribute name="estimatedvalue_base" /&gt;
            &lt;attribute name="estimatedclosedate" /&gt; 
            &lt;attribute name="customerid" /&gt;
            &lt;attribute name="opportunityid" /&gt;
     &lt;order attribute="estimatedclosedate" descending="false" /&gt;
     &lt;filter type="and"&gt;
              &lt;condition attribute="ownerid" operator="eq-userid" /&gt;
              &lt;condition attribute="statecode" operator="eq" value="0" /&gt;
            &lt;/filter&gt;
          &lt;/entity&gt;
        &lt;/fetch&gt;</CommandText>
        <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
    </DataSet>

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.

Cheers,

Kunal Garg