Reports Within Reports

Thus far, we have placed report items within report items and data regions within data regions. In this section, we look at putting whole reports inside one another. This is done using the subreport report item; the only item in the Toolbox that we have not yet used.

The subreport item is simply a placeholder in a report. It sits in the parent report and shows the space to be occupied by another report when the parent report is run. Nothing is special about a report placed in a subreport item. Any report can be used as a subreport.

The report placed in the subreport can even contain parameters. These parameter values can be passed from the parent report to the subreport. Any field value, parameter value, or expression in the parent report can be used as a parameter in the subreport.

Subreports are used for many reasons. They can provide an easy way to reuse a complex report layout within a parent report. They can also be used to implement a more complex form of drilldown.

The following subreports are anything but subpar!

The Employee Evaluation Report

Features Highlighted

• Using a subreport as reusable code

• Using the page width and page height properties for a landscape report

• Using a rectangle for grouping

Business Need   The Galactic Delivery Services personnel department has created an application for employees to conduct peer reviews as part of each employee’s annual review process. They are also collecting a review and comments from each employee’s manager. They need a report that can be used to present the results of the peer review at the employee’s meeting with their supervisor.

The manager’s review and comments should be noted as coming from the manager. The peer reviews, however, should be presented anonymously.

Task Overview

1. Create a New Report, Create a Dataset, Add a Table to the Report Layout, and Populate It

2. Create a New Report, Create a Dataset, and Populate the Report Layout

3. Add a Rectangle

Employee Evaluation Report, Task 1: Create a New Report, Create a Dataset, Add a Table to the Report Layout, and Populate It

1. Reopen the Chapter08 project if it was closed.

2. Create a new report called EvalDetail. Do not use the GDSReport template.

3. Create a new dataset called EvalRatings that calls the stp_EvalRatings stored procedure.

4. Select the Layout tab.

5. Place a table onto the body of the report.

6. Place the Goal, Rating, and GoalComment fields in the details row of the table.

7. Add a group to the table using EvaluatorEmployeeNumber as the grouping expression.

8. Complete your report layout so it is similar to Figure 8–15. The top row has the BorderStyle: Top property set to Solid. The bottom row has the BorderStyle: Bottom property set to Solid and the BorderWidth: Bottom property set to 5pt. Also note that the table header and table footer have been turned off.

   
   Figure 8–15: The Employee Evaluation Detail Report layout

9. Select Save All from the toolbar.

Task Notes   The EvalDetail report is going to be used in two subreports in our parent report. It is going to be used in one location to display the peer reviews and in another location to display the manager review. We can create this layout for displaying review information, and then use it in multiple places.

Employee Evaluation Report, Task 2: Create a New Report, Create a Dataset, and Populate the Report Layout

1. Create a new report called EmployeeEval using the GDSReport template.

2. Create a new dataset called EvalPerformance that calls the stp_EvalPerformance stored procedure.

3. Select the Layout tab.

4. Modify the following properties of the report:

   Property	Value
   InteractiveSize: Width	11 in
   InteractiveSize: Height	8.5in
   PageSize: Width	11in
   PageSize: Height	8.5in

   This creates a landscape page layout, rather than a portrait page layout.

5. Drag the right edge of the report body until it is 10 inches wide. Use the ruler at the top of the layout area as a guide.

6. Drag the EmployeeName field onto the report body. Modify the following properties of the text box that results:

   Property	Value
   Font: FontSize	20pt
   Font: FontWeight	Bold
   Location: Left	0in
   Location: Top	0in
   Size: Width	6.875in
   Size: Height	0.5in

7. Place a text box onto the report body. Modify the following properties of this text box:

   Property	Value
   Font: FontSize	20pt
   Font: FontWeight	Bold
   Location: Left	8.25in
   Location: Top	0in
   Size: Width	1.625in
   Size: Height	0.5in
   Value	=Parameters!Year.Value

8. Place a text box onto the report body. Modify the following properties of this text box:

   Property	Value
   Font: FontSize	16pt
   Font: FontWeight	Bold
   Location: Left	0in
   Location: Top	0.625in
   Size: Width	2in
   Size: Height	0.375in
   Value	Peer Evaluations

9. Place a subreport onto the report body immediately below the text box. Modify the following properties of this subreport:

   Property	Value
   Location: Left	0in
   Location: Top	1in
   Size: Width	6.875in
   Size: Height	1.125in

10. Right-click the subreport and select Properties from the Context menu. The Subreport Properties dialog box appears.

11. Select EvalDetail from the Subreport drop-down list.

12. Select the Parameters tab.

