solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Create Centralized Report Headers Using Subreports in SSRS

MSSQLTips author Rob Fisch By:   |   Read Comments (5)   |   Related Tips: > Reporting Services Subreports
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.
  • If you have any questions or comments about this tip, send them to Rob Fisch


Last Update: 10/10/2007


About the author
MSSQLTips author Rob Fisch
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Monday, October 15, 2007 - 1:45:53 PM - dj_dba Read The Tip

Thanks for helpful article.

You might want to consider using one of the sample DBs, such as Adventure Works, etc. for future examples so we can follow your steps exactly.  This makes it much easier to troubleshoot when things don't look like the example.  Just a suggestion.

Thanks again.

dj


Thursday, September 27, 2012 - 10:51:19 PM - A.Manojkumar Read The Tip

Hi

I have developed the subreports but i could not insert the subreport in page header of footer...how can i do that? Please help me i need to centralize the  header and footer all over the reports...


Monday, December 03, 2012 - 2:11:18 AM - srinu Read The Tip

I have 2 parameters @StartDate and @EndDate...

I need to retrieve data in between Dates. up to here ok..

then I need to filter Data by giving Userid betwen the above Dates...

 

so can U please explain me step by step Process

 


Monday, December 03, 2012 - 8:36:10 AM - Rob Fisch Read The Tip

Hi Srinu,

Please review this tip:

http://www.mssqltips.com/sqlservertip/1336/pass-parameters-and-options-with-a-url-in-sql-reporting-services/

 

-Rob


Friday, February 22, 2013 - 11:03:30 AM - Bob Armstrong Read The Tip

Rob,

I created a main report with 40 sub-reports.  It all looks fine except for white lines on the report where the sub-reports were bypassed by a filter.

The report cannot be grouped since the requirement states that the group name needs to be in the header and not in the detail line.  I cannot think of a way to resolve the problem without using those subreports.

 

Any Ideas?

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.