I have a free form report which is basically a company overview report.
The report looks something like this:
------------
Company XYZ
------------
- address
- telephone
-...
**Contacts**
- Bryan
- Caroline
- Patrick
**Complaints**
- Complaint 1
- Complaint 2
This report needs to be able to run for multiple company records at once, resulting in a nice print-out per company. I've currently solved this using a main report in which I have the main company selection by using:
select companyid from Company
This main reports has one table with a subreport in it. I provide the companyid as a parameter to the subreport.
In the subreport I then define the different datasets I need (e.g. Contacts, Complaints, ...) as follows.
- Contacts:
select contactid, name from Contact where companyid = @companyid - Orders:
select revenue from order where companyid = @companyid - ...
Keep in mind that these datasets are not related to eachother, they are only related to the company
However when running this report for multiple companies, this results in a horrible performance (I have 7 datasets in the subreport, and if the main report is ran for 50 accounts this results in (7+1)* 50 queries.
My question is: is it possible to eliminate the need for a subreport? I tried creating a dataset that is basically a combination of the datasets using unions and adding each dataset as a new set of columns to the main query. However I seem to be stuck on rendering this properly using lists with nested tables.
Are cached datasets a possible solution? If so, how exactly, as I can't seem to figure this out...
Many thanks!
