By: Scott Murray | Comments (17) | Related: > Reporting Services Development
Problem
SQL Server Reporting Services (SSRS) 2012 includes many drilldown / drillthrough features. What options are available for creating reports with these features? In this tip we will look at the different options and how they can be used.
Solution
As you work with SSRS, you will quickly find a need to offer end users the ability to see additional details about the data they are currently viewing. Often this situation arises while looking at a summary or dashboard type report, and the end user would like to see "what makes up" a particular number. Alternate methods of providing this detail include:
- Drilldown Reports
- Nested Data Regions
- Subreport
- Drillthrough Reports
Which option is best certainly depends on your specific needs and uses. The remainder of this article will review how to create each of these different report options while also discussing some of the benefits and drawbacks of each choice.
Drilldown
A drilldown report is actually a slight misnomer; the true name is a drilldown action. In order to define a drilldown action, a tablix must first be created with the appropriate level of aggregation all the way to the level of drilldown detail desired. This detail includes both the row and columns desired for the lowest level viewing needed. The drilldown functionality works either at the row level, the column level, or the group (column or row) level.
First, a field is selected as the toggle button location. This field will display the + and - (show / hide) toggle switches. Next, as displayed in the below example, right mouse clicking on the sales order group, selecting group properties, and then navigating to the visibility window allows for the creation of a drilldown toggle from the category field to the sales order detail.
Now, as shown in the above screen print, the column, row, or group to be hidden is selected and then within the object's properties, the hidden property is set to true. Secondly, the toggle field must be set to the textbox selected earlier as the toggle location.
Now when the report is executed, the selected fields will be hidden until the report runner clicks on the plus button next to the toggle switch. The expanded data is displayed in the below figure; certainly the minus button can be clicked to then collapse the detail.
The drilldown methodology has several advantages including using the same data source for the detail and summary information and the data is all contained within the same report. However, the disadvantages include having to create enough "space" on the report itself to support viewing the drilldown and the parameters and data used for the drilldown must match the main reports detail.
Nested Data Regions
Nested data regions share several similarities to drilldown actions including using the same dataset as the main report. However, with a nested data region, a matrix, a table, a tablix, or even a chart is "embedded" into a cell on a tablix or list. This process actually allows for the creation of a "sub matrix" or chart for each and every grouping within a tablix which is much easier than creating each object on its own. For a detailed tip on embedding charts within a cell, please see my tip: SSRS Textbox Tips and Tricks. Creating a non-chart nested data region is very similar to the process of creating a nested chart as described in the linked article as well as the process followed while creating a drilldown report.
First, create a tablix with the desired level of detail / aggregation needed. Next, as shown below, drag a matrix object from the tool box to a cell within the existing object where the nested region is needed.
This scenario allows for the nesting of multiple layers of data complimented with the ease of setting up just one matrix instead of multiple matrices. The results of embedding the matrix within a group is displayed below.
Although the advantages of nested data regions are obvious, some of the disadvantages include:
- formatting and sizing must be properly set as the data / chart is displayed within a single cell that must be sized correctly (not too small to read for instance)
- scoping the data correctly can be tricky; the data that is displayed will match the same data which would be displayed or aggregated within the same column or row group.
Drillthrough Reports
Drillthrough reports diverge from the previous two methods discussed in many ways. First, as opposed to being embedded within an existing report, drillthrough reports are actually a separate and distinct report which has its own data sources, datasets, parameters, and layout. The drillthrough report can be run completely on its own.
In order to create a drillthrough report, the location of the "action" path must be selected. Simply, a particular textbox must be selected which will be the textbox end users will click on to open the drillthrough report. Generally, this text box will be the "number" or "item" upon which additional detail is needed; for instance, a text box that contains a sales figures or product category may the be the action location. Now, as displayed below, using the properties of that textbox, and navigating to the action tab, the report designer can select the appropriate "Navigate to" report. Furthermore, as noted in the below figure, parameter values or dataset values from the original report can also be passed to the drillthrough report.
The report designer must also pay special attention to the absolute and relative paths used in the "Specify a Report" field. Please see this MSDN article for details on absolute and relative paths.
A drillthrough report offers the flexibility of creating a new, separate report which also uses its own dataset and report layout. On the contrary, once the user navigates to the drillthrough report, the original report is no longer active and navigation using the back button may not always work. One idea for handling that situation is to actually create a "drillthrough button" on the second report which navigates back to the original report, as noted in the below illustration.
SubReports
Subreports are similar to drillthrough reports in that they are a separate and distinct report and use their own, if desired, dataset. However subreports are actually one report embedded within another report. Of course, this situation is advantageous as compared to a drillthrough report where a completely new report is opened; this convenience comes with a possible performance penalty, since both the main report and the subreport must be rendered at the same time. The first step to adding a subreport is to drag the subreport object from the toolbox onto the report panel, as displayed in the below figure.
Next, right mouse clicking on the subreport object and selecting Subreport properties allows for the input of the subreport object name (no special characters or spaces are allowed) and, more importantly, the selection of the subreport to be embedded within the parent report.
Any parameters needed for the subreport must be passed from the main report's parameter list. Certainly, the parent report and the subreport can share parameters, or default values could be used for the subreport.
Additionally, with subreports, consideration must be given to the actual size of the subreport. As you drag the subreport object onto the design grid and then resize it, this sizing is not the actual size the report will display when the report is run. It will autogrow to consume the space needed to display the subreport; no CanAutoGrow properties is available for the subreport.
Conclusion
SSRS includes many options for obtaining additional details about a number on a report. Drilldown actions allow for quick toggling of detail lines within a row or column group. Drillthrough reports give the user access to a completely new report. Nested Data regions allows for an object to be embedded within another object, whereas subreports allow one report to be embedded into a parent report. Of course, each of these methods comes at a cost; drilldown reports, nested data regions, and subreports require all the data to be consumed for each object where as a drillthrough report only consumes data for the main report. However, drillthrough reports force the user to navigate from one report to another and then back to the original report if needed. The report designer must weigh the benefits and cost of each method to determine the best options (or combination of options) for the report consumers.
Next Steps
- MSDN- Configuring Subreports and Drillthrough Reports
- Nested data regions-Charts
- Rob Barleys Report Parts vs. Subreports Tip
About the author
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
