1. Linked Reports
1.1. What is a linked report?
A linked report is a report server item that provides an access point to an existing report. Conceptually, it is similar to a program shortcut that you use to run a program or open a file.
A linked report is derived from an existing report and retains the original's report definition. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.
You can create a linked report when you want to create additional versions of an existing report. For example, you could use a single regional sales report to create region-specific reports for all of your sales territories.
Although linked reports are typically based on parameterized reports, a parameterized report is not required. You can create linked reports whenever you want to deploy an existing report with different settings.
1.2. How to create a linked report
- In Report Manager, navigate to the folder containing the report that you want to link to, and then open the options menu can click Create Linked Report.
- Type a name for the new linked report. Optionally type a description.
- To select a different folder for the report, click Change Location. Click the folder you want to use, or type the folder name in the Location box. Click OK. If you do not select a different folder, the linked report is created in the current folder (where the report it is based on is stored).
- Click OK. The linked report opens.
- A linked report's icon differs from other items managed by a report server. The following icon indicates a linked report:
1.3. Example
- Step 1 – This is the original ‘Company Sales’ report in Adventure Works 2008 Sample Reports
- Step 2 – Click the Properties tab to see the ‘Create Linked Report’ Option
- Step 3 – After click the ‘Create Linked Report’, you can customize the liked report such as Name, Description, report location
- Step 4 – After click the ‘Create Linked Report’, you can also change the parameters, execution options, maintenance of report history, or report security. The chart below shows the parameters only
- Step 5 – You can see the linked report is shown in the report folder as we keep the linked report on the same folder of the original report.
2.Subreports
2.1. What is a subreport?
A subreport is a report item that displays another report inside the body of a main report. Conceptually, a subreport in a report is similar to a frame in a Web page. It is used to embed a report within a report. Any report can be used as a subreport. The report that is displayed as the subreport is stored on a report server, usually in the same folder as the parent report. You can design the parent report to pass parameters to the subreport. A subreport can be repeated within data regions, using a parameter to filter data in each instance of the subreport.
2.2. Example
In this illustration, the contact information displayed in the main Sales Order report actually comes from a Contacts subreport.
Note: You can create and modify report definitions (.rdl) in Report Builder and in Report Designer in SQL Server Data Tools. Each authoring environment provides different ways to create, open, and save reports and related items. For more information, see Designing Reports in Report Designer and Report Builder (SSRS) on the Web at microsoft.com
2.3. Comparing Subreports and Nested Data Regions
If you're thinking of using subreports to display separate groups of data, consider using data regions, such as tables, matrices, and charts, instead. Reports with data regions only may perform better than reports that include subreports.
Use data regions to nest groups of data from the same data source within a single data region. Use subreports to nest groups of data from different data sources within a single data region, reuse a subreport in multiple parent reports, or display a standalone report inside of another report. For example, you can create a "briefing book" by placing multiple subreports inside the body of another report.
Data regions provide much of the same functionality and flexibility as subreports, but with better performance. Because the report server processes each instance of a subreport as a separate report, performance can be impacted. For more information, see Nested Data Regions (Report Builder and SSRS).
3. Drillthrough Reports
3.1. What is a drillthrough report?
A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. For example, in this illustration, the sales summary report lists sales orders and totals. When a user clicks an order number in the summary list, another report opens that contains details about the order.
3.2. Example
The data in the drillthrough report is not retrieved until the user clicks the link in the main report that opens the drillthrough report. If the data for the main report and the drillthrough report must be retrieved at the same time, consider using a subreport.
4. Drilldown reports
4.1. What is a drilldown report?
A drilldown report is a layout design that at first hides complexity and enables the user to toggle conditionally hidden report items to control how much detail data they want to see. Drilldown reports must retrieve all possible data that can be shown in the report. The main reason for hiding items is to provide a report that shows summary data but enables a user to drill down into detail data. For reports that use large amounts of data, consider drillthrough reports instead.
4.2. Example:
In this illustration, the user clicks the plus signs (+) in the report to show detail data.
For example, you can initially hide all the rows except the outer group summary row for a table with row groups. For each inner group (including the details group), add a toggle to the grouping cell of the containing group. When the report is rendered, the user can click the text box to expand and collapse the detail data.
5. Which one to use: Drillthrough, Drilldown, Subreports, or Nested Data Regions?
5.1. Comparisons
You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drilldown action. You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix. You can display the data in a subreport that is completely contained within a main report. Or, you can put the detail data in drillthrough reports, separate reports that are displayed when a user clicks a link.
A. Drillthrough report
B. Subreport
C. Nested data regions
D. Drilldown action
All of these have commonalities, but they serve different purposes and have different features. Two of them, drillthrough reports and subreports, are actually separate reports. Nesting is a means of putting one data region inside another data region. Drilldown is an action you can apply to any report item to hide and show other report items. They all are ways that you can organize and display data to help your users understand your report better.
5.2. Summary of Characteristics
This table summarizes these different traits. Details are in separate sections later in this topic. Drilldown isn't included in these comparisons because you can apply its showing and hiding action to any report item.
| Trait | Subreport | Drillthroug | Nested |
| Uses dataset of main report | Same or different | Same or different | Same |
| Retrieves data | Data retrieved at the same time as main report | Data retrieved one drillthrough report at a time | Data retrieved all at the same time as main report |
| Is processed and rendered | With the main report | When link is clicked | With the main report. |
| Performs | Slower (but retrieves all data with main report) | Faster (but does not retrieve all data with main report) | Faster (and retrieves all data with main report) |
| Uses parameters | Yes | Yes | No |
| Can be reused | As report, or subreport or drillthrough report in other reports | As report, or subreport or drillthrough report in other reports | Cannot be reused. |
| Is located | External to main report, same or different report server | External to main report, same report server | Internal to main report |
| Is displayed | In the main report | In a different report | In the main report |
