By: Rob Fisch | Comments (6) | Related: > Reporting Services Development
Problem
In the SQL Server 2005 documentation, Microsoft suggests that "[if] you have used subreports in the past to display separate groups of data, consider using data regions (tables, matrices, lists, or charts) instead. But does that mean if you are using SQL Server 2005, you should avoid using subreports altogether?
Do you find yourself copying and pasting code from report to report in order to standardize a look and feel? Don't you wish there was a better way to centralize your reporting designs and re-use snippets of code in creative ways?
Solution
While performance may be a consideration, functionality and ease of code development are also important factors. Using subreports can provide some interesting solutions in a variety of ways. Here is a method of using a subreport as centralized way to manage report headers.
The Report Header Subreport
Let's create a standardized report header that can be used as a subreport while being managed centrally. When changes are made, they will automatically roll out to all reports sharing the subreport as a header.
Let's start with a fresh new report. You can reuse an old report solution or create new one. (For a new one, you will need a new Shared Data Source. You can make up something simple.)
- Right-click on the Reports folder and choose Add-->New Item.
Name your report header-subreport.rdl (or anything you want).
For the purposes of this demonstation, you don't even need a real dataset. But you need at least an artificial one. Follow these instructions (or create your own dataset).
- In the Dataset dropdown of the Data tab, choose <New Dataset...>
- In the Query string section, type Select 1 as pictured below.
- Click OK.
- Click on the Layout tab.
- Place a Table object from the Toolbox on your layout page at the top
- We'll save the top (header) row for the name of the organization or company...skip
down to the detail section of the table.
- In the first details textbox, right-click and select Expression.
- Type ="Report runtime: "+Globals!ExecutionTime
- Click the Preview tab to see the results.
- Size the textbox until the line does not wrap.
- In the second textbox of the details section type =Globals!ReportName
- In the third textbox type =User!UserID.
- Select all textboxes in the details row. In the Properties window and change the BorderStyle to Solid.
- Select the Footer row by click on the left grey selector.
- Right click on the selector and select Delete Row.
- In the first textbox of the header row, type the name of your company and/or department. (In real life, you might want to use a banner style graphic. For the purposes of this tutorial, it is not necessary.)
- Select the three cells in the header row. (This time do not use the row selector. Select them individually.)
- Right-click in one of the header textboxes and choose Merge cells.
- From the format toolbar, center the text in the header row.
- Make it bold.
- Still with the header textbox selected, in the Properties window, change the BorderStyle to Solid.
- Make sure the table is all the way in the top left corner of the layout section.
- Shrink the page layout to fit right up the edge of the table.
- In the Report Properties change all the margins to 0.
- The final Preview of your report should look something like this:
- Save and close your report.
The Main Report (that uses the subreport).
Now we'll create a second report where we can use the header template we just made as a subreport. You can name it MainReport.rdl.
- Start a new report the same way as the first report. No real dataset is
needed. As a reminder:
- Right-click on the Reports folder and choose Add-->New Item
- In the Dataset dropdown of the Data tab, choose <New Dataset...>
- In the Query string section, type Select 1.
- Click on the Layout tab of your report.
- From the Toolbox, choose the subreport object and drag it to the top of the layout section.
- Shrink the height of the subreport object to about .25 inches.
- Right-click the subreport and choose Properties.
- In the Subreport dropdown, choose header-subreport.
- Click OK.
- Now run the Preview of the report.
(Because you have not added content to the report, it only shows the header.)
If you need to make a change the header-subreport, all changes will rollout automatically to all reports that use the subreport.
Voila!
Next Steps
- Using this modular approach, try building a dashboard around a specific topic area using a combination of charts and tabular reports.
- Learn how to pass parameters to subreports.
- Take a look at these other Reporting Services tips.
About the author
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
