SSRS 2016 Enhanced Report Items

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:

• Understand nesting data regions
• Create subreports and drillthrough reports
• Work with drillthrough reports

Files needed:

• /SampleReports
• /Answers
• /StudentX
• \Classfiles\ReportBuilder\Starter Solutions\Ch7-Queries.txt

Setup (this is the same set up as you needed in part 1. If you already have this, no action needed):

• SSRS 2016 installed and configured in Native Mode
• The Adventureworks2014 and AdventureworksDW2014 databases that can be downloaded from here:
  • AdventureWorks2014FullDatabaseBackup.zip
  • AdventureWorksDW2014FullDatabaseBackup.zip
• The samples and answers projects in the zip file downloaded and then extracted
  • Use SQL Server Data Tools for Visual Studio 2015 or later to deploy first the SampleReports.sln solution/project and then the Answers.sln solution/project located under the Setup folder
  • If you are not running SQL Server on your local computer, change the TargetServerURL in the project properties of each project before deploying.

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.

Nesting Data Regions

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.

1. In Report builder, use the Blank Report option to create a new report.
2. Save the report as SampleNestedRpt in your StudentX folder.
3. Add a pointer to the /Datasets/SalesOrderDetails shared dataset in the Report Data window, name it SalesOrderDetails.
4. Add a List report item to the design surface. Keep in mind that like tables and matrices, list report items are implemented as a Tablix data region.


Figure 40 : Resizing the Data Region

5. Click inside the List data region and then click the Tablix corner to allow you to move and resize the List data region.
6. Resize the List data region to fill the existing report body area as shown in Figure 40.


Figure 41: Configuring the Tablix Properties for the List Item

7. Click within the List data region to activate the Tablix data region.
8. Right-click any of the handles around the data region, andclick Tablix Properties.
9. In the Tablix Properties dialog box, select the SalesOrderDetails data set in the Dataset name drop-down list, and then click OK.
10. In the Row Groups area, click the down arrow next to the (Details), and then click on Group Properties.


Figure 42 : Group Properties

11. In the Group Properties dialog box, on the General page, change the Name to SalesOrderNumber.
12. Click Add in the Group expressions area.
13. From the Group on drop-down list, select [SalesOrderNumber].
    
    

By adding a group expression, you changed your details grouping to be a parent group organized by sales order ids.



14. Click Page Breaks to switch to the Page Breaks page.
15. Select the box next to Between each instance of a group.
16. Click OK to close the Group Properties dialog box. Notice that the three horizontal lines representing detail rows are now gone in both the Row Groups area and the List data region, and have been replaced by the group symbol .


Figure 43 : Arranging the Fields

17. Add the following data fields near the top of the list data region:
1. SalesorderNumber
2. CustomerID
3. OrderDate
18. For each field, right-click the text box and click Expression.
19. In the expression dialog box, type the following for each respective box. When finished, run the report to verify that all of the expressions work.
1. ="Sales Order: " & Fields!SalesOrderNumber.Value
2. ="Customer: " & Fields!CustomerID.Value
3. ="Order Date: " & Fields!OrderDate.Value
   


Figure 44 : Adding Fields

20. On the Insert tab, click Table and then Insert Table. Add the table inside of the list item just below the fields that you edited.
21. Add the following fields to the table from left to right as shown in Figure 44.
1. ProductID
2. OrderQty
3. LineTotal
22. Save your completed report.
23. Run the report. View several pages and notice that each new Sales Order starts on its own page.
24. If time permits, practice formatting your report, or nest a chart in the List data region.

You can find a completed report at: /Answers/Ch7-SampleNestedRptCompleted-1.

Subreports and Drillthrough Reports

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.

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

Passing Parameters

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”.

Configuring Subreports

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.

1. In Report Builder, open, review, and run SubreportChildRpt from the SampleReports folder on your report server.
2. Use Report Builder to open, review, and run SubreportParentRpt from the SampleReports folder on your report server.
3. Return to the Design view.
4. Use the Save As option to save the SubreportParentRptX to your /StudentX folder.


Figure 45 : Subreport Item

5. On the Insert tab, click on the Subreport item.
6. Click and drag to insert the Subreport item inside of the list item on the right side as shown in Figure 45.
7. Right-click the subreport that you just added, and then click Subreport Properties.
8. In the Subreport Properties dialog box, on the General page, click Browse, browse to the SampleReports folder, select SubreportChildRpt, and then click Open.
9. Change to the Border page.
10. Click the Outline box to add a border around the Subreport.
11. Click OK.
12. Run the report. Save the SubreportParentRpt.rdl to your StudentX folder.

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.

Working with Drillthrough Reports

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:

• Opening a detailed report with information on the individual sales information for a particular product category in a chart showing total sales by category.
• Opening an individual sales order report from a list of all orders placed in the last 5 years.
• Opening a report listing a specific employee’s hire date, personal information, and department history from a report showing all employees.

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.

