Sub-Reports in Dynamics CRM and SSRS Run on Multiple Records
In Dynamics CRM, when we create a report in SSRS we can choose to add filtering so it can be run on a single record, or multiple records from CRM.
Say we want to create a custom invoice report, which contains our company logo, and some details about the invoice and line items. This will typically be a 1 page report, which would run on one invoice at a time.
Now say we need to print these invoices out every day, of which there could be upwards of one hundred invoices to run and print at once. Opening each invoice and running the report individually is tedious, and in an automated system like Dynamics CRM, there should be an easier way.
We can make the report available to run on multiple records, however because of the way it’s designed, it will only print one page. Additionally, the line items from all invoices will be displayed in the table, which is not right.
What we really need is the report to run separately for each invoice, but the only way to do this using a single report is to put everything in a repeating table. Unfortunately, we cannot put a repeating table inside a repeating table, so the line items cannot be added.
The other way we can do this is using a sub-report. How it works is the main report runs on multiple invoices, and has a repeating table with the sub-report in it, so each invoice gets a sub-report generated for it.
First we need to make a few minor adjustments to the original report query. Instead of getting all invoices and using CRMAF for filtering, we need to add a WHERE clause with a parameter for the invoiceid. Something like this (note this example is using SQL, but the same rules apply for FetchXML):
SELECT i.billto_composite, i.duedate, i.invoicenumber, i.totalamountlessfreight, i.totaltax, i.totalamount,i.customeridname, ISNULL(p.productdescription, p.productidname) AS productname, p.priceperunit, p.quantity, p.baseamount
FROM filteredinvoice i
left outer join FilteredInvoiceDetail p ON p.invoiceid = i.invoiceid
WHERE i.invoiceid = @CRM_InvoiceId
That will also create the parameter for us in the report.
Next we need to edit the parameter properties to make it hidden, and we should also specify a default value using the ID of an Invoice from our system so that we can preview the report.
We should then rename this report to include ‘Sub-Report’, and create another report in our project for the main report. The query for the main report should simply get the invoiceid and perform the pre-filtering:
select invoiceid from filteredinvoice as CRMAF_FilteredInvoice
For the body of the main report, we need to add a table with just one repeating cell. The table should use our only dataset, and should group by the invoiceid column.
Then we need to drag a Subreport item from the toolbox into the table cell.
Edit the subreport properties and select to use the CustomInvoice_SubReport as the subreport to use.
Then we need to pass in the InvoiceId as a parameter to the subreport, so click on Parameters from the subreport properties, and add the InvoiceId as a parameter. There should only be 1 parameter and 1 column to pick from.
One last thing I like to do is edit the group properties again and make sure Page Breaking is added between each instance of the group. This ensures that when the report is run, it will split each invoice onto its own page.
Finally resize the table/subreport item to fill the whole page. You might need to work out the sizes to make sure everything fits on one page horizontally.
NOTE: If you want to include a page header or footer, these will need to be added into the main report, not the sub-report.
Now you can upload the reports to CRM. First, upload the main/parent report, and allow it to run on forms and views for the Invoice entity.
Next upload the sub-report, which should automatically detect and prefill the Parent Report lookup. The sub-report should not display anyway, as the parent report will be the only one people run.
We can now run our Custom Invoice report from either the invoice form, which will only generate the report for the current invoice, or if we run the report from a view, we can choose to run it against all records in the view, selected records, or all applicable records.

When the report runs, in this case we can see 2 pages are generated, 1 for each of the invoices selected. We can then export to PDF or print directly from here to quickly and easily print all the invoices we need to at once.

And as it always has, if we run the report from a form, it will still only run for the current record.
*This post is locked for comments