Transport List Report Task 2: Create a 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.

Creating a dataset

Transport List Report, Task 2: Create a Dataset

1. Right-click in the diagram pane (the upper area) of the Graphical Query Designer screen. Select Add Table from the Context menu.
2. Add the following tables to the query:Transport (dbo)TransportType (dbo)Repair (dbo)
3. Click Close to exit the Add Table dialog box.
4. Right-click the diamond on the connection between the Transport table and the Repair table. Select the Select All Rows from Transport item from the Context menu. You may need to rearrange the TransportType table, the Transport table, and the Repair table to see this diamond.
5. Check the following columns in the TransportType table:DescriptionCargoCapacityRange
6. Check the following columns in the Transport table:SerialNumberPurchaseDateRetiredDate
7. Check the following column in the Repair table:BeginWorkDate
8. In the criteria pane (the second area from the top), type 1 in the Sort Order column across from the Description field and type 2 in the Sort Order column across from the SerialNumber field.
9. The business need for this report states it is to include only active transports. That means we only want to include transports that do not have a retired date. Type IS NULL in the Filter column across from the RetiredDate field. Remove the check mark under the Output column across from the RetiredDate field.
10. Right-click in the SQL pane (the third area from the top) and select Execute SQL from the Context menu. In the results pane (the bottom area), notice that several records appear for serial number P-348-23-4532-22A.


Figure 6

---

NOTE

You can also run the query by clicking the Run button (the one with a red exclamation point) in the Graphical Query Designer toolbar. The Graphical Query Designer toolbar is directly below the Data, Layout, and Preview tabs.

---

11. Right-click in the diagram pane of the Graphical Query Designer screen. Select Add Group By from the Context menu. A new column called Group By is added to the criteria pane.
12. In the criteria pane, click in the Group By column across from BeginWorkDate.
13. Use the drop-down list in this cell to select Max, as shown here.


Figure 7

14. When you move your cursor out of the Group By column, Expr1 will be assigned as the alias for BeginWorkDate. Replace Expr1 with LatestRepairDate in the Alias column across from BeginWorkDate.
15. Right-click in the SQL pane and select Execute SQL from the Context menu. Notice that now only one record appears for serial number P-348-23-4532-22A.

Figure 8

Task Notes The relationship between the Transport table and the Repair table is a one-to-many relationship. One transport may have many repairs. When you join these two tables, you get one record in the result set for each match between records in the Transport table and the Repair table. Because transport P-348-23-4532-22A has been in for repairs ten times, it generates ten records in the result set.

This is not exactly what the business requirements call for. Instead, we want to have one record for transport P-348-23-4532-22A with the latest repair date. To accomplish this, we use the GROUP BY clause. In Step 11, we instruct the Graphical Query Designer to group together records in the result set that have the same value.

When you use the GROUP BY clause, all the fields in the FIELD LIST must fit into one of the following two categories:

• The field must be included in the GROUP BY clause.
• The field must be enclosed in an aggregate function.

Any fields with the words "Group By" in the Group By column are included in the GROUP BY clause. These fields also have a special Group By symbol next to them in the diagram pane. By selecting Max under the Group By column, as we did in Step 13, we enclose BeginWorkDate in the MAX() aggregate function. This returns the maximum BeginWorkDate (in other words, the latest repair date) for each transport. Note, a special symbol, the Greek letter sigma, next to the BeginWorkDate field in the diagram pane to signify it is enclosed in an aggregate function.

When the BeginWorkDate field is enclosed in the MAX() aggregate function, it becomes a calculated field. It is not simply the value of the BeginWorkDate field that is returned as a column in the result set. Instead, it is a calculation using the value of the BeginWorkDate field that makes up this column of the result set. The Graphical Query Designer needs a name for this calculated column. This is known as the alias for the column. By default, the Graphical Query Designer assigns a calculated column an alias of Expr1 or something similar. To better remember what is in this result set column when the time comes to use it in a report, we changed the alias to LatestRepairDate.

Any fields with the words "Group By" in the Group By column are included in the GROUP BY clause. These fields also have a special Group By symbol next to them in the diagram pane. By selecting Max under the Group By column, as we did in Step 13, we enclose BeginWorkDate in the MAX() aggregate function. This returns the maximum BeginWorkDate (in other words, the latest repair date) for each transport. Note, a special symbol, the Greek letter sigma, next to the BeginWorkDate field in the diagram pane to signify it is enclosed in an aggregate function.

When the BeginWorkDate field is enclosed in the MAX() aggregate function, it becomes a calculated field. It is not simply the value of the BeginWorkDate field that is returned as a column in the result set. Instead, it is a calculation using the value of the BeginWorkDate field that makes up this column of the result set. The Graphical Query Designer needs a name for this calculated column. This is known as the alias for the column. By default, the Graphical Query Designer assigns a calculated column an alias of Expr1 or something similar. To better remember what is in this result set column when the time comes to use it in a report, we changed the alias to LatestRepairDate.

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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT 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 SQL Server Business Intelligence (BI) and Data Warehousing Programming report generation with SQL Server Reporting Services 2008 Using the Pivot transformation in SQL Server Integration Services 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

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary application server  (SearchSQLServer.com) Yukon  (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