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 Product 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 Prodcuts 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.
1)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 id ,name WHERE filteredproducts P
INNER JOIN filteredcategory CP on CP.productid=P.ID
WHERE P.ID=@ID
2)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 Products 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 id and perform the pre-filtering:
SELECT id FROM filteredProducts as CRMAF_FilteredProducts
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 id column.
3)Then we need to drag a Subreport item from the toolbox into the table cell.
4)Then we need to pass in the Id as a parameter to the subreport, so click on Parameters from the subreport properties, and add the Id as a parameter. There should only be 1 parameter and 1 column to pick from.
5)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 Products onto its own page.
6)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 Product entity.
Hope it's help you...
