Fetch XML based SSRS report in Microsoft Dynamics CRM Online/On-premises is an important and interesting topic to learn about. Here, in this post, I am sharing some of my rudimentary understanding, examples for some complex development scenarios , and limitations of Microsoft Dynamics CRM Fetch XML based report.
Rudimentary understanding
The very basic thing that needs to be kept in mind is that unlike Dynamics CRM on-premises, you cannot write SQL queries against filtered views for Dynamics CRM online, because an access to the SQL database is not permissible here. Hence, the only option to develop SSRS report in CRM online is to write Fetch XML for retrieving the data. These fetch XMLs can be generated using Advanced Find. The Advanced Find helps us to write query to fetch data from existing CRM instance, save the results as a user owned saved view or as an organization owned view, and download the Fetch XML for this custom view. The Advanced Find utilizes the out of the box security structure to fetch the data. This is a good starting point for developers to get Fetch XML for such type of SSRS reports.
The Fetch XML language is an exclusive query language that is used in Microsoft Dynamics CRM and it supports similar query abilities as a SQL query expression. From CRM online, when we run the Fetch Xml based SSRS report, the query is sent to the web server to retrieve data from CRM database. This permits only SSL connections to the web server and protect data over the wire in the case of IFD and CRM Online deployments.
The below Fetch XML query is fetching records for Account entity. This query will return only active Account records with columns as Account Name, Primary Contact, and Home Phone. It will sort the result set based on Account Name and Home Phone fields.
A simple example of Fetch XML query.
<fetch
version="1.0"
output-format="xml-platform"
mapping="logical"
distinct="false"
>
<entity
name="account"
>
<attribute
name="name"
alias="Account_Name"
/>
<attribute
name="primarycontactid"
alias="Primary_Contact"
/>
<attribute
name="telephone1"
alias="Home_Phone"
/>
<attribute
name="accountid"
alias="AccountId"
/>
<order
attribute="name"
descending="false"
/>
<order
attribute="telephone1"
descending="false"
/>
<filter
type="and"
>
<condition
attribute="statecode"
operator="eq"
value="0"
/>
</filter>
</entity>
</fetch>
How to create Fetch XML based report.
I’m not going to cover this topic in detail here in this post as it is for the developers who know the fundamentals about creating Fetch XML based report. Although, a reader who doesn’t know how to create Fetch Xml based report in SSRS for Dynamics CRM, the below links would be a good starting point for them to begin their journey.
Some complex development scenarios
Adding Parameters to a Fetch XML Based report:
Adding/Passing Parameters mean passing values to the queries. Passing parameters to fetch xml reports is same as we pass parameters to SQL query. The advantage of passing parameter(s) is that their values will be set at run time, which can then be used by the fetch statements defined in the report. At run time the received value of the parameter is replaced in the fetch xml before it is executed to get the results.
The parameter name has to be started with “@” and it’s value is populated at run time. Let’s take an example and see how this works.
Consider the following fetch xml that shows sum of estimated revenue for opportunities in “ABC” 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="ABC" />
</filter>
</entity>
</fetch>
Now, let’s make the same fetch xml query as Parameterized fetch xml:
1. Define a report parameter, in our case it is a ‘stage’. This parameter will be shown to the user at runtime and user can insert a value to filter the report data.
<ReportParameter Name="stage">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ABC</Value>
</Values>
</DefaultValue>
<Prompt>Opportunity Stage</Prompt>
</ReportParameter>
2. When we use a parameter @stepname in our fetch xml, the SSRS internally defines it as mentioned below.
<QueryParameter Name="@stepname">
<Value>=Parameters!stage.Value</Value>
</QueryParameter>
3. Modify the
condition node
i.e.
<condition
attribute="stepname"
operator="eq"
value="ABC"
/>
in aforementioned fetch xml to use the query parameter, as specified
follows:
<condition
attribute="stepname"
operator="eq"
value="@stepname"
/>
That’s it, the 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.
Pre-filtering in Fetch based
reports
One of the most useful features of
Reports in CRM is to allow reports content to be filtered at run time
using Advanced Find feature. The Pre-filtering allows users to create
context specific report.
E.g. A user who is
running the report, should be able to run the report only for the
opportunities owned by him. Using report pre-filtering, one 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.
All you need to do is to specify an
enableprefiltering
attribute in the entity node of your fetch query. Please know, the
prefilterparametername
attribute is not a mandatory attribute.
If you are manually
modifying a Fetch-based report definition without using the Report
Wizard in the Microsoft Dynamics CRM Web application or SQL Server
Data Tools to enable pre-filtering for primary and linked entities,
make sure that you:
1. Set
the value of the
enableprefiltering
parameter
to 1 for the required entity or linked entity, And specify a unique
parameter name for the
prefilterparametername
property.
as specfied below
<fetch
distinct="false"
mapping="logical">
<entity
name="account"
enableprefiltering="1"
prefilterparametername="CRM_FilteredAccount">
2.
Create a corresponding query parameter with the same name as
specified for the
prefilterparametername
property.
Make sure that the parameter name starts with "CRM_" and
specify it as a hidden parameter.
<QueryParameters>
<QueryParameter
Name="CRM_FilteredAccount">
<Value>=Parameters!CRM_FilteredAccount.Value</Value>
</QueryParameter>
</QueryParameters>
3.
Create a corresponding report parameter with the same name.
<ReportParameters>
<ReportParameter
Name="CRM_FilteredAccount">
<DataType>String</DataType>
<Prompt>
CRM
Filtered Account
</Prompt>
</ReportParameter>
</ReportParameters>
Multiple Datasets and Multivalued
Parameter in Fetch based reports
Usually, when we need to use the result
of one dataset to another, we would prefer to have multiple datasets
in Fetch Xml based reports. Here, the multi-valued result set could
be passed to a second dataset as a parameter to
filter the record set for the
second dataset.
Sometimes, we have a requirement like we
have to select some multiple values in report filter criteria and
need to display the records based on the selected values, like we
have a filter of Cities (here user can select one
or more cities for the report parameter) and we need to display the
records based on the selected cities. At this time, we use the
multivalued parameter.
The concept is that, in the multi-value
parameter we set the data source that
holds the cities and we use this
parameter directly into
our report’s
main query.
Example: We’ve
created a report that displays
accounts which match the specified cities.
Dataset
1
<DataSet
Name="DSCities">
<Query>
<DataSourceName>FetchDataSource</DataSourceName>
<CommandText>
<fetch
>
<entity
name="new_city"
>
<attribute
name="new_cityid"
/>
<attribute
name="new_name"
/>
</entity>
</fetch>
</CommandText>
</Query>
</DataSet>
Dataset
2
<DataSet
Name="DSAccounts">
<Query>
<
DataSourceName>FetchDataSource</DataSourceName>
<CommandText>
<fetch>
<entity
name="account">
<attribute
name="name"/>
<filter>
<condition
attribute="new_cityid"
operator="in"
value="@Cities"/>
</filter>
</entity>
</fetch>
</CommandText>
<QueryParameters>
<QueryParameter
Name="@Cities">
<Value>=Parameters!Cities.Value</Value>
</QueryParameter>
</QueryParameters>
</Query>
</DataSet>
Multi-valued parameter
<ReportParameter
Name="Cities">
<MultiValue>true</MultiValue>
<Hidden>true</Hidden>
<DefaultValue>
<DataSetReference>
<DataSetName>DSCities</DataSetName>
<ValueField>new_cityid</ValueField>
</DataSetReference>
</DefaultValue>
</ReportParameter>
Limitations
-
Fetch XML does not support RIGHT OUTER JOIN and FULL OUTER JOIN .
-
Fetch XML does not support EXISTS condition with sub-query/expression.
-
You cannot compare two fields directly. For instance, you won't be able to find an equivalent query for the following SQL script:
SELECT * FROM account
WHERE telephone1 <>
telephone2
-
When you execute a Fetch XML query, the maximum number of records you get back from CRM server is 5,000.
-
No keyword like UNION, the following thing cannot be achieved in Fetch XML.Select Name from account Union ALL Select FullName from contact
-
There is no support of a CASE / WHEN structure.
-
You canno t have more than 10 linked entities in a Fetch XML query.
-
Fetch XML based reports cannot use non-CRM online data sources .
-
When you have more than one dataset the pre-filtering will be applied only on the first dataset.
Tip:
In
fetch xml when you would not like to consider the time part while
comparing the date value then prefer to use
“
on
”
instead
of
“eq”.
I hope this article
would be useful for developing Fetch XML based reports. Please feel
free to update your views about this post and any additional points,
which could be considered in relevance for this post.
hi Ankit Shah. Excellent post!
ReplyDelete