Search: Microsoft+Dynamics+CRM+Live

As you may already know, we have introduced fetch based reports in Microsoft Dynamics CRM 2011. This means you can now upload powerful, custom fetch based reports even in Microsoft Dynamics CRM Online and CRM’s wizard reports are not the only available option. Other than fitting nicely into CRM Online scenarios, these reports are also a good candidate for On Premise deployments as they generally perform better when compared to an equivalent SQL report running against CRM’s Filtered Views.

You may read more about fetch based reports in the following blog posts:

• Getting Started with Custom Reports in the Cloud
• How To: Creating Custom Report with Microsoft Dynamics CRM 2011 BIDS Fetch Extension
• Custom Report Capabilities in Microsoft Dynamics CRM 2011

Reports, as we all know, are typically used to present business data in a more meaningful way to the end users so that they can derive quick insights from it. In order to increase the value a report provides, it usually takes some additional inputs or parameters at runtime and uses them to adapt the content or presentation. One of the common usage patterns of these parameters is to use them within the data sets defined in the report and control the data being retrieved from the data source. Today, I am going to talk about how you can parameterize fetch statements used in a fetch based report. I will start with a description of how query parameterization works for fetch based reports followed by an explanation on the elements that support parameterization.

Fetch based reports allow certain parts of a fetch xml to be parameterized. In other words, fetch based reports can be designed to receive parameter values at runtime which can then be used by the fetch statements defined in the report. We allow value of certain fetch xml nodes or attributes to be parameterized, and at runtime the received value of the parameter is replaced in the fetch xml before it is executed to get the results. Let us take an example and see how this works.

Consider the following fetch xml that allows me to show sum of estimated revenue for opportunities in “3 – Negotiating” stage for each customer:

<fetch mapping='logical' count='10' aggregate='true'>
   <entity name='opportunity'>
      <attribute name='estimatedvalue' aggregate='sum' alias='sum_estimatedvalue'/>
      <attribute name='customerid' groupby='true' alias='customerid'/>
      <order alias='sum_estimatedvalue' descending="true" />
      <filter type="and">
         <condition attribute="stepname" operator="eq" value="3 - Negotiating" />
      </filter>
   </entity>
</fetch>

This will work nicely for me. But if I want to slightly enhance my report so that the user viewing the report can choose which stage the opportunities should be in, I need a way to change the value of the condition dynamically at runtime. This is exactly what parameterized fetch xml of fetch based reports is meant for. However, for the complete thing to work for a report, I need to do the following:

1. Define a report parameter, say stage, in the RDL which will be shown to the user at runtime and will receive an input from her for the stage she is interested in.

<ReportParameter Name="stage">
          <DataType>String</DataType>
          <DefaultValue>
               <Values>
                 <Value>3 - Negotiating</Value>
               </Values>
          </DefaultValue>
          <Prompt>Opportunity Stage</Prompt>
     </ReportParameter>

2. Define a query parameter, say @stepname, in the RDL for the data set and set its value to the report parameter using the expression =Parameters!stage.Value. The prefix “@” is important and I will talk about it in a moment.

<QueryParameter Name="@stepname">

      <Value>=Parameters!stage.Value</Value>

</QueryParameter>

3. Modify the condition node of the fetch xml to use the query parameter as follows:

<condition attribute="stepname" operator="eq" value="@stepname" />

I have replaced the value of the condition with the query parameter @stepname. As I said before, the “@” prefix is important. It acts as a hint for CRM that @stepname could be a query parameter that may receive a value at runtime. I say it’s a hint and not a certainty, because a condition can have a value that starts with “@” but is not a query parameter. E.g. a condition that email address that ends with “@microsoft.com” is a valid condition but is not expecting a query parameter. At runtime, CRM will parse the fetch xml for potential parameters and if there’s a matching query parameter available it will simply replace the parameter in fetch xml with the value of the query parameter.

And I am done. My parameterized report is ready to work! When I execute the report, I will be prompted to enter an Opportunity Stage according to which the data will be filtered.

Seemed like a lot of work, isn’t it? So, let’s make it a little easier for you. When you author a fetch based report using the BIDS extension that CRM provides, we do a bunch of work automatically for you. You only need to provide the fetch xml with the parameter hint, i.e. a value with a “@” prefix, and we will automatically create a report parameter and a query parameter for you to complete the parameterization support. You can change the parameter settings or prompt text as per your needs. In case you do not want the parameter at all, you are free to remove it later.

