NOTE: The report shown on this page called 'Example - Subreport (parent)' and its subreport 'Example - Top Ten Oldest' can be downloaded as part of the Example Reports package.
Subreports can be used to provide a parent/child style report. In the above example, the "Parent" report counts open issues for each project and the "Child" report reports the top ten oldest issues for each project.
The main report query returns a result set of one row for each project. A subreport is always executed once for each row in the parent report’s result set. If the parent report query did not return any rows then the subreport will not be executed. If the main report query returns rows, then the subreport will be executed three times.
The values of the result set from the parent query can be used as parameters in the subreport.
In the above example the parent report (Example - Sub-report (parent)), the SQL Query looks like this:
select Projects.ProjectCode,
Projects.ProjectId,
Projects.ProjectDescription,
COUNT(Issues.IssueId) as NumIssues
from Issues
inner join Projects on Projects.ProjectId = Issues.ProjectId
where Issues.StatusCode = 'Open'
and (Projects.ProjectId = @ProjectId or @ProjectId = -1)
group by Projects.ProjectId, Projects.ProjectCode, Projects.ProjectDescription
order by Projects.ProjectCode desc
Note that part of the result set of this query is ProjectId, and the result set will produce one row for each ProjectId. The ProjectId is then passed to the subreport (Example - Top Ten Oldest) which has ProjectId as a parameter and uses it to filter for the correct project.
The Layout for the the parent report looks like this:
