Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Dynamics CRM Sub-Report Not Displaying? Here's a Workaround

Post Author: Joe D365 |

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!

Joe CRM
By 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.

6 comments on “Dynamics CRM Sub-Report Not Displaying? Here's a Workaround”

  1. Thanks for the post.

    If I wanted the same report as above, but needed it to run from the Reports Area for all Accounts modified in the last 30 days, how would I manage to pass the account name from the first dataset to the second without using a subreport?

    I'm having no ends of fun (!?!) with a subreport at the moment - it works in BIDS but in CRM it simply displays no data (no errors - just no data) so if I can get around this by using 2 datasets I'd be overjoyed.

    1. Mike: The approach that you have taken is appropriate for your situation, since you are running this report on multiple records at a time and also filtering based on last modified date. I am not sure why it works in VS and not in CRM. Instead of passing name as parameter between main report and sub-report, can you try passing the ID like accountid. One reason why you are not seeing any data displayed in the report in CRM can be that you are designing the report to run from form ( form based report) but you are actually running it from reports area. In this situation, the report picks the first account from CRM which might not have contacts associated with it. That is why it is not giving you error and also not displaying any records in the sub-report.

      1. Hi Speedy,

        Thanks for the reply. I am passing through accountid as the parameter - found that was the only thing that worked in BIDS. The report is designed to work from the reports area - and it all works fine apart from the subreport part which will display the subreport column headers but no data when run from CRM.

        It's really frustrating me now as I need to get this report rolled out and I just can't seem to cracking this last issue.

        1. Hi Mike,

          I know this is a long shot since this is a really old thread.....any chance you ever figured out how to get your subreport to display? I'm having the exact same issue and have been racking my brain and scouring the internet trying to find a way to make it work.

  2. I'm struggling using this technique to make a quote report showing quote products.

    I have a parameter called Quote which links to the quoteid pulled in the first query as stated in the article, and have a filterDS, quote and products datasets setup as follows:

    filterDS

    Quote:

    Products:

    The issue I have is when the report is run from the quote form, it shows a selector drop down above displaying all the quoteid's which I have to use to select, so its not picking up the id from the form I'm in, also when run from the list view I don't get the usual 3 options of run on all/selected etc.
    Any ideas?

  3. I am sure, if it's issue or the requirement. when I run the report it's asking @account to select. I wanted something the report should display everything, not with the particular account selection

PowerObjects Recommends