Now when you know how query parameterization works in fetch based reports, it is time to understand what all elements of a fetch xml statement can be parameterized. The elements that can be parameterized fall into the following three categories:

1. Paging Elements

These elements are meant to support paged data retrieval. Often times, a report spans a large number of records but it only show a subset or a page of the complete data set at a given point in time. If you want a report that can fetch and show data in paged manner, then these elements can be parameterized to support that.

<fetch mapping=’logical’ page=’@page’ count=’@count’ paging-cookie=’@cookie’ >

Using parameters for these elements, it is possible to build reports that fetch data only for the page that the user is presently viewing and can significantly improve a report’s performance. I am going to talk about this in more detail in my future posts.

2. Condition Elements

These elements are meant to supply operand values dynamically to a condition element of a fetch xml statement. This is useful if you want to apply a condition based on some values that you receive at runtime. There are the following two elements that can be parameterized under this category:

a. Value Attribute: <condition attribute="stepname" operator="eq" value="@stepname" />

We have already seen an example of such a parameterization. In addition to what was already explained, you can also make the query parameter receive an array of multiple values. Let’s take our example forward to understand this better. I had defined a report that allowed users to provide an opportunity stage at runtime. If I want my users to provide multiple opportunity stages instead of just one, I also need to do make the report parameter stage a multi valued report parameter in addition to the other steps mentioned before. This can be done by modifying the parameter properties to “Allow multiple values” as shown below:

This will cause report viewer control to allow the user running the report to enter multiple values for the stage parameter and at runtime CRM will use those multiple values to form an array of values for the fetch xml condition.

b. Value Node

<condition attribute="stepname" operator="in" >

     <value>3 – Negotiating</value>

     <value>@stage</value>

</condition>

In my report described earlier, let’s say I want all opportunities in opportunity stage “3 – Negotiating” to be considered always. Additionally, I want my users to provide one additional stage to filter the data. This can be done using the xml fragment shown above. One of the several values of the condition value array is parameterized.

3. Pre-filtering Element

This element is used to apply a pre-filter to an entity of the fetch xml statement at runtime. CRM supports a notion of report pre-filtering using which you can filter a report’s content at runtime using an advanced find query. This provides reports a flexibility that they can be defined only once but can be rendered against a differently filtered data set as and when needed. Taking our example report yet another step forward, if I want my report to have all the previously discussed functionality and in addition to that I want my users to be able to provide another filtering criterion so that the data set can be filtered on an adhoc basis. E.g. a user running the report should be able to run the report only for the opportunity owned by her. Using report pre-filtering, I can make the report to prompt the user with an Advanced Find control before executing the report. Then whatever filtering criteria is entered by the user, it is taken as the base filtering criteria for the report and any filtering defined by the report are applied on top of the filtered data set.

Pre-filtering parameters work slightly differently than the rest of the parameters and need not be prefixed with “@”. These parameters are defined using custom xml attributes that are not part of the standard fetch xml schema and are defined by fetch based reports for proprietary usage.

<entity name=’opportunity’ enableprefiltering=’true’ prefilterparametername=’opportunityfilter’>

Fetch based reports uses two xml attributes, namely enableprefiltering and prefilterparametername, under entity or link-entity nodes of a fetch xml for setting a query up for pre-filtering. As soon as the CRM extension for BIDS detect a fetch xml having these two nodes, it will automatically create corresponding report and query parameters. When you upload such a report to CRM server, CRM will also detect this usage pattern and will automatically modify the report. It will make the report parameter for pre-filtering as a hidden parameter and will prompt the users running the report with an Advanced Find user interface. It will then capture the filtering criteria entered by the user and will pass it to the report which will be used to pre-filter the report data set. The xml attribute prefilterparametername is an optional xml attribute and if not provided, CRM will generate a parameter with a default name “CRM_<entityname>” where entity name is the name given by the name attribute of the entity node.

I will be covering pre-filtering in more detail in my future posts but this information should be good enough for building pre-filterable fetch based reports.

You can use any of the above described ways of parameterizing your fetch based reports and gain more out of them. I hope that this post was useful for understanding the parameterization support that we have built. Keep your questions coming and wait for the next edition.

Cheers,

Abhishek Agarwal