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
- Click the Layout tab to begin working on the report layout.
- 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.)
- For the Prompt, change SerialNumber to Serial Number.
- Select From Query for Available Values.
- Select TransportSNs from the Dataset drop-down list, if it is not selected by default.
- Select SerialNumber from the Value Field drop-down list. Select SerialNumber from the Label Field drop-down list as well.
- Click OK.
- 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.
- Drag the bottom of the report body down to create more room to lay out the list report.
- 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.
- Click-and-drag the mouse over the entire report body.
- When you release the mouse button, after dragging, a list is created to occupy the area you just defined.
- Place a text box across the top of the list (inside the list). This will be the title.
- 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
- Click in the textbox and type Transport Information Sheet for the value of the textbox.
- 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.
- 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.
- 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
- 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.
- Select Rectangle from the Toolbox and drag a rectangle around the unoccupied portion of the report body below the NextMaint fields.
- 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.
- 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.
- 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.
- Place the ManAddr1 and ManAddr2 fields inside the rectangle, below the Manufacturer field. Make these new fields the same size as the Manufacturer field.
- Place a text box inside the rectangle, directly below the ManAddr2 field. Make this new text box the same size as the ManAddr2 field.
- Right-click in the text box added in Step 26 and select Expression from the Context menu.
- Select Fields(TransportInfo).
- Double-click the ManCity field to add it to the expression at the top of the dialog box.
- In the Expression area, type the following, including a space before and after each ampersand (&) character after Fields!ManCity.Value:
& ", " &
- Double-click the ManState field to add it to the expression.
- In the Expression area, type the following, including a space before and after each ampersand (&) character after Fields!ManState.Value:
& " " &
- Double-click the ManZipCode field.
- In the Expression area, type the following, including a space before and after each ampersand (&) character after Fields!ManZipCode.Value:
& " " &
- Double-click the ManPlanetAbbrv field. Make sure no red lines are under any part of your expression indicating a syntax error. Click OK.
- 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:
- Click the Preview tab.
- Select the first serial number from the Serial Number drop-down list and click View Report. Your report should appear similar to the illustration.
- 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.
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
This was first published in February 2006