FetchXML Sub queries

(0) Share

Report

Posted on by Community Member

Hi,

I dont write reports, and we have an on premise installation at the moment that one of the people who does write reports is using some tool to publish reports he made in SQL.

These reports have sub queries and some login applied to some fields.

As a company we are planning on moving to crm online, obviously this means reports will have to be re-written in fetchxml so my question is - is it possible to do something like sub queries in fetchxml as i understand one of the limitation is that it cant handle them?

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

a33ik 84,331 Most Valuable Professional on at

Like (0)

Report

There is no way doing subqueries in FetchXml reports so you will have to prepare data on CRM side before building of a report and use prepared data without subqueries.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

how do you mean prepare data?

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

Hi,

You can convert your sql query to fetch xml online. please refer the below link :

http://www.sql2fetchxml.com/

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi,

You should consider re-engineering your current SSRS to include input parameters:

http://blogs.msdn.com/b/crm/archive/2011/03/18/parameterizing-fetch-based-reports.aspx

FetchXml offers all basic SQL functionality, so 99% of scenarios should be achievable, and once you have your data set ready, SSRS functions allows you to farther filter/manipulate data.

Thanks,

Piotr

Was this reply helpful? Yes No
Verified answer

a33ik 84,331 Most Valuable Professional on at

Like (0)

Report

For example you have report with following SQL query:

Select

a.Name

,(Select Count(*) From FilteredContact c where c.parentcustomerid = a.accountid) ContactsCount

From FilteredAccount a

You can't convert it directly using FetchXml statement. So preparation in this case is creation of int field in account entity and population of it based on number of child contacts (this can be done through rollup field or simple field and plugin that handle operation with child contacts).

Was this reply helpful? Yes No
suresh maurya 630 on at

Like (0)

Report

You can test FetchXML online http://msxrmtools.com

Was this reply helpful? Yes No
suresh maurya 630 on at

Like (0)

Report

For testing of FetchXML, you can test here online http://msxrmtools.com

Was this reply helpful? Yes No