In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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:
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:
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.
Here are some reasons why sub-reports should be avoided as much as possible:
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!
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.
Create a parameter called account of datatype text.
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.
Now create the main dataset (master dataset) for the account as you see below and apply the filter accountid equals the parameter @account.
Also create the detail dataset for contact and filter on parentcustomerid as you see below.
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:
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!
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.
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.
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.
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.
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?
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