When working with Microsoft Dynamics CRM Online reports, you may encounter issues with reports not displaying properly. You might find that a sub-report cannot be displayed, a sub-report cannot be found, or there is an error rendering the report. The problem may be in the use of sub-reports. In this blog, we will cover:

  1. Common uses for sub-reports,
  2. Why to avoid sub-reports, and
  3. How to create reports without using sub-reports.

Common Uses for Sub-Reports

It’s common in Dynamics CRM to have a master detail record in a single form. This is known as a one-to-many relationship. In the example below, an account record shows many contacts associated with that account:

Microsoft Dynamics CRM Sub-reports

When building a custom FetchXML-based SSRS report with this type of data, most developers will gravitate towards displaying the account information in a report and passing the accounted to sub-report that displays the contact information. This may not be the best option for several reasons.

Why to Avoid Dynamics CRM Sub-Reports

Here are some reasons why sub-reports should be avoided as much as possible:

  1. They can have some performance issues with loading more slowly due to multiple reports.
  2. They can be difficult to manage. It’s easier to manage one report than to manage multiple reports. Each sub-report is a separate RDL (report definition file).
  3. With Dynamics CRM reporting, each sub-report should have a separate data source because Dynamics CRM does not support shared data sources.

How to Create a Master Detail Form Based Report without Using Sub-Reports

This blog assumes that the readers have knowledge on how to use FetchXML to develop custom SSRS report for Dynamics CRM. Our focus today is not to show how to create a FetchXM-based report, but to how show a technique that helps us build this report without a sub-report.

Also, we’ll be making this a “form-based” report, meaning this report can be run from an Account form by selecting the report from the ribbon (versus being run from the Reports area).

To get some background, you can view these resources:

Without further ado, here are the steps!

Step 1

Create a Dataset i.e FilterDS to pull accountid while enabling prefiltering. This will enable the report to grab the GUID of the account the report is run from.

Dynamics CRM Sub-report

Step 2

Create a parameter called account of datatype text.

Dynamics CRM Sub-report

Step 3

Right click the parameter account. Under properties, set the available and default value to point to the FilterDS and the accountid field as the value.

Dynamics CRM Sub-report

Step 4

Now create the main dataset (master dataset) for the account as you see below and apply the filter accountid equals the parameter @account.

Dynamics CRM Sub-report


Step 5

Also create the detail dataset for contact and filter on parentcustomerid as you see below.

Dynamics CRM Sub-report

Notice the @account parameter is key in both of these datasets. @account contains the ID (GUID) of the account record that the report is run for.

If you put everything together, it looks something like this:

Dynamics CRM Sub-report

This will take care of the need to use sub-reports while still using the awesome feature of Dynamics CRM called Report Parameter Pre-filtering.

Happy CRMing!

Avatar for Joe D365

Joe D365

Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.