Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services 2012 Drilldown Features


By:   |   Last Updated: 2012-12-12   |   Comments (17)   |   Related Tips: > Reporting Services Drill Through

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.

toggle setup

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.

toggle flagtoggle up

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.

nested region

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.

nested region

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.

drill through report setup

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.

path reference

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.

drill through report back

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.

drag subreport

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.

select sub 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.

subreport parameters

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.

subreport size

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


Last Updated: 2012-12-12


next webcast button


next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

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, December 11, 2014 - 8:21:31 AM - Scott Murray Back To Top

I would first check the can grow property.


Thursday, December 11, 2014 - 2:06:31 AM - Blackie Back To Top

I have a problem with drilldown function in my reports after upgrade to reportviewer 11 (also tested latest version DLLs 11.0.3452.0) component in my ASP.NET application.

The report is based on multiple row groups which should be opened one after another.

If I open some row group (drill down) via clicking onto a + sign in any other row than first from the top, the column width becomes too big(like twice the original row width) causing the whole row width to increase. If I do the same with the first row in the table, the tablix row is holding it's original width.

The same report was running without this problem via reportviewer 10 component in the same application.

The drilldown in the same report is working fine when run directly in Reportbuilder 3.0 or when exported to Excel.

Query is based on simple stored procedure with multiple parameters and returns a table with fixed structure.

Can anyone suggest to me what can I do to solve this problem?

 


Wednesday, August 20, 2014 - 12:33:47 PM - James Back To Top

I was experiencing the issue with a self referencing SSRS report is holding on the parameter values that were passed to it in VS designer preview.  When I deployed to Sharepoint / SSRS environment I did not have this issue.


James


Wednesday, August 20, 2014 - 9:52:17 AM - James Back To Top

@Bhushan, I am experiencing the same issue.  My self referencing SSRS report is holding on the parameter values that were passed to it.  I have been looking for a solution, but have not found anything.  I have tried the ideas in the links below, but that did not work.

http://stackoverflow.com/questions/6280832/ssrs-parameter-value-change-is-not-running-my-report-again

http://www.bp-msbi.com/2008/10/refreshing-report-data-through-view/

 

James


Thursday, May 29, 2014 - 5:41:35 AM - Bhushan Back To Top

Hi

 

I am facing one issue in SSRS 2K12 while self referencing the report along with passing value to multiple value parameters. Initially when I view report parameter values selection/deselection work well. When I self refrenece report on click of text box by passing one of the possible value to this multiple value parameter and then try to select another value from parameter and click on view report, it doesn't show the selected value, it keeps on holding the value which was passed during self reference. I think this is a bug in SSRS 2K12. Would be glad if someone try to reproduce and reply.


Tuesday, May 06, 2014 - 4:34:44 PM - Patricia Back To Top

Thanks.

It has been a while since I tried the Rounding function on some of those reports; however, I believe it worked for some of the totals but not for all of them. I must of managed to resolve the situation, but when the saw the above I recall there was an issue with it.

Thanks again, Patricia


Tuesday, May 06, 2014 - 3:54:48 PM - scott murray Back To Top

Patricia... rounding is causing the issue you noted.


Tuesday, May 06, 2014 - 11:58:48 AM - Patricia Back To Top

Just wondering why the drill down total is higher by 1. I have noticed this is in some other reports people have created. What can one do to assure that the totals match to the summary total.

Example:

Clothing                  $1437

              SO57030    $897

              SO57044    $541

Actual Total is         $1438

 

Thanks,

Patricia


Thursday, October 10, 2013 - 8:08:19 AM - Rajiv Kumar Back To Top

It is really helpful for understand  the basic concept.


Wednesday, August 21, 2013 - 12:08:50 PM - TonyC Back To Top

Scott, I am doing this on a development site for which I have full access. As I mentioned, I can launch the report that I am drilling down to directly (rather than drilling through) and it works just fine. Also, I am NOT passing parameters from one report to the other; I have a simple text box with the "action" set to "Go to report." Again, this is working fine on the stand alone SSRS 2012 version - just not in the SharePoint integration mode.

The report is launch however the behavior is such that it seems it is not interpreting the default parameters correctly.


Tuesday, August 20, 2013 - 8:56:04 PM - Scott Murray Back To Top

Tony..... I have not seen that behaviour, but unfortunately, I no longer have a SharePoint 2010 environment to test on.  Have you verified the url is passing the parameters?  Have you also checked your access on the SharePoint side.  


Tuesday, August 20, 2013 - 5:07:10 PM - TonyC Back To Top

I forgot to mention, we are using SQL Server 2012, SSRS with SharePoint integration mode (SharePoint 2010). I've tried deploying with SSDT (Visual Studio 2010). When in SharePoint, I can open the reports for editing using Report Build 3.0 and all of the settings appear to be the same as in Visual Studio (parameters, text box action, etc).


Tuesday, August 20, 2013 - 5:03:36 PM - TonyC Back To Top

This works in native mode for reports I've generated in the past. We're now moving our SSRS 2012 reports to SharePoint integration mode and the drillthrough reports no longer function correctly. Some reports complain because parameters on the target report (the report drilling down to) are not set even though they have default values set. Some reports run but you cannot change the parameters (i.e., the parameters are not available to be modifed. They simply are not available).

In both cases, if we run the target (drill down to) report directly, all is well. I have even created a "Main menu" text box, that simply renders the main report, on each of the drill through reports. Again, this works (drilling up/back) but drillingdown/through does not.

Any ideas on this? Bug?


Friday, August 16, 2013 - 3:57:25 PM - Scott Murray Back To Top

Anil... not sure about the specifics of your question, but the answer is yes... you just need to change the format used by ssrs.


Friday, August 16, 2013 - 2:59:00 PM - anil Back To Top

can i have a stored procedure with input as yyyy-mm-dd format and output as dd-mm-yyyy format..

please explain me with an example

 


Monday, December 17, 2012 - 5:32:14 PM - David Bradford Back To Top

Hey Scott,

Very nice article. I am relatively new to health-care industry so it's great to meet other SQL/SSRS people working in the same domain. I will definitely be using parts of this example for something we're working on here.

Kind regards,

David

 


Thursday, December 13, 2012 - 11:56:11 AM - Tatyana Back To Top

Thank you! Very useful.


Learn more about SQL Server tools