13. Configure the parameters as shown here:

    Parameter Name	Parameter Value
    EmpNum	=Parameters!EmpNum.Value
    Year	=Parameters!Year.Value
    MgrFlag	=0

    Remember to use the Edit Expression dialog box to select the parameter values.

14. Click OK to exit the Subreport Properties dialog box.

15. Select the Peer Evaluations text box and the subreport. Press CTRL-C to copy these two items. Press CTRL-V to paste a copy of these items on the report body. Drag the two copied items, so they are immediately below the original subreport.

16. Modify the new text box to read “Manager Evaluation.” Adjust the width of the text box as needed.

17. Open the Subreport Properties dialog box for the new subreport and select the Parameters tab.

18. Change the parameter value for MgrFlag from =0 to =1. This causes the second subreport to contain the manager’s evaluation rather than the peer evaluations.

19. Click OK to exit the Subreport Properties dialog box.

20. Place a text box onto the report body. Modify the following properties of this text box:

    Property	Value
    Font: FontWeight	Bold
    Location: Left	7.125in
    Location: Top	1in
    Size: Width	2in
    Size: Height	0.25in
    Value	Areas of Excellence

21. Drag the AreasOfExcellence field onto the report body. Modify the following properties of the text box that results:

    Property	Value
    Location: Left	7.125in
    Location: Top	1.375in
    Size: Width	2.75in
    Size: Height	0.25in

22. Place a text box onto the report body. Modify the following properties of this text box:

    Property	Value
    Font: FontWeight	Bold
    Location: Left	7.125in
    Location: Top	1.875in
    Size: Width	2in
    Size: Height	0.25in
    Value	Areas for Improvement

23. Drag the AreasForImprovement field onto the report body. Modify the following properties of the text box that results:

    Property	Value
    Location: Left	7.125in
    Location: Top	2.25in
    Size: Width	2.75in
    Size: Height	0.25in

24. Select the Preview tab. Enter 1394 for EmpNum and 2005 for Year, and then click View Report. Your report should appear similar to Figure 8–16.

    
    Figure 8–16: The Employee Evaluation Report preview after Task 2

Task Notes   We used the InteractiveSize and PageSize properties of this report to change its orientation from portrait to landscape. When you are creating your report templates, you may want to create one template for portrait reports with the default InteractiveSize and PageSize values, and another report template for landscape reports with the InteractiveSize and PageSize values used in this report. The InteractiveSize parameter controls the dimensions of the report when it is viewed interactively. The PageSize parameter controls the dimensions of the report when it is printed.

Two steps are required to get each subreport item ready to use. First, you have to specify which report is going to be used within the subreport item. Once this is done, you need to specify a value for each of the parameters in the selected report. With these two tasks completed, your subreports are ready to go.

In this report, we are using several fields outside of a data region: the EmployeeName field, the AreasOfExcellence field, and the AreasForImprovement field. Remember, data regions are set up to repeat a portion of their content for each record in the result set. When a field value occurs outside of a data region, it is not repeated; it occurs only once. Therefore, the field value must be put inside an aggregate function to determine how to get one value from the many records in the result set. The First() aggregate function is chosen by default.

In this particular report, the EvalPerformance dataset has only one record. Of course, the Report Designer does not know at design time how many records the dataset will have at run time. (Even if the dataset has only one record at design time, it could have 100 records at run time.) Therefore, the Report Designer insists on the aggregate function for this field value.

Finally, you may have noticed a little problem with the text box that contains the contents of the AreasForImprovement field. It seems to be sliding down the page. In actuality, it was pushed down the page when the subreport grew.

The text boxes that contain the Areas of Excellence title, the AreasOfExcellence field value, and the Areas for Improvement title are all even with the first subreport. However, the text box containing the value of the AreasForImprovement field starts below the bottom of the first subreport. When the subreport grows because of its content, the text box is pushed further down the report, so it remains below the bottom of the subreport.

In Task 3, you see a way to prevent this problem.

Employee Evaluation Report, Task 3: Add a Rectangle

1. Select the Layout tab.

2. Select the Areas of Excellence text box, the AreasOf Excellence field value text box, the Areas for Improvement text box, and the AreasForImprovement field value text box. Press CTRL-X to cut these four text boxes.

3. Select a rectangle from the Toolbox and place it in the area just vacated by these four text boxes.

4. With the rectangle still selected, press CTRL-V to paste the four text boxes into the rectangle.

5. Arrange the rectangle and the four text boxes as needed. Your layout should appear similar to Figure 8–17.

   
   Figure 8–17: The Employee Evaluation Report layout with a rectangle

6. Select the Preview tab. Enter 1394 for EmpNum and 2005 for Year, and then click View Report. Your report should appear similar to Figure 8–18.

   
   Figure 8–18: The Employee Evaluation Report preview with a rectangle

