Transport List Report Task 2: Create a Dataset

This excerpt from Chapter 5 of the book Microsoft SQL Server 2005 Reporting Services 2005 explains how to create a dataset for a Transport List Report.

This Content Component encountered an error

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.



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.

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


  12. 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.
  13. In the criteria pane, click in the Group By column across from BeginWorkDate.
  14. Use the drop-down list in this cell to select Max, as shown here.

  15. Figure 7

  16. 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.
  17. 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

This was first published in February 2006

Dig deeper on Microsoft SQL Server Reporting Services (SSRS)

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close