Joining more than 2 datasets in SSRS?

1
votes

I am creating a report from MS CRM, I need to show data from 3 different tables. I can join 2 datasets using lookup but I don't know how to show fields from 3 datasets?

Datasource is CRM > Fetch XML We are not using SQL query so can't join tables in one query. If the solution is multilookup, then please let me know the way to write multilookup OR is there any other way?

Second problem is: when I am creating a report with report wizard, I am able to integrate the report in MS CRM but when i am creating the report without wizard, CRM shows the below error:

This report type is not supported. Microsoft Dynamics 365 Online supports only reports that use Fetch XML data sources. If you contact support, please provide the technical details.

1
reporting-servicesdynamics-crmssrs-2012dynamics-crm-onlinefetchxml
The only way I have benn able to get data from three datasets was to first use a lookup and then use a second lookup (in another column) using the reportitem of the previous column. – C Bell

1 Answers

0
votes

Other way: To perform inner join in FetchXML query & set the following attributes in link-entity clause.

1.set name attribute to logical name of related entity

2.set to attribute to linking attribute in primary entity

3.set from attribute to linking attribute in related entity

4.set link-type attribute to inner

5.within link-entity clause specify attributes from related entity to retrieve.

      <link-entity name='account'  to='parentcustomerid' from='accountid' link-type='inner'>

Ref: http://blog.msxrmtools.com/2016/04/inner-join-in-fetchxml-fetchxml-query.html?m=1