When using a Report Layout/Organization type of
report you can have a
Field Type of
Subreport that will call another report with the parent report.
NOTE: The example reports shown on this page are part of the Example Reports package download. The "parent" report is called "Example - Subreport (parent)" which calls the subreport "Example - Top Ten Oldest".
|
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 result set. If the parent report query did not return any rows then the subreport will not be executed.
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 - Subreport (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 Projects.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:
