Dynamics 365 CRM - Remove Outer Join Duplicates from SSRS Report

Question

I am facing an issue with SSRS reports in Dynamics 365. I have a custom entity named entity1, which has a 1:N relationship with the connections entity.

Each record in entity1 can have more than one connection to it and some records may have no connections at all.

I have created an SSRS, fetchXML report using entity1 as the primary entity and using outer join to link the connections, which works fine.

The report looks something like this :

**Name (entity1)**    |   **Stakeholder (Connection)**   |   **Role (Connection)**

Hotel Name 1          |           Joe Bloggs             |         Designer

Hotel Name 1          |           Jane Doe               |         Developer

Hotel Name 2          |                                  |         

Hotel Name 3          |           Joe Bloggs             |         Designer 

However, I want the report to look like this (there are a number of columns for entity1):

Hotel Name 1          |           Joe Bloggs             |         Designer

                      |           Jane Doe               |         Developer

Hotel Name 2          |                                  |         

Hotel Name 3          |           Joe Bloggs             |         Designer 

Is there a way to do this in the SSRS report builder/FetchXML statement or will I need to do something fancy in SQL?

My fetchXML looks like this:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="entity1">
    <attribute name="new_id" />
    <attribute name="new_name" />
    <attribute name="new_city" />
    <attribute name="ownerid" />
    <order attribute="new_name" descending="false" />
    <filter type="and">
      <condition attribute="new_city" operator="eq" value="London" />
    </filter>
    <link-entity name="connection" from="record1id" to="new_newbuildlistid" link-type="outer" alias="con">
    <attribute name="record2id" />
    <attribute name="record2roleid" />
    <attribute name="connectionid" />
    <attribute name="record1id" />
    <filter type="and">
      <condition attribute="record2id" operator="not-null" />
      <condition attribute="record2roleid" operator="not-null" />
    </filter>
    </link-entity>
  </entity>
</fetch>

Answer 1

There's not much you can do in the FetchXML for this. The Fetch is just (correctly) returning data, this question is all about presentation of that data.

You will need to implement a form of grouping with the report for this presentation. For example Create a Stepped Report.

To create a stepped report

1. Create a table report. For example, insert a tablix data region and add fields to the Data row.

2. Add a parent group to your report.