Microsoft Dynamics CRM Team Blog: Fetch-Xml based Reports: Bits & Pieces
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>
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>
(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
FetchDataSource <fetch> <entity name="contact"> <attribute name="fullname" alias="name" /> </entity> </fetch> FetchDataSource <fetch> <entity name="account"> <attribute name="name"/> </entity> </fetch> ataSet>
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
(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.
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
|Microsoft Dynamics CRM Team Blog: Windows Azure AppFabric Integration with Microsoft Dynamics CRM - Step By Step||Blog bot||Dynamics CRM: Blogs||0||18.02.2011 23:12|
|Microsoft Dynamics CRM Team Blog: Driving Success with the New Microsoft Dynamics Marketplace!||Blog bot||Dynamics CRM: Blogs||0||28.01.2011 00:12|
|Microsoft Dynamics CRM Team Blog: Cloud-Based Version of Microsoft Dynamics CRM 2011 Now Available Worldwide||Blog bot||Dynamics CRM: Blogs||3||18.01.2011 03:13|
|Microsoft Dynamics CRM Team Blog: Including External Data in Microsoft Dynamics CRM 4.0 Reports||Blog bot||Dynamics CRM: Blogs||0||12.06.2009 04:31|
|Microsoft Dynamics CRM Team Blog: Microsoft Dynamics CRM 4.0 Bookshelf||Blog bot||Dynamics CRM: Blogs||1||22.01.2009 04:46|
|Опции темы||Поиск в этой теме|