Data Regions

The table item is one of four special report items designed specifically for working with datasets. These special report items are called data regions. The other data regions are the matrix, the list, and the chart.

Data regions are able to work with multiple records from a dataset. The data region reads a record from the dataset, creates a portion of the report using the data found in that record, and then moves on to the next record. It does this until all the records from the dataset have been processed.

In the report you just completed, you saw how the table data region creates a detail row for each record in the dataset. The matrix data region creates both rows and columns based on the contents of the dataset. You see this demonstrated in our next report. The list data region is not limited to rows and columns. It creates a whole section, perhaps a whole page, for each record in the dataset. We create a report using a list data region later in this chapter. The chart data region creates elements on a graph for each record in a dataset. We create a report using a chart data region in Chapter 6.

Each data region item has a property called DataSetName. This property contains the name of the dataset used by the data region. In the Transport List report you just created, the DataSetName property of the table has the value TransportList (see the following illustration). Visual Basic automatically set this property for you when you placed the first field, the SerialNumber field, in the table. Because the SerialNumber field is from the TransportList dataset and because the table’s DataSetName property was empty, Visual Basic put the value TransportList into the DataSetName property.

Now let’s move down the road a little further and create a matrix report without the wizard.

The Repair Count By Type Report

Features Highlighted

• Creating a matrix report from scratch

• Using a specialized property dialog box

Business Need   GDS needs to purchase several new transports to update their delivery fleet. The company must decide which type of transport to purchase. One factor in the decision is the amount of time the new transports will spend in the maintenance hanger for repairs and preventative maintenance.

Upper management has asked the GDS maintenance department to provide a report showing the number of each type of repair required by each type of transport. The report should include statistics from all transports, both active and retired. Also, the report should group the repairs by their cause.

Task Overview

1. Reopen the Chapter05 Project, Create a New Report in the Chapter05 Project, Select the Shared Data Source, and Create a Dataset

2. Place a Matrix Item on the Report and Populate It

3. Add Column Grouping and Other Report Formatting

Repair Count By Type Report, Task 1: Reopen the Chapter05 Project, Create a New Report in the Chapter05 Project, Select the Shared Data Source, and Create a Dataset

1. If you closed the Chapter05 project, reopen it.

2. In the Solution Explorer on the right side of the screen, right-click the Reports folder.

3. Put your mouse pointer over Add in the Context menu and wait for the submenu to appear. Select New Item from the Context menu. This displays the Add New Item Chapter05 dialog box.

4. Make sure the Report icon is selected in the Templates area. Enter RepairCountByType for the name.

5. Click Add. A new report called RepairCountByType.rdl is created in the Chapter05 project. You are taken to the Data tab of this new report.

6. Select <New Dateset…> from the Dataset drop-down list. The Dataset dialog box appears.

7. Enter RepairsByType for the name in the Dataset dialog box.

8. Galactic (shared) is selected for the data source by default. Click OK. You return to the Data tab, which now displays the Generic Query Designer.

9. Click the Generic Query Designer button to switch to the Graphical Query Designer.

10. Right-click in the diagram pane of the Graphical Query Designer screen. Select Add Table from the Context menu.

11. Add the following tables to the query:

    Repair (dbo)

    Transport (dbo)

    TransportType (dbo)

    RepairWorkDoneLink (dbo)

    WorkDone (dbo)

    RepairCause (dbo)

12. Click Close to exit the Add Table dialog box.

13. Check the following column in the Repair table:

    RepairID

14. Check the following column in the TransportType table:

    Description

15. In the criteria pane, type TypeOf Transport in the Alias column in the Description row.

16. Check the following column in the WorkDone table:

    Description

17. In the criteria pane, type TypeOf Work in the Alias column in the Description row for the WorkDone table.

18. Check the following column in the RepairCause table:

    Description

19. In the criteria pane, type RepairCause in the Alias column in the Description row for the RepairCause table.

20. Type 1 in the Sort Order column for RepairCause. Type 2 in the Sort Order column for TypeOf Work.

21. Right-click in the SQL pane and select Execute SQL from the Context menu. The Graphical Query Designer should appear similar to this:

    

Task Notes   Although this report is a pretty straightforward request, we need to link together a number of tables to collect the necessary data. What we are interested in is repairs, so we start with the Repair table. However, none of the fields we need in the result set are in the Repair table. To find the type of transport being repaired, we need to join the Transport table with the Repair table, and then join the TransportType table to the Transport table. To find the type of work done, we need to join the RepairWorkDoneLink table to the Repair table, and then join the WorkDone table to the RepairWorkDoneLink table. Finally, to group by the cause of the repair, we need to join the RepairCause table to the Repair table. If you get confused by all of this, refer to Figure 3–23 in Chapter 3.

