SSRS report - Display 1 to Many relationships

Question

I am trying to build a custom SSRS report and publish to our CRM system. I have a Dataset created using FetchXml by connecting to Microsoft Dynamics CRM 365. The data set has 1 to Many and Many to Many relationships. The query looks fine and I am able to preview the Dataset results in Visual Studio. I am able to display the primary entity fields in the report but failed to show the 1 to Many relationship values. Please suggest on how to get this wprking. My fetch xml looks like this

<?xml version="1.0" encoding="UTF-8"?>
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
   <entity name="entity1">
      <attribute name="name"/>
      <link-entity name="entity2" from="entity2id" to="entity1id">
         <attribute name="name"/>
      </link-entity>
   </entity>
</fetch>

How do you reference the entity2 name attribute in the report? You could introduce alias for the linked entity attribute to make it more "clear". Also the link-entity element looks a bit fishy, as it joins entity2id to entity1id, which are most likely never equal. — Jonas Rapp
I am just dragging and dropping the fields from the dataset on to the report. If I open the actual expression of the entity2 name, it shows =Fields!entity29bd2f473_name.Value where as the entity1 name shows =Fields!name.Value @Jonas Rapp — Rohith
use fetchxml tester from xrmtoolbox to build the query & test or use advanced find.. — Arun Vinoth - MVP

Arun Vinoth - MVP Arun Vinoth - MVP · Accepted Answer · 2017-08-02T14:51:23

alias & link-type is missing in your query syntax.

<link-entity name="entity2" from="entity2id" to="entity1id" alias="xyz" link-type="inner" >

On a side note, I recommend you to use Xrmtoolbox FetchXML builder to build the query without errors.

SSRS report - Display 1 to Many relationships

1 Answers