I have report with a table and two nested tables in the footer of the main table. The report is providing sales information for different retail outlets. The idea being that the main table summarises information about the retail outlet and the two nested tables show details of current retail activity and future retail activity for the retail outlet identified in the main report.
The table is set up as follows:
Report Header
Report Body
Parent Table
Table Group (repeats and new line after group, grouping on a dataset field value)
Table Footer Row 1 - nested table 1 (filtered on date parameters)
Table Footer Row 2 - blank row (to allow spacing between nested tables
Table Footer Row 3 - nested table 2 (filtered on date parameters)
Report Footer
There is no detail row in the parent table.
Users select a variety of parameters to choose the retail outlets for review and can choose one, more or all of the outlets.
I am using the same dataset for all data regions (there is no need to really split up the datasets as the only difference between the current and future sales figures is the date they occur, all other fields are the same so there's no data redundancy by combining the queries).
When I run the report for a single retail outlet all works perfectly. However, when I try to choose multiple retail outlets, the information for all outlets is combined and repeated on each page, i.e. I am grouping the main table by the retail outlet name, however, the current/future sales are being driven by the report parameters selected and not the retail outlet of the main report.
I believe I can achieve what I want by using sub reports but for scalability and performance I would prefer not to use sub reports (most places including MSDN suggest that data regions should be used in preference to sub reports).
I had thought to create a filter for the nested tables but I can't quite see how to get the correct value, I tried adding a filter to compare the retail outlet name field in the nested table to the reportItem containing the retail outlet name in the main report but I can't use ReportItems in filters. I think it must be because the grouping I am using on the table isn't passing down to the footer of the table where I have the two nested tables.
Any help would be gratefully received.
Thank you.
