SSRS 2016 Tutorial: Enhanced Report Items Part 2
In addition to all of the visual reporting features such as charts, sparklines, etc., Microsoft provides additional reporting features to enhance your reports. Some of these features, such as nested data regions and drillthrough reports, are covered in this tutorial. This tutorial builds on some of the reports and topics from the first tutorial, however, you can use the answer keys if you are not performing all of the exercises in order.
In this SSRS Tutorial, you will learn how to:
Setup (this is the same set up as you needed in part 1. If you already have this, no action needed):
You can nest one data region inside of another data region. You saw this feature in the data bar and indicator Try It in this tutorial. When nesting a report item, it is important to remember the scope of each area on the report surface and understand how that will relate to your new report item. For example, if you add a chart to the text box located in the Tablix Corner (the upper left corner of a matrix), it will present static data that will appear only once, at the beginning of the report. If you add a Sparkline report item to a text box in the Details area of the report, you will receive one Sparkline for every row of data being returned from the dataset query.
In addition to adding graphical report items to a Tablix data region, you can also nest other report items. For example, you can nest a Table report item inside of a List report item to provide details under a group heading with page breaks between each grouping.
With SQL Server 2008R2, Microsoft added a feature called Report Parts. With Report Parts, you can save and deploy charts, maps, or any other report object individually. You can then re-use those report parts to build new reports. Because nested data regions do not exist exclusive of the data region of which they are a part, you cannot save them as report parts.
Nesting Data Regions
By performing the following steps, you will nest a table inside of a list item. This will allow you to see an individual group on each page with a table representing subgroups and details.
You can find a completed report at: /Answers/Ch7-SampleNestedRptCompleted-1.
Reporting Services offers a wide variety of options for organizing data within a report. In this and previous tutorials, you have already worked with hiding groups to provide reports that users can drill down into. Additionally, you have nested data regions inside of each other to correlate different types of data regions such as tables and data bars. In addition to these features, SSRS provides you with the ability to define subreports that can be used in multiple locations and inserted into parent reports. Drillthrough reports are launched when a user clicks on a link in a report. You use parameters to pass information from the parent to the child report
Working with Subreports
Subreports offer many advantages when designing reports. First, they allow you to reuse portions of a report in more than one parent report. Additionally, because they can use the same or different data sets from the parent report, they add flexibility for complex reports.
You can pass information from the parent report to the subreport by using parameters. You must first create a report parameter in the subreport. When you add the subreport to the parent report, you can define the name of the report parameter and the value that you want to pass to it on the Parameters page of the Subreport Properties dialog box. For subreports nested in a data region, you will use a field expression to define the value to be passed for each row or group. With Report Builder, when the subreport is located on the report server, you need to be sure to type the name exactly as it is defined in the subreport. This is a case sensitive field.
By performing the following steps, you will insert a subreport that stands on its own, without the need to pass parameters. Because you will place the subreport inside of the list object, the subreport will appear on each page of the parent report.
You can find a completed report at: /Answers/Ch7-SubreportParentRptCompleted-1.
In addition to making it easier to design reports with complex requirements, drillthrough reports have the added benefit of improving performance. Drillthrough reports are not generated until the action linked to the drillthrough report is activated. Because the drillthrough report is an independent report on its own, it can be launched directly from Report Manager in addition to being launched from the parent report.
The top or general level of information can be displayed on the first report. You will then use the Go to report option on the Action page of the appropriate object to configure the report to be launched when a user clicks on that object.
When you configure an action for an object, it does not automatically format the object to alert the user that if they click on the object, it will do something different. For text boxes, you will frequently format the text to be blue and underlined—the universal sign for a hyperlink. Report Builder also gives you the ability to design a tool tip that will be displayed when you pause the mouse over the object. In some cases, you may want to add additional text to explain what will happen if they click the object.
There are many situations where the use of a drillthrough report would be valuable including the following:
Like subreports, you can use parameters to pass values from the parent report to the drillthrough report.
Defining a Drillthrough Report
By performing the following steps, you will add a drillthrough report to the SalesMap2008 report that will provide detailed information about a particular state. If you did not complete the SalesMap2008 report in the Map Try It above, you can open a copy of the completed report from /Answers/Ch7-SalesMap2011Completed-1.
You can find completed reports at: /Answers/Ch7-SalesByState2011Completed-2 and /Answers/Ch7-SalesMap2011Completed-2.
Exercise 2: Adding a correlated sub-report
The goal of this exercise is to allow you to practice the steps involved with adding a subreport to a parent report. You will also practice sending a parameter through to the subreport. Step by step directions are provided in the Solutions to Exercises section. Answers may vary depending on how the directions are interpreted. If you did not complete the exercise in the prior tutorial, you can find a completed report at /Answers/Ch7- ProductSalesRptCompleted-1.
A completed sample report can be found at: