We offer private, customized training for 3 or more people at your site or online.
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):
|Some of the Try It practices in this tutorial build on one another. Answers for each report along the way can be found in the /Answers folder of your report server.|
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.
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.
Figure 40 : Resizing the Data Region
Figure 41: Configuring the Tablix Properties for the List Item
Figure 42 : Group Properties
|By adding a group expression, you changed your details grouping to be a parent group organized by sales order ids.|
Figure 43 : Arranging the Fields
Figure 44 : Adding Fields
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
|SSRS also includes a feature called Linked Reports. Linked Reports are created and managed in the web portal. Linked reports have their own settings such as permissions and parameters, but link to a shared report definition. Additional information can be found at http://technet.microsoft.com/en-us/library/ms155998.aspx or under “Create a Linked Report” in the SQL Server Documentation.|
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.
Because subreports are generated at the same time as the parent report, they do not offer any performance benefits. Additionally, they may even slow performance over nested or separate data regions because SSRS processes each instance of the subreport as a separate report, even though it is displayed within the parent report.
|If your subreport is located within a cell of a table or matrix, the page breaks defined within the subreport will be ignored.|
A subreport can be added to an empty location on the parent report’s report body, or it can be added to a data region. If you add a subreport to a data region, it will repeat for each group or detail row in which it was embedded.
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.
|You must include a report parameter and not a query parameter in the subreport. For more information on report and query parameters, see Tutorial 6, “Add Flexibility with Parameters”.|
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.
Figure 45 : Subreport Item
You can find a completed report at: /Answers/Ch7-SubreportParentRptCompleted-1.
|Notice that the path to the subreport is included. If the reports are deployed to a SharePoint Integrated SSRS server, the parent and subreports must exist in the same folder, and the filename of the report including the file extension should be used.|
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.
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.
Figure 46: Map Polygon Properties
You can find completed reports at: /Answers/Ch7-SalesByState2011Completed-2 and /Answers/Ch7-SalesMap2011Completed-2.
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.
Figure 56 : Inserting a Row Inside a Group
Figure 57 : Merging Cells
Figure 58: Subreport Properties
Figure 59: Report Parameter Properties
Figure 60: Configuring the Filter
Figure 61: Configuring the Subreport
|If you receive a data cache error, click the Refresh button on the Run tab. Additionally, sometimes modifications to the subreport cannot be seen immediately when you refresh the parent report. If this happens, save and run the subreport independently by unhiding the parameter and entering a valid part number and then go back to the parent report and try running it again.|
A completed sample report can be found at:
/Answers/Ch7-ProductSalesRptCompleted-2 and /Answers/Ch7-VendorSubReportCompleted-1.
In-Depth SSRS Training
For in-depth SSRS training, click here to view all of Accelebrate's SSRS training courses for you and your staff.
Our live, instructor-led lectures are far more effective than pre-recorded classes
If your team is not 100% satisfied with your training, we do what's necessary to make it right
Whether you are at home or in the office, we make learning interactive and engaging
We accept check, ACH/EFT, major credit cards, and most purchase orders
New York City
Salt Lake City