Manage Learn to apply best practices and optimize your operations.

Transport Information Sheet Task 3: Populating a List Item

This excerpt from Chapter 5 of the book Microsoft SQL Server 2005 Reporting Services 2005 explains how to populate a list item.

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.

Populating a list item

Transport Information Sheet, Task 3:
Place a List Item on the Report and Populate It

  1. Click the Layout tab to begin working on the report layout.
  2. Select Report | Report Parameters from the menu. The Report Parameters dialog box appears. (The Report menu is only visible when the Report Designer thinks you are making changes to the report. If the Report menu is not visible, click in the body of the report and it reappears.)
  3. For the Prompt, change SerialNumber to Serial Number.
  4. Select From Query for Available Values.
  5. Select TransportSNs from the Dataset drop-down list, if it is not selected by default.
  6. Select SerialNumber from the Value Field drop-down list. Select SerialNumber from the Label Field drop-down list as well.
  7. Click OK.
  8. Move your mouse pointer to the bottom of the white report layout area, so it changes from the regular mouse pointer to the double-headed arrow, as shown in the following illustration. The white report layout area is the body of the report.

  9. Figure 30

  10. Drag the bottom of the report body down to create more room to lay out the list report.
  11. Select the Toolbox window and click the List report item. The mouse pointer changes to a list icon and crosshairs when you move your mouse pointer over the report layout area.
  12. Click-and-drag the mouse over the entire report body.
  13. When you release the mouse button, after dragging, a list is created to occupy the area you just defined.
  14. Place a text box across the top of the list (inside the list). This will be the title.
  15. As an alternative to the Properties window, font and text alignment properties can be set using the items in the Report Formatting toolbar. The Report Formatting toolbar is circled in the following illustration. Use the Report Formatting toolbar to set the properties of the textbox as follows:

    Property Value
    FontSize 16pt
    FontWeight Bold
    TextAlign Center

  16. Figure 31

  17. Click in the textbox and type Transport Information Sheet for the value of the textbox.
  18. Place a second text box under the existing title. Type Serial Number: in this text box. Size the text box so it just fits this text. This serves as the label for the Serial Number field.
  19. In the Datasets window, expand the TransportInfo dataset. Drag the SerialNumber field from the Datasets window and place it to the right of the text box that was added in Step 16. Click the white square to the right-center of the SerialNumber text box and drag it until the text box is approximately twice its original size. Your report layout should appear similar to the illustration.

  20. Figure 32

  21. Repeat this operation with each of the following fields, creating a label for the field, and then placing the field to the right of the label. (Hint: you may want to create all the labels first, and then add all the fields, so you are not switching back and forth between the Toolbox window and the Datasets window.)

    Label Field
    Purchase Date: PurchaseDate
    Transport Type: Description
    Cargo Capacity: CargoCapacity
    Range: Range
    Cost: Cost
    Crew: Crew
    Next Maint: NextMaintDate
  22. Use either the Report Formatting toolbar or the Properties window to set the properties for these fields as follows (these properties are for the fields themselves, not the labels):

    Field Property Value
    PurchaseDate Format MM/dd/yyyy
    PurchaseDate TextAlign Left
    CargoCapacity TextAlign Left
    Range TextAlign Left
    Cost Format ###,###,###.00
    Cost TextAlign Left
    Crew TextAlign Left
    NextMaintDate Format MM/dd/yyyy
    NextMaintDate TextAlign Left

    Select the Toolbox window and click on Line. Drag a line across the report layout at the bottom of the Serial Number label and the Serial Number field.

  23. Select Rectangle from the Toolbox and drag a rectangle around the unoccupied portion of the report body below the NextMaint fields.
  24. Use the Report Borders toolbar, shown here, to set the properties for the border of the Rectangle. Make sure the toolbar items are set to Solid, 1pt, and Black. Click the Outside Border toolbar button, indicated here by the mouse pointer, to create a solid, 1-point wide, black border on all sides of the rectangle.

  25. Figure 33

  26. Select Textbox from the Toolbox and place a text box in the upper-left corner of the rectangle. Type Manufacturer: in this text box. This is the manufacturer label.
  27. Drag the Manufacturer field from the Datasets window and place it inside the rectangle to the right of the manufacturer label. Size this field until it goes all the way to the right side of the rectangle. If you drag too far to the right, the Report Designer automatically increases the size of the List item and the body of the report. If this happens, simply reduce the size of the rectangle, reduce the width of the List item, and, finally, reduce the width of the body of the report.
  28. Place the ManAddr1 and ManAddr2 fields inside the rectangle, below the Manufacturer field. Make these new fields the same size as the Manufacturer field.
  29. Place a text box inside the rectangle, directly below the ManAddr2 field. Make this new text box the same size as the ManAddr2 field.
  30. Right-click in the text box added in Step 26 and select Expression from the Context menu.
  31. Select Fields(TransportInfo).
  32. Double-click the ManCity field to add it to the expression at the top of the dialog box.
  33. In the Expression area, type the following, including a space before and after each ampersand (&) character after Fields!ManCity.Value:
    & ", " &
  34. Double-click the ManState field to add it to the expression.
  35. In the Expression area, type the following, including a space before and after each ampersand (&) character after Fields!ManState.Value:
    & " " &
  36. Double-click the ManZipCode field.
  37. In the Expression area, type the following, including a space before and after each ampersand (&) character after Fields!ManZipCode.Value:
    & " " &
  38. Double-click the ManPlanetAbbrv field. Make sure no red lines are under any part of your expression indicating a syntax error. Click OK.
  39. Drag the ManEmail field from the Datasets window and place it inside the rectangle under the text box added in Step 26. Enlarge this text box. Your report layout should appear similar to this:

  40. Figure 34

  41. Click the Preview tab.
  42. Select the first serial number from the Serial Number drop-down list and click View Report. Your report should appear similar to the illustration.

  43. Figure 35

  44. Click the Save All button in the toolbar.

Task Notes As you saw in the Transport Information Sheet report, the List item enables you to place information anywhere. Text boxes, lines, and rectangles can be placed anywhere within the List item to create complex forms. This type of report is good for presenting a large amount of information about a single entity, as we did in this report.

As stated earlier, the contents of the List item are repeated for each record in the dataset. The TransportInfo dataset selects only a single record based on the user's selection of a serial number. Therefore, our report only has one page.

The Line report item is used simply to help format the report. It helps separate information on the report to make it easier for the user to understand. When working with the Table report item, we could use the borders of the text boxes in the table cells to create underlines. In the more freeform layout of the List report, the Line report item often works better than using cell borders.

The Rectangle report item serves two purposes. When its border is set to something other than None, it becomes a visible part of the report. Therefore, it can serve to help separate information on the report in the same manner as the Line report item. This is how we are using the Rectangle report item in this report.

The Rectangle report item can also be used to keep together other items in the report. We examine this use of rectangles in Chapter 7.

Getting Graphical

You have now seen three of the four data regions in action. In the next chapter, you learn about the final data region—the chart. We also look at the Image report item and its uses for adding graphics to a report. Finally, in Chapter 6, you learn about ways to control the properties of a report item using Visual Basic expressions.

Return to the first book excerpt in this series: Transport List Report

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.