7. Select Save All from the toolbar.

Task Notes   The rectangle report item comes to your rescue here. Once the four text boxes are inside the rectangle, they remain together no matter how much the subreport grows. As your report designs become more complex, rectangles are often necessary to keep things right where you want them.

The Invoice Report

Features Highlighted

• Using a subreport in a table

• Using a subreport to facilitate drilldown

Business Need   The Galactic Delivery Services accounting department wants an interactive Invoice Report. The Invoice Report needs to show the invoice header and invoice detail information. The user can then expand an invoice detail entry to view information on the delivery that created that invoice detail.

Task Overview

1. Create a New Report, Create a Dataset, and Copy the Layout from the DeliveryStatus Report

2. Create a New Report, Create a Dataset, and Populate the Report Layout

Invoice Report, Task 1: Create a New Report, Create a Dataset, and Copy the Layout from the DeliveryStatus Report

1. Reopen the Chapter08 project if it was closed.

2. Create a new report called DeliveryDetail. Do not use the GDSReport template.

3. Create a new dataset called DeliveryStatus that calls the stp_DeliveryDetail stored procedure.

4. Select the Layout tab.

5. Double-click the entry for the DeliveryStatus report in the Solution Explorer to open the DeliveryStatus report.

6. Select the table in the DeliveryStatus report and press CTRL-C to copy it. (Make sure you have the entire table selected and not just a single cell in the table.)

7. Close the DeliveryStatus report and return to the DeliveryDetail report.

8. Press CTRL-V to paste the table into the report body.

9. Move the table to the upper-left corner of the report body. Size the report body, so it exactly contains the table.

Task Notes   Instead of re-creating a layout for the delivery detail, we borrowed a layout created previously in another report. This works because the stp_DeliveryDetail stored procedure returns the same columns as the stp_DeliveryStatus stored procedure used for the previous report. The other requirement needed to make this cut-and-paste operation successful was to use the same name for the dataset in both reports.

When you have a layout that is nice and clean, reusing it whenever possible is always a good idea. Even better would be to modify the DeliveryStatus report to use our new DeliveryDetail report in a subreport. That way, we would only need to maintain this layout in one location.

Consider that an extra credit project.

Invoice Report, Task 2: Create a New Report, Create a Dataset, and Populate the Report Layout

1. Create a new report called Invoice using the GDSReport template.

2. Create a new dataset called InvoiceHeader that calls the stp_InvoiceHeader stored procedure.

3. Create a second dataset called InvoiceDetail that calls the stp_InvoiceDetail stored procedure.

4. Select the Layout tab.

5. Place a list onto the report body.

6. Size the list and add fields and text boxes to create the layout shown in Figure 8–19. The fields come from the InvoiceHeader dataset. The black line across the bottom is a solid bottom border on the list item with a border width of 10 points.

   
   Figure 8–19: The Invoice Report layout with on invoice header

7. Drag the report body to make it larger.

8. Place a table onto the report body immediately below the list.

9. Drag the LineNumber, Description, and Amount fields from the InvoiceDetail dataset into the details row of the table.

10. Size the table columns appropriately. Type the letter C for the Format property of the text box containing the Amount field value.

11. Turn off the table header and table footer.

12. Add a second details row below the existing details row.

13. Merge the three cells in this new details row.

14. Place a subreport in the merged cell.

15. Open the Subreport Properties dialog box. Set the subreport to DeliveryDetail.

16. Select the Parameters tab and configure it as follows:

    Parameter Name	Parameter Value
    DeliveryNumber	=Fields!DeliveryNumber.Value

17. Click OK to exit the Subreport Parameters dialog box.

18. Click the gray box to the left of the row containing the subreport. Modify the following properties for this table row using the Properties window:

    Property	Value
    Visibility: Hidden	True
    Visibility: ToggleItem	LineNumber

19. Select the Preview tab. Type 73054 for InvoiceNumber and click View Report.

20. Expand one of the invoice detail entries and observe how the subreport appears.

21. You can widen the report body, list, and table so the report layout does not expand when the subreport appears. Your report should appear as shown in Figure 8–20.

    
    Figure 8–20: The Invoice Report preview

22. Select Save All from the toolbar.

Task Notes   In the Invoice Report, we placed our subreport right in a table cell. A field from the table’s dataset is used as the parameter for the subreport. Because of this, the subreport is different for each details row in the table.

We chose to have the subreport initially hidden in our report. The reason for this is the subreport contains a large amount of detail information. This detail would overwhelm the users if it were displayed all at once. Instead, the users can selectively drill down to the detail they need.

In our next report, you look at another way to manage large amounts of detail by using the drill-through feature of Reporting Services.