Microsoft Dynamics CRM Team Blog: Fetch-Xml based Reports: Bits & Pieces

[Blog bot]

Источник: http://blogs.msdn.com/b/crm/archive/...mp-pieces.aspx
==============

With Fetch-xml based reports in Microsoft Dynamics CRM 2011, users can now create custom reports in Microsoft CRM Online. The CRM Report Authoring extension for BIDS provides a rich authoring experience for creating such custom reports. Abhijit Gore has a great introduction post on authoring and updating fetch based report using BIDS. In this post, I will detail other features of fetch-xml based reports like Adding Parameters, Pre-filtering that will help you create better and effective reports.

1. Adding Parameters to a Fetch Based report:

Just like parameters in SQL queries, one can have parameters in Fetch-xml queries too. The advantage of having parameters is that their values can be set at runtime by the end user.
For example, you want to show all opportunities with Estimated Revenue greater than some value specified by the user. If you were creating the Report using BIDS with the CRM Fetch extension, you would write the following fetch-xml in the Query-Builder:



Parameter names need to start with “@” and their value is populated at runtime. The above query returns opportunities with EstimatedValue greater than the value of the parameter @MinEstimatedValue. Adding the parameter in the fetch query also adds the following QueryParameter and ReportParameter nodes in the RDL.

=Parameters!MinEstimatedRevenue.Value ryParameters>   rtParameters> String Min Estimated Revenue ortParameters>

At runtime, the text from the “Prompt” node in the ReportParameter is displayed to the end user. The parameter value entered by the user is then used to populate the QueryParameter.



2. Pre-filtering in Fetch based reports:

One of the most useful features of Reports in CRM was to allow reports’ data to be filtered using Advanced Find functionality. Pre-filtering allows users to create context sensitive report - this post from Dana explains how pre-filters can be used for SQL based reports. But how do you add pre-filters to a fetch query? Well, it is fairly easy. All you need to do is specify an “enableprefiltering” attribute in the entity node of your fetch query. (The “prefilterparametername” attribute is optional):

 

As with parameters before, adding a pre-filter parameter in the fetch-query also requires adding a QueryParameter and ReportParameter node in the RDL.

=Parameters!OpportunityFilter.Value =Parameters!AccountFilter.Value   String Opportunity Filter String Account Filter rtParameters>

Once the report is uploaded into CRM and Run an Advanced Find dialog opens up. The pre-filter parameters are populated with fetch-xmls generated from this Advanced Find dialog.

(In BIDS, the pre-filter parameter will be visible as any another string value parameter. The expected input is a fetch-xml with filter conditions. Leaving the parameter value blank when prompted defaults it to a redundant filter - a fetch-xml that returns all attributes for all entity records).




3. Union Queries in Fetch based reports

The UNION operator in SQL allows you to combine two or more select statements and return the result as part of a single DataSet. The current fetch-xml schema does not have an operator that allows a similar UNION functionality. Instead, the workaround is to retrieve the results as part of different data sets.
Suppose you want names of all accounts and contacts in you organization. The SQL Query in your RDL would be:

… SqlDataSource select fullname as name from FilteredContact UNION select name from FilteredAccount

In a fetch based report, you would instead have two data sets one corresponding to each of the above select statements but using the same data source.

  FetchDataSource <fetch> <entity name="contact"> <attribute name="fullname" alias="name" /> </entity> </fetch>   FetchDataSource <fetch> <entity name="account"> <attribute name="name"/> </entity> </fetch> ataSet>

4. Multiple Datasets and Multivalued Parameter in Fetch based reports

Reports with multiple datasets would typically require utilizing results of one dataset in another dataset’s query. Let’s take the following example:
We want to generate a report that displays the top-15 accounts sorted by revenue and a 16th row that displays the total revenue for the rest of the accounts. We would again require two datasets – Dataset1 retrieves the top 15 records ordered by revenue while Dataset2 retrieves the TotalRevenue aggregating over all accounts except the ones from DataSet1.

FetchDataSource <fetch count="15"> <entity name="account" > <attribute name="accountid" /> <attribute name="name" /> <attribute name="revenue" /> <order attribute="revenue" descending="true" /> </entity> </fetch> …   FetchDataSource <fetch aggregate="true"> <entity name="account"> <attribute name="revenue" aggregate="sum" alias="TotalRevenue" /> <filter> <condition attribute="accountid" operator="not-in" value="@TopAccountIds"/> </filter> </entity> </fetch> =Parameters!TopAccountIds.Value …   … true true DataSet1 accountid

The Top 15 AccountIds retrieved from Dataset1 are taken in a multi-valued parameter called “TopAccountIds”. In the fetch-xml query in Dataset2, these parameter values are used as a filter to exclude accounts selected in Dataset1.

(Please note that the above multivalued parameter usage with fetch-xml will only work with the Microsoft Dynamics CRM 2011 RTM or later bits)

I hope that the above discussion helps you in authoring reports with parameters and pre-filters. In case you are experiencing issues with Microsoft Dynamics CRM 2011 Beta, please leverage the CRM Dev Forums.

Please let us know, via comments below, what topics you would like to see about CRM-Reporting in future blogs.

Cheers,

-Ankit Malpani







Источник: http://blogs.msdn.com/b/crm/archive/...mp-pieces.aspx