Repair Count By Type Report, Task 2: Place a Matrix Item on the Report and Populate It

1. Click the Layout tab to begin working on the report layout.

2. Click the Matrix report item in the Toolbox. The mouse pointer changes to a matrix icon and crosshairs when you move your mouse pointer over the report layout area.

3. Click-and-drag the mouse over the lower three-quarters of the report layout.

4. When you release the mouse button, after dragging, a matrix is created to occupy the area you just defined. By default, every cell in the matrix is occupied by an empty text box.

5. In the Datasets window, expand the RepairsByType dataset. Drag the TypeOfTransport field from the Datasets window and drop it on the cell containing the word “Columns.” The values in this column in the dataset determine the columns in the matrix report.

6. Drag the TypeOfWork field from the Datasets window and drop it on the cell containing the word “Rows.” The values in this column in the dataset determine the rows in the matrix report.

7. Drag the RepairID field from the Datasets window and drop it on the cell containing the word “Data.”

8. In the cell where you just dropped the RepairID field, change Sum to Count, so the contents of the cell appear as follows:

    =Count(Fields!RepairlD.Value)

9. With this cell still selected, change the following property:

   Property	New Value
   TextAlign	Center

10. Reduce the width and height of the columns in the matrix. When you finish, your report design should look similar to this:

    

11. Click the Preview tab. Your report should look similar to the following illustration. The rows and columns in your report may appear in a different order from those shown here.

    

Task Notes   Because the matrix report always groups a number of records from the dataset to create the entries in the matrix, the field that supplies the data for the matrix must be enclosed in some type of aggregate function. If the field placed in the data cell is a number, Report Designer encloses the field in the SUM() aggregate function.

The RepairID field, which we placed in the data cell in Step 7, is a number. However, it does not make sense to add up the RepairIDs. Instead, we want to count the number of RepairIDs. For this reason, we changed the SUM() aggregate function to the COUNT() aggregate function.

Repair Count By Type Report, Task 3: Add Column Grouping and Other Report Formatting

1. Click the Layout tab to return to the report layout.

2. Click the cell in the upper-right corner of the matrix and change the following properties:

   Property	New Value
   FontWeight	Bold
   TextDecoration	Underline

3. Click the square in the upper-left corner of the gray border to select the matrix item.

4. In the Properties window, click the Property Pages button shown in the following illustration. The Matrix Properties dialog box appears.

   

5. Click the Groups tab.

6. In the Rows area, click Add. The Grouping and Sorting Properties dialog box appears.

7. Next you set up your matrix for drilldown. Replace matrix 1_RowGroup2 with matrix1_RepairCause for the name. Select Fields !RepairCause.Value from the drop-down list in the first row under Expression. Click OK.

8. In the Rows area, click Up to move matrix l_RepairCause to the top of the list. Click the matrix1_TypeOfWork entry. Click Edit in the Rows area. The Grouping and Sorting Properties dialog box appears.

9. Click the Visibility tab. Set the Initial Visibility to Hidden. Click the Visibility Can Be Toggled by Another Report Item check box. Select textbox2 from the Report Item drop-down list. (If textbox2 is not in the drop-down list, type textbox2 for the Report item value.)

10. Click OK in the Grouping and Sorting Properties dialog box. Click OK in the Matrix Properties dialog box.

11. Click the cell in the upper-left corner of the matrix and change the following properties:

    Property	New Value
    FontWeight	Bold
    TextDecoration	Underline
    Value	Cause/Type of Repair Work

12. Click the Textbox report item in the Toolbox. Click-and-drag the mouse over the area above the matrix on the report layout area. When you release the mouse button, after dragging, a text box is created to occupy the area you just defined. Click the text box and type the following:

     Repair Count By Type Report

13. With the text box still selected, make the following changes in the Properties window:

    Property	New Value
    FontSize	16pt
    FontWeight	Bold
    TextAlign	Center

14. Your report layout should appear similar to the illustration.

    

15. Click the Preview tab. The report should appear as follows.

    Note 

    If the report displays an error message mentioning textbox2 when you preview the report, then textbox2 was not the name assigned to the text box containing the RepairCause group label. Return to the Layout tab and select the text box containing the =Fields!RepairCause.Value expression. Look at the Name property in the Properties window. Return to the drop-down list you populated in Step 9 of this task and replace textbox2 with this name. Click OK to exit the dialog boxes and preview the report again.

    

16. Click Save All in the toolbar.

Task Notes   The Property Pages button in the Properties window provides an alternative way to change the properties of a report item. This button displays a dialog box that deals specifically with the properties of the selected report item. These specialized property dialog boxes can make it much easier to modify the properties of a report item. You can also access the specialized property dialog boxes by right-clicking a report item and selecting Properties from the Context menu.