1. If necessary open Report Builder.
2. Open, review, and run the SalesByState2011 report located in /SampleReports on your report server.
3. Test the report with several state abbreviations, such as TX and NC.
4. In the Report Data window, expand Parameters and right-click the State parameter, and then click Parameter Properties.
5. Set the Select Parameter visibility to Hidden, and then click OK.
6. Use the Save As option to save this report to your /StudentX folder.
7. Open your SalesMap2011.rdl report.
   

   
   Figure 46: Map Polygon Properties

8. If necessary, click inside the map to activate the Map Layers window.
   Note: You may have to scroll to the right to see the Map Layers window.
9. In the Map Layers window, click the down arrow next to PolygonLayer1, and then click Polygon Properties.
10. In the Map Polygon Properties window, go to the Action page.
11. Select Go to report in the Enable as an action section.
12. Next to the Specify a report textbox, click Browse and select SalesByState2011 from your StudentX folder.
13. In the Use these parameters to run the report section, click Add.
14. In the name column, select State.
15. In the value column, select [StateProvinceCode].
    Note: The Name of State comes from the State parameter in the report you are drilling through to. The Value must come from the dataset associated with the map report item.
16. Click OK to close the Map Polygon Properties dialog box.
17. Run the SalesMap2011. Click on a state and verify that the SalesByState2008 report opens and only includes information that correlates to the state you selected.
    Note: The Blue Back arrow in the Navigation section will return you to the map report.

You can find completed reports at: /Answers/Ch7-SalesByState2011Completed-2 and /Answers/Ch7-SalesMap2011Completed-2.

Lab

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.

1. Open a second copy of Report Builder. In the first copy, verify that ProductSalesRpt from the prior exercise is open. In the second copy of Report Builder, open VendorSubReport from /SampleReports.
2. Review the VendorSubReport design and run the report. If you did not complete the prior exercise, review and run both reports.
3. Save a copy of each report to your /StudentX folder.
4. In the table for the ProductSalesRpt, add a row inside of the Product group below the existing rows.
1. In the ProductSalesRpt, Right-click on the [Product] group text box in the table, and then click Insert Row | Inside Group – Below as shown in Figure 56.


Figure 56 : Inserting a Row Inside a Group

5. Merge the two cells on the right-most side of the new row together.


Figure 57 : Merging Cells

2. Hold the CTRL key and click the two right-most empty cells in the new row as shown in Figure 57, right-click the selected cells, and then click Merge Cells.
   
   
6. Add the VendorSubReport report as a subreport to this newly merged textbox.


Figure 58: Subreport Properties

3. Click in the merged cell and then on the Insert ribbon double click the Subreport report item.
4. Right-click the text box with the new Subreport item, and then click Subreport Properties.
5. In the Subreport Properties dialog box, click Browse and select the VendorSubReport report in your StudentX folder, and then click Open.
6. Click OK.
   
   
7. Save ProductSalesRpt. Change to the Report Builder where you opened the VendorSubReport.rdl report.
8. Add a parameter for the ProductName field and configure it to be ready to accept the Product field from the ProductSalesRpt report.
   Note: The Product field in the ProductSalesInfo dataset maps to the ProductName field in the VendorInfo dataset.


Figure 59: Report Parameter Properties

7. In the Report Data window of the VendorSubReport designer, right-click the parameter folder, and then click Add Parameter.
8. In the Report Parameter Properties dialog box, type ProductName in the name.
9. Verify that Text is selected in the Data type drop-down list.
10. Select Hidden in the Parameter Visibility section.
11. Click OK.
    Note: The Product field in the ProductSalesInfo dataset maps to the ProductName field in the VendorInfo dataset.


Figure 60: Configuring the Filter

12. If necessary, expand the Datasets folder, and right-click the VendorInfo dataset pointer, and click Dataset Properties.
13. On the Filters page, click Add.
14. In the new filter definition, select [ProductName] in the Expression drop down list.
15. Verify that the equals sign “=” is in the Operator drop down list.
16. Click the expression button next to the Value text box.
17. In the Expression window, click the Parameters category.
18. Double click the ProductName parameter under Values on the right.
19. Click OK to close the Expression dialog box.
20. Click OK to close the Dataset Properties dialog box.
21. Save the VendorSubreport changes to your /StudentX folder.
    
    
9. Configure the ProductSalesRpt report to filter based on the parameter being passed in to this subreport.


Figure 61: Configuring the Subreport

22. Return to the ProductSalesRpt report in the other copy of Report Builder.
23. Right-click the subreport, and click Subreport Properties.
24. In the Subreport Properties dialog box, switch to the Parameters page.
25. On the Parameters page, click Add.
26. In the Name drop-down list, select ProductName. If the parameter is not visible in the drop-down list, return to the VendorSubreport and save the report. If this doesn’t work, you can type the parameter name. Remember, it is case sensitive.
27. In the Value drop-down list, click [Product], and then click OK.
    
    
10. Run the report and test the new subreport.
28. Save and run the ProductSalesRpt report. Expand the year 2013, and the Accessories category and notice the preferred vendors listed. Bikes are not purchased through vendors, and therefore, the subreport is blank for these products.

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.