Creating Parent-Child Relationships in SSRS
- by Tim Murphy
As I have been working on SQL Server Reporting Services reports the last couple of weeks I ran into a scenario where I needed to present a parent-child data layout. It is rare that I have seen a report that was a simple tabular or matrix format and this report continued that trend. I found that the processes for developing complex SSRS reports aren’t as commonly described as I would have thought. Below I will layout the process that I went through to create a solution. I started with a List control which will contain the layout of the master (parent) information. This allows for a main repeating report part. The dataset for this report should include the data elements needed to be passed to the subreport as parameters. As you can see the layout is simply text boxes that are bound to the dataset. The next step is to set a row group on the List row. When the dialog appears select the field that you wish to group your report by. A good example in this case would be the employee name or ID. Create a second report which becomes the subreport. The example below has a matrix control. Create the report as you would any parameter driven document by parameterizing the dataset. Add the subreport to the main report inside the row of the List control. This can be accomplished by either dragging the report from the solution explorer or inserting a Subreport control and then setting the report name property. The last step is to set the parameters on the subreport. In this case the subreport has EmpId and ReportYear as parameters. While some of the documentation on this states that the dialog will automatically detect the child parameters, but this has not been my experience. You must make sure that the names match exactly. Tie the name of the parameter to either a field in the dataset or a parameter of the parent report. del.icio.us Tags: SQL Server Reporting Services,SSRS,SQL Server,Subreports
