Transport Information Sheet Task 1 and 2: Create New Report and Dataset

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.

Transport information sheet: Create a new report

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

1. Reopen the Chapter05 Project, Create a New Report in the Chapter05 Project, Select the Shared Data Source, and Create the TransportSNs Dataset
2. Create the TransportInfo Dataset
li>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

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. Select Add | New Item. This displays the Add New Item Chapter05 dialog box.
3. Make sure the Report icon is selected in the Templates area. Enter TransportInfoSheet for the name. Click Add.
4. Select from the Dataset drop-down list. The Dataset dialog box appears.
5. Enter TransportSNs for the name in the Dataset dialog box.
6. The data source should be Galactic (shared).
7. Enter the following for the query string:

   SELECT SerialNumber FROM Transport WHERE RetiredDate IS NULL ORDER BY
   SerialNumber
   

8. Click OK.
9. Click the Generic Query Designer button to switch to the Graphical Query Designer.
10. 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.

Figure 27

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

1. Select from the Dataset drop-down list. The Dataset dialog box appears.
2. Enter TransportInfo for the name in the Dataset dialog box. The data source should be Galactic. Click OK.
3. Click the Generic Query Designer button to switch to the Graphical Query Designer.
4. 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)
   

5. Click Close to exit the Add Table dialog box.
6. 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.)
7. 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.
8. 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.


Figure 28

9. Check the following columns in the Transport table:

   SerialNumber
   PurchaseDate
   

10. Check the following columns in the TransportType table:

    Description
    CargoCapacity
    Range
    Cost
    Crew
    Manufacturer
    ManAddr1
    ManAddr2
    ManCity
    ManState
    ManZipCode
    ManPlanetAbbrv
    ManEmail
    

11. Check the following column in the ScheduledMaint table:
    ScheduledDate
12. Check the following column in the Repair table:
    RepairID
13. In the criteria pane, type the following in the Filter column for SerialNumber:

    = @SerialNumber
    

14. In the Filter column for RepairID, type this:

    IS NULL
    

15. Right-click in the diagram pane and select Add Group By from the Context menu.
16. In the criteria pane, in the Group By column for ScheduledDate, select Min from the drop-down list.
17. In the Alias column for ScheduledDate, change Expr1 to NextMaintDate.
18. 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.

Figure 29

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

Click for the next excerpt in this series: Transport Information Sheet Task 3: Populating a List Item

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL Server Business Intelligence (BI) and Data Warehousing DBA career paths could lead to business intelligence Are data warehouses made for the cloud? Q&A: Business intelligence gets a facelift in SQL Server 2008 R2 Project Gemini gets a new name, Madison earns buzz Speed up reports in SQL Server Reporting Services with caching Data Transformation Services vs. SSIS: The key differences Using package configurations in SQL Server Integration Services (SSIS) How SQL Server 2008 components impact SharePoint implementations Achieving high availability and disaster recovery with SharePoint databases Recommended practices for SQL Server Analysis Services aggregations Microsoft SQL Server 2005 End of life comes for SQL Server 2005 SP2, 2008 SQL Server Reporting Services Fast Guide SQL Server Service Broker Tutorial and Reference Guide Tips for tuning SQL Server 2005 to improve reporting performance SQL Server consolidation: Why it's an optimization technique Parent-child dimensions in SQL Server 2005 with Analysis Services MDX Enforcing data integrity in a SQL Server database SSIS error message due to installation problem on SQL Server 2005 Should you upgrade to SQL Server 2005 or SQL Server 2008? Basics for working with DATETIME and SMALLDATETIME in SQL Server 2005 Microsoft SQL Server 2005 Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data aggregation  (SearchSQLServer.com) data preprocessing  (SearchSQLServer.com) data warehouse  (SearchSQLServer.com) FileMaker  (SearchSQLServer.com) GIS  (SearchSQLServer.com) MOLAP  (SearchSQLServer.com) pivot table  (SearchSQLServer.com) Quiz: SQL Server 2000  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary