New Territory | MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Now that you have created the table and matrix reports without the aid of the Report Wizard, it is time to venture into new territory. As mentioned previously, the list item is the third type of data region. Just as the table item makes up the main portion of a table report and the matrix item makes up the main portion of a matrix report, the list item is the main part of a list report.

List reports are used when you need to repeat a large area of content—perhaps even an entire page—for each record in the dataset. They are often used to create forms. List reports function similarly to a mail merge in a word processing program such as Microsoft Word.

The Transport Information Sheet

Feature Highlighted

Creating a list report

Business Need The GDS maintenance department needs an efficient way to look up general information about a particular transport that comes in for repair. The user should be able to select the serial number from a drop-down list and see all the basic information about the transport. This transport information sheet should also include the date of the next scheduled maintenance appointment for this transport.

Task Overview

Reopen the Chapter05 Project, Create a New Report in the Chapter05 Project, Select the Shared Data Source, and Create the TransportSNs Dataset
Create the TransportInfo Dataset
Place a List Item on the Report and Populate It

Transport Information Sheet, Task 1: Reopen the Chapter05 Project, Create a New Report in the Chapter05 Project, Select the Shared Data Source, and Create the TransportSNs Dataset

If you closed the Chapter05 project, reopen it.
In the Solution Explorer on the right side of the screen, right-click the Reports folder. Select Add|New Item. This displays the Add New Item Chapter05 dialog box.
Make sure the Report icon is selected in the Templates area. Enter TransportInfoSheet for the name. Click Add.
Select <New Dateset…> from the Dataset drop-down list. The Dataset dialog box appears.
Enter TransportSNs for the name in the Dataset dialog box.
The data source should be Galactic (shared).

Enter the following for the query string:

 SELECT SerialNumber FROM Transport WHERE RetiredDate IS NULL ORDER BY SerialNumber

Click OK.
Click the Generic Query Designer button to switch to the Graphical Query Designer.
Right-click in the SQL pane and select Execute SQL from the Context menu. The bottom two panes of the Graphical Query Designer should appear similar to the illustration.

Task Notes The TransportSNs dataset provides a list of the serial numbers for all the active transports at GDS. This dataset is used to populate the drop-down list from which the user selects the transport for which the Transport Information Sheet will be printed. Because the query for this dataset is relatively straightforward, it is faster to type the query string by hand rather than build it using the Graphical Query Designer.

This is not the case with the query string for the second dataset required by this report, as you shall see in the next task.

Transport Information Sheet, Task 2: Create the TransportInfo Dataset

Select <New Dateset…> from the Dataset drop-down list. The Dataset dialog box appears.
Enter TransportInfo for the name in the Dataset dialog box. The data source should be Galactic. Click OK.
Click the Generic Query Designer button to switch to the Graphical Query Designer.
Right-click in the diagram pane of the Graphical Query Designer screen. Select Add Table from the Context menu. Add the following tables to the query:

Transport (dbo)

TransportType (dbo)

ScheduledMaint (dbo)

Repair (dbo)
Click Close to exit the Add Table dialog box.
Right-click the link between the Transport and the Repair tables, and then select Remove from the Context menu. (You may have to rearrange the tables in the diagram pane to make this visible.)
Right-click the diamond in the middle of the link between the Repair table and the ScheduledMaint table. Select the command Select All Rows from ScheduledMaint in the Context menu.
Find the diamond in the middle of the link between the Transport and ScheduledMaint tables. (You may have to rearrange the tables in the diagram pane to make this visible.) Right-click this diamond and choose Select All Rows from Transport from the Context menu. With a bit of rearranging, your screen should look similar to the illustration.
Check the following columns in the Transport table:

SerialNumber

PurchaseDate
Check the following columns in the TransportType table:

Description

CargoCapacity

Range

Cost

Crew

Manufacturer

ManAddr1

ManAddr2

ManCity

ManState

ManZipCode

ManPlanetAbbrv

ManEmail
Check the following column in the ScheduledMaint table:

ScheduledDate
Check the following column in the Repair table:

RepairID
In the criteria pane, type the following in the Filter column for SerialNumber:
```
 = @SerialNumber
```
In the Filter column for RepairID, type this:
```
 IS NULL
```
Right-click in the diagram pane and select Add Group By from the Context menu.
In the criteria pane, in the Group By column for ScheduledDate, select Min from the drop-down list.
In the Alias column for ScheduledDate, change Expr1 to NextMaintDate.
Right-click in the SQL pane and select Execute SQL from the Context menu. Enter 3809393848 for the @SerialNumber parameter and click OK. The Graphical Query Designer should appear similar to the next illustration.

image from book

Task Notes The TransportInfo dataset must include all the information about a selected transport. This is not complicated, except for the last item noted in the business need for this report: the date of the next scheduled maintenance for this transport. You need a little background on the way the Galactic database functions regarding scheduled maintenance to understand this query.

Records are added to the ScheduledMaint table for each time a transport needs to come into a maintenance facility for preventative maintenance. These are considered appointments for preventative maintenance. They are scheduled for dates in the future. Transports may have more than one pending preventative maintenance appointment. The ScheduledMaint table records are linked to a transport by the TransportNumber field.

When a transport comes in for preventative maintenance, a record is added to the Repair table. This indicates an appointment for preventative maintenance has been fulfilled. The record in the Repair table is linked to the record in the ScheduledMaint table by the ScheduledMaintID field. If a scheduled appointment is missed, the appointment is rescheduled by changing the value in the ScheduledMaint.ScheduleDate field to a value in the future.

Given these business rules, records in the ScheduledMaint table for a given transport that do not have corresponding records in the Repair table represent pending preventative maintenance appointments. The record that has the minimum value in the ScheduledDate field represents the next appointment. To find this record, we are joining the ScheduledMaint table to the Repair table using a left outer join. Because we require the RepairID to be NULL, our result set only includes the pending appointments (that is, the records in the ScheduledMaint table that do not have a matching record in the Repair table).

Because a transport may have more than one pending appointment, we could end up with more than one record for a given transport. We need to use GROUP BY to consolidate these into one record. The MIN() aggregate function is used to find the ScheduledDate field with the lowest value (that is, the next scheduled appointment).

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.

Property	Value
FontSize	16pt
FontWeight	Bold
TextAlign	Center

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 Transporting) 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.