Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SSRS Report Parts Versus Sub Reports FAQ


By:   |   Last Updated: 2011-02-24   |   Comments (5)   |   Related Tips: > Reporting Services Subreports

Problem

I'm trying to decide on a development strategy to satisfy the reporting needs in my organization.  I would like to increase our efficiency in responding to report requests, while minimizing our maintenance burden.  Two topics that I would like to dig in to are Report Parts and Subreports.  Can you provide some considerations for using one versus the other?

Solution

In this tip I will discuss Report Parts and Subreports in the form of a list of Frequently Asked Questions (FAQ).  For additional details on report parts and subreports, please refer to the earlier tips Getting Started with Report Parts in SQL Server Report Builder 3.0 and Create Centralized Report Headers Using Subreports in Reporting Services.

What is a Report Part?

A Report Part is an element of an existing report that can be published to the report server and made available for reuse in other reports.  Examples of report items that can be published as report parts are charts, gauges, images, maps, parameters, rectangles, tables, matrices, and lists (see the MDSN article Publishing Report Parts for additional details).  A report part is stored as an XML fragment in a file with the extension .rsc.

What is a Subreport?

A Subreport is a SQL Server Reporting Services report that is rendered from within another report.  When designing a report, you can add a subreport report item, then specify the report to run and any parameters required.  When your report is executed, the subreport is also executed and rendered within your report.  Think of your report as a container for one or more subreports.  There is no visual indication to the user that a subreport has been executed.

What version(s) of SQL Server Reporting Services include Subreports and Report Parts? 

Subreports are available in all versions of Reporting Services; report parts were a new feature in SQL Server Reporting Services 2008 R2. 

Can I use Subreports and Report Parts in Business Intelligence Development Studio (BIDS) and/or Report Builder?

Subreports are available in any version of BIDS and in Report Builder 3.0.  Report parts are only available in Report Builder 3.0 and BIDS 2008 R2.  However, in BIDS 2008 R2 you can only publish report parts; you cannot include a report part in a report that you are designing.  In Report Builder 3.0 you can select report parts to include in your report from the Report Part Gallery, which allows you to search for report parts on the report server. 

When designing a report, how is the user interface different for a subreport versus a report part?

When you add a report part to a report you are designing, the user interface looks just like you added a report item to the report.  When you add a subreport to the report you are designing, all you see is a blank rectangle that represents the area of the report where the subreport will be rendered. 

How are datasets that provide data for a report part handled?

When a report part is added to a report, the dataset that it uses gets added as well.  The dataset may be a shared dataset or an embedded dataset; either way it gets copied into the report.   

If I add a report part to my report, what happens when the report part is modified and republished?

When you add a report part to your report, you are essentially making a copy of the report part and adding it to your report.  You are free to make changes to the report part within your report.  When a report part is changed and republished, initially nothing happens to the report part in your report.  When you open your report in Report Builder 3.0, the default behavior is to ask you if you want to update the report part to the latest published version.  The setting "Show updates to published report parts in my reports" in Report Builder Options determines whether you will be notified when a report part has been changed.  There is no check to determine that you have made changes to the report part; if you have and you accept the update to the report part, then your changes will be overwritten.

If I add a subreport to my report, what happens when the subreport is modified?

A subreport exists independent of any report that includes it.  If a subreport is changed, any report that includes it will reflect the change the next time it is run.

How does my choice of subreports versus report parts affect report execution performance?

There is no impact on performance when using report parts which are embedded in the report, essentially it's the same as if you added the report items directly to the report.  With subreports there is some impact on performance because the report server processes each instance of a subreport as a separate report.

What do you think are the key decision points for choosing report parts versus subreports?

As with most design decisions, it usually comes down to your specific requirements and constraints.  In general here are some points to consider:

  • What version of SQL Server Reporting Services are you using? You can use subreports with any version of Reporting Services; report parts are only available with SQL Server Reporting Services 2008 R2.
  • Are you using BIDS or Report Builder 3.0 to design reports? If you're using BIDS to design reports, you can publish report parts but you cannot add a report part to a report.  Report Builder 3.0 can publish report parts and use them.  Report Builder 3.0 can use subreports; prior versions of Report Builder cannot.
  • What type of people are developing reports?  Report parts are much more appealing to business users; they can still get the WYSIWYG experience in Report Builder 3.0.  For developers WYSIWYG is always good, BIDS 2008 R2 provides it, but it's not essential.  With a subreport there is no WYSIWYG experience.
  • How often are the report parts going to change?  Remember that a report that uses a report part must be opened in Report Builder 3.0 in order to get an updated version of a report part.  A report that includes a subreport automatically executes the latest version of the report.
  • Do you want to perform any customizations on the report part or subreport?  You can change the properties of a report part after you add it to your report.  You cannot make any changes to a subreport that you have added to your report.  You can change the subreport but that change will be seen in any report that includes the subreport.
Next Steps


Last Updated: 2011-02-24


get scripts

next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Thursday, August 09, 2012 - 7:58:02 AM - Jit Back To Top

 

Thanx !!!!!!

     Sir it is working now I Placeed all subreport controls on my first report in execution order

     instead of placing them on different reports & all 25 reports execute successfully 

Thanx once again for immediate reply !!!!!!!!!


Thursday, August 09, 2012 - 6:24:11 AM - Ray Barley Back To Top

Can you run the subreport that is failing on it's own successfully?


Wednesday, August 08, 2012 - 4:22:08 AM - Jit Back To Top

 

THANX !!!!!!!!!!

   For Reply, I removed the subreport & again add it.

Now on the same report I am getting error ,

    Data Retrieval Failed For Subreport Located at ...

 


Monday, August 06, 2012 - 8:37:34 AM - Ray Barley Back To Top

This may be one instance where you have to open the RDL file with noepad and actually go through the XML.  Look at the RDL for one of the subreports that is working then compare that to the one that isn't.  There are occasional glitches in the RDL where it doesn't seem to match what you did in the designer.

Another idea is to remove the subreport from your report and add it back which will force you to map the report parameters again.


Monday, August 06, 2012 - 3:00:44 AM - Jit Back To Top

 

I Created 25 Reports using Subreports. Each Report provide parameters to next report.

Using above method, 21 reports are working correctly without any error.But 22nd Report is showing Error

One Or More Parameters Are required to run the subreport

But there is no issue with any parameter values. Even In Preview After rendering the report it shows error on next Page.

Plz Help Me to resolve the issue !!!!!!


Learn more about SQL Server tools