How to create a subreport to calculate a distinct total in Microsoft Dynamics CRM Online using SSRS BIDS Report

When writing reports against data for Microsoft Dynamics CRM Online, I am often faced with limitations of writing dataset queries using Fetch XML. One such limitation I recently faced was trying to get the total value for a set of records.  Normally totals are not an issue. The thing that made this situation difficult was that I was linking to a subset of records having a many-to-one relationship.  This meant the value that I wanted to sum in the report was being duplicated any time multiple child records were found.

In this blog, I am assuming knowledge of writing FetchXML based SSRS reports using BIDS, including how to create and use subreports.  Due to the sensitive nature of my client’s data, I will not be using screenshots or the actual queries used.  I will instead display sample FetchXML and illustrative tables.

The FetchXML for my report looks similar to this:

Picture the data using the amount columns looking something like this:

Picture the data using the amount columns looking something like this:

The desired result in my report was to display the Payment Amounts on a single row, the month representing the column headers.  My expected results would look something like this:

Instead of seeing 4500 for my total, I was getting 10500.  This was confusing until I realized it was due to the Loan Amount being duplicated for every payment.

I was able to get around this was by creating a subreport in BIDS that calculated only the total for Loans.  The dataset for the subreport was copied from the main report; however, I removed the link in the FetchXML to the child records.

The subreport FetchXML looks similar to this:

I placed the subreport in my main report where the total had once displayed incorrectly.  The design view of the main report’s table would look similar to this:

If you are looking for support for Microsoft Dynamics CRM, RSM offers a full range of services from implementation and optimization to development and support. We can help install updates or make configuration changes, contact our professionals for more information on our services at 855.437.7202 or via email at crm@rsmus.com.

By: Jon Angell – Minnesota Microsoft Dynamics CRM Partner

Jon Angell