Advanced Report Design - Professional SQL Server 2005 Reporting Services - page 218

Subreports
This feature is largely borrowed from Microsoft Access. Essentially, a subreport is a stand-alone report
that is embedded into another report. Using parameters, you can link the contents of a subreport to the
main report.

There are some limitations to the content and formatting that can be rendered within a subreport. For
example, a multicolumn report may not be possible within a subreport (depending upon the rendering
format used). If you plan to use multiple columns in a subreport, test your report with the rendering
formats you plan to use.

There are generally two uses for subreports, which include embedding one instance of a separate report
into the body of another report with an unassociated data source. The other scenario involves using the
subreport as a custom data region to display repeated master and detail records in the body of the main
report. From a design standpoint, this makes perfect sense. Using a subreport allows you to separate
two related data sets and perhaps even data sources, linked in the same way that you would join tables
in a SQL query. It allows you to reuse an existing report so that you don't have to redesign functionality
you've already created. However, there may be a significant downside. If the master report will consume
more than just a few records, this means that the subreport must execute its query and render the con-
tent many times. For large volumes of data, this can prove to be a very inefficient solution. Carefully
reconsider the use of subreports with large result sets. It may be more efficient to construct one larger
report with a more complex query and multiple levels of grouping rather than assume the cost of execut-
ing a query many times. I rarely use subreports in standard reporting scenarios. If I do, the main report
is limited to one or a few records.

A subreport can be linked to the main report using a correlated parameter and field reference, so that it
can be used like a data region, but this is not essential. A subreport could be used to show aggregated
values unrelated to groupings or content in the rest of the report.

Creating a subreport is like creating any other report. You simply create a report and then add it to
another report as a subreport. If you intend to use the main report and subreport as a Master/Detail view
of related data, the subreport should expose a parameter that can be linked to a field in the main report.
In the following walk-through, you'll build a simple report that lists products and exposes a subcategory
parameter. The main report will list categories and subcategories and the product list report will then be
used as a data region, like a table or list as in previous examples.

The first report, which will be used as a subreport, will include a list of products. The second report will
consist of the product categories and subcategories and will contain the subreport, which renders a list
of products for each subcategory.

1.

Add a new report to your project called Product List Subreport.

2.

On the Data tab, create a new data set called Product_List using the AdventureWorks database.
Add the Product table to the data set and select the Name, StandardPrice, and
ProductSubCategoryID columns to be output by the query. Sort the records by the Name col-
umn in ascending order.

3.

Create a parameter for the ProductSubCategoryID column called @SubCategoryID . The easiest
way to do this is to move the cursor to the grid column labeled Criteria on the row for the
ProductSubCategoryID table column and type = @SubCategoryID . The SQL for the data set
should look like this: