Using Subreports

Subreports are a powerful feature of SSRS. I use them in all my reports to separate out the different tasks that need to be done. I use a master report to handle interaction with the user (working out what parameters and parameter values should be available and receiving input) and displaying any headers/footers.

MasterReport
Master report design: header, footer, subreports

I then call a subreport for each atomic part of the report. The subreport receives the parameters it needs, fetches the data and displays the report element(s).

The advantages of this setup are:

  • Separation of concerns – It makes the report less complex and easier to change when it is split into several files and each file has less responsibility.
  • The subreports can be reused in other master reports. As long as it receives the parameter values it needs, it can be placed anywhere.
  • You can reuse the master RDL for other reports. The headers and footers remain the same, but with different content. Often most of the report parameters remain the same too.
  • The individual subreports can easily be shown/hidden, plus page breaks etc. become easier.
ParametersDatasets
Master report parameters & datasets

My standard method for creating master and subreports is:

  1. Create a master RDL.
  2. Add datasets to fetch lists of available parameter values.
  3. Add datasets to fetch lists of default parameter values.
  4. Add report parameters, using the datasets to populate them.
  5. Add header and footer.
  6. Add a table to the main report area with a cell for each report part.
  7. Create a subreport with necessary report parameters (no available / default values needed).
  8. Add datasets to fetch the report part data, using the subreport parameter values.
  9. Add the report elements, using the datasets.
  10. Repeat for each subreport.
  11. On the master report, add a subreport item to each table cell.
  12. Set the subreport properties: select which subreport to call, show/hide & parameter mapping.
    SubreportProperties
    Subreport parameter mapping

     

     

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s