Transport List Report Task 4: Add Table Grouping

This excerpt from Chapter 5 of the book Microsoft SQL Server 2005 Reporting Services 2005 explains how to add table grouping and other report formatting.

Microsoft SQL Server 2005: The Complete Reference The following excerpt, courtesy of McGraw-Hill Osborne Media, is from Chapter 5 of the book Microsoft SQL Server 2005 Reporting Services 2005 written by Brian Larson. Click here to purchase the book.



Table grouping

Transport List Report, Task 4:
Add Table Grouping and Other Report Formatting

  1. Click the Layout tab.
  2. Right-click the gray border to the left of the table. You may need to click on the table to get the borders to appear. Select Insert Group from the Context menu. The Grouping and Sorting Properties dialog box appears.
  3. Enter TransportType for the name (no spaces are allowed in the Name field).
  4. Select =Fields!Description.Value from the drop-down list in the first row under Expression. You have to click in this cell to get the drop-down list to appear.
  5. Uncheck the Include Group Footer check box.
  6. Click OK. A new blank row is added to the table below the header row. This is the grouping row.
  7. Click the leftmost cell in the grouping row. Hold down SHIFT, and then click the center and the rightmost cells in the grouping row. Right-click in any of the selected cells and select Merge Cells from the Context menu, as shown here.

  8. Figure 15

  9. Right-click in the newly merged field and select Expression from the Context menu. The Edit Expression dialog box appears.
  10. Type the following after the equals sign (=), including the quotation marks and the space after the ampersand, in the Expression area:
  11.  "Transport Type: " &
  12. Select the Fields (TransportList) entry in the tree view as shown here. Note, the fields in the TransportList dataset appear in the lower-right list box.

  13. Figure 16

  14. Double-click the Description field to append it to the expression in the Expression area.
  15. CAUTION

    If you type the field expression, rather than selecting it from the Fields area, it must be typed in the exact case shown in the Fields area. Fields, as well as parameters, are case-sensitive when used in expressions.

  16. Type the following at the end of the expression in the Expression area:
     & vbCrLf & " Cargo Capacity: " & 

    A space must be before and after each ampersand (&) character.

  17. Double-click the CargoCapacity field to append it to the expression in the Expression area.
  18. Type the following at the end of the expression in the Expression area:
     & " Range: " & 

    A space must be before and after each ampersand (&) character.

  19. Double-click the Range field to append it to the expression in the Expression area. The Edit Expression dialog box should appear.

  20. Figure 17

  21. Click OK.
  22. With the merged field still selected, make the following changes in the Properties window:

    Property New Value
    BorderStyle/Bottom (expand the BorderStyle property to find the Bottom property) Solid
    FontWeight Bold
  23. Click the Textbox report item in the Toolbox. The mouse pointer changes to a text box icon and crosshairs when you move your mouse pointer over the report layout area.
  24. Click-and-drag the mouse over the entire area above the table on the report layout area. Note, when you begin dragging, the mouse pointer changes back to the usual arrow icon.
  25. 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:
    Transport List
  26. With the text box still selected, make the following changes in the Properties window:

    Property New Value
    FontSize 16pt
    FontWeight Bold
    TextAlign Center
  27. Click the Preview tab. The report should appear as shown here.

  28. Figure 18

  29. Click Save All in the toolbar.

Task Notes When we added the grouping, we specified an expression in Step 4. This group expression determines when a new group header is placed in the report. In the Transport List report, we used the Description field from the TransportType table in the group expression. Because our first sort in the dataset was on the TransportType.Description column, all the Photon III transports came first in the dataset, followed by the StarLifter transports, and, finally, the Warp Hauler transports. Each time the value of the group expression changes, a new group header is added to the report.

Be sure you do not confuse the grouping in the report with the GROUP BY clause we used in SQL SELECT statements. The SQL GROUP BY clause takes a number of records and combines them into a single record in the result set. The grouping in the report takes a number of records in the dataset and surrounds them with a group header and/or group footer when they are output in the report.

In Steps 9–15, we combined all the fields that need to be in the group header into one expression. This was done so we could create a multiline group header, and also to concatenate or combine the labels (Transport Type:, Cargo Capacity:, and Range:) and the contents of the three fields (Description, CargoCapacity, and Range) into one string. The three columns of the group header were merged together to create room for the resulting expression. The Visual Basic concatenation operator (&) is used to combine the values into one long string. The Visual Basic constant vbCrLf is used to put a carriage return and linefeed in the middle of the string. This causes everything following the carriage return and linefeed to be placed on the next line down, giving us a two-line group header.

Remember, table cells are always occupied by a report item. If no other report item has been placed in a cell, the cell is occupied by a text box. When multiple cells are merged, the report item in the leftmost cell expands to fill the merged table cell. The report items in the other cells involved in the merge are automatically deleted.

We created a border on the bottom of the text box in the merged cells to underline our group heading. This is easier and more efficient than adding a Line report item to the report to get the same result. This is especially true when you are trying to underline something in the middle of a table, such as our group header.

When you typed the text in Step 20, it looked like you were entering the text directly into the text box. What you were doing is changing the Value property of the text box. You can change the Value property of a text box by typing directly into the text box in the report layout area or by using the Properties window.

In addition, the Edit Expression dialog box can be used to change the Value property of a text box, as well as many other report item properties. In Step 8, we used the Context menu to bring up the Edit Expression dialog box. The Edit Expression dialog box can also be accessed through a drop-down list in the Properties window, as this illustration shows. In addition to the Value property of the text box, the Edit Expression dialog box can be used to change a number of properties of various report items. We discuss this in more detail in Chapter 7.


Figure 19

You probably noticed a red, jagged line that appears occasionally below the expression as you typed it in the Edit Expression dialog box. If you have ever used Microsoft Word, then you know this means something is wrong with the text you have typed. In Word, this red line indicates a spelling error. In the Edit Expression dialog box, this means a problem exists with the syntax of your expression. Hovering over the red line provides you with a brief description of the problem.

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.


Figure 20

Click for the next excerpt in this series: Repair Count By Type Report

This was first published in February 2006

Dig deeper on Microsoft SQL Server Reporting Services (SSRS)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close