Riding Down Familiar Roads

We cover some familiar territory as we begin building reports without the Report Wizard. In Chapter 4, you used the Report Wizard to create table reports (the Customer List Report and the Customer-Invoice Report) and matrix reports (the Invoice-Batch Number Report). We create these types of reports once more but, this time, without the aid of the wizard.

Again, we look at the business needs of Galactic Delivery Services (GDS) and create reports to satisfy those business needs.

The Transport List Report

Features Highlighted

• Building a GROUP BY clause using the Graphical Query Designer

• Creating a table report from scratch

Business Need   The transport maintenance department at Galactic Delivery Services needs a list of all the transports currently in service. They want this list to be grouped by transport type. The list includes the serial number, the purchase date, and the date the transport was last in for repairs. The list also includes the cargo capacity and range of each transport type.

Task Overview

1. Create the Chapter05 Project, Create a Shared Data Source, and Create a New Report in the Chapter05 Project

2. Create a Dataset

3. Place a Table Item on the Report and Populate It

4. Add Table Grouping and Other Report Formatting

Transport List Report, Task 1: Create the Chapter05 Project, Create a Shared Data Source, and Create a New Report in the Chapter05 Project

1. Run the Business Intelligence Development Studio or Visual Studio 2005. The Start page is displayed (or select File|Close Project from the menu if a solution is already open).

2. Click New Project to create a new project. This displays the New Project dialog box. (Remember, you can create a new project in three different ways: Select File|New|Project from the Main menu, click the New Project toolbar button, or click the Create Project link on the Start page. All these actions achieve the same result.)

3. Click the Report Server Project icon in the Templates area of the New Project dialog box. (Be sure to click the Report Server Project icon and not the Report Server Project Wizard icon.)

4. Type Chapter05 for the project name. This project will contain all the reports you create in this chapter.

5. Click Browse to open the Project Location dialog box.

6. Click My Projects to go to the Visual Studio Projects folder.

7. In the list of folders, double-click the MSSQLRS folder.

8. Click Open in the lower-right corner of the Project Location dialog box. The New Project dialog box should now look like this:

   

9. Click OK in the New Project dialog box. A new project is created.

10. In the Solution Explorer on the right side of the screen, right-click the Shared Data Sources folder. Select Add New Data Source from the Context menu, as shown here.

    

11. Type Galactic for Name. Click Edit. The Connection Properties dialog appears.

12. Type the name of the Microsoft SQL Server database server hosting the Galactic database or select it from the drop-down list. If the Galactic database is hosted by the computer you are currently working on, you may type (local) for the server name.

13. Click the Use SQL Server Authentication radio button.

14. Type GalacticReporting for the user name.

15. Type gds for the password.

16. Click the Save My Password check box.

17. Select Galactic from the Select the Database on the Server drop-down list.

18. Click Test Connection. If the message Test Connection Succeeded appears, click OK. If an error message appears, make sure the name of your database server, the user name, the password, and the database are entered properly. If your test connection still does not succeed, make sure you have correctly installed the Galactic database.

19. Click OK to exit the Connection Properties dialog box. Click OK again to exit the Shared Data Source dialog box. A new shared data source called Galactic.rds is created in the Chapter05 project.

20. In the Solution Explorer, right-click the Reports folder.

21. Put your mouse pointer over Add in the Context menu and wait for the submenu to appear. Select the New Item command from the Context menu, as shown here.

    

22. The Add New Item—Chapter05 dialog box appears. Make sure the Report icon is selected in the Templates area. Enter TransportList for the name.

    

23. Click Add. A new report called TransportList.rdl is created in the Chapter05 project. You are taken to the Data tab of this new report.

24. Select <New Dataset…> from the Dataset drop-down list. The Dataset dialog box appears.

25. Enter TransportList for the dataset’s name in the Dataset dialog box.

    Note 

    The dataset name must not contain any spaces.

26. Galactic (shared) is selected as the data source by default. Click OK. You return to the Data tab, which now displays the Generic Query Designer. We use the Generic Query Designer in Chapter 6. For now, we switch to the Graphical Query Designer and all the helpful tools it provides.

27. Click the Generic Query Designer button in the Data tab toolbar as shown in the following illustration. This unselects the Generic Query Designer and switches to the Graphical Query Designer.

Task Notes   Because we are creating several reports in the Chapter05 project, all of which select data from the Galactic database, we began by creating a shared data source. This saves us time as we create each of the reports. We continue this practice throughout the remaining chapters.

In Steps 20 and 21, we are adding a report to the project. In Chapter 4, you saw that selecting Add New Report from the Context menu causes the new report to be created with the Report Wizard. In this chapter, we are looking to build our reports from scratch, which is why we used Add New Item in Step 21.

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:

   Description

   CargoCapacity

   Range

6. Check the following columns in the Transport table:

   SerialNumber

   PurchaseDate

   RetiredDate

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.

    

    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.

    

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.

    

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.

Transport List Report, Task 3: Place a Table Item on the Report and Populate It

1. Click the Layout tab to begin working on the report layout.

   Note 

   Your installation of the Report Designer may be using a feature called Auto-Hide with the Toolbox. Auto-Hide is used to provide more screen space for your report layout. When Auto-Hide is active for the Toolbox, the Toolbox is only represented on the screen by a tab containing a tool icon and the word “Toolbox” at the extreme left side of the window. To view the actual Toolbox, place your mouse pointer on top of this tab. After a second or two, the Toolbox appears. Once your mouse pointer moves off the Toolbox, it is automatically hidden again.

2. Click the Table report item in the Toolbox. The mouse pointer changes to a table icon and crosshairs when you move your mouse pointer over the report layout area, as shown in the next illustration.

   

3. Click-and-drag the mouse over the lower three-quarters of the report layout area, as shown in the following illustration. Note, when you begin dragging, the mouse pointer changes back to the usual arrow icon.

   

4. When you release the mouse button, after dragging, a table is created to occupy the area you just defined. By default, every cell in the table is occupied by an empty text box. Click in each cell of the table, and note the name and type of report item shown at the top of the Properties window.

5. Let’s take a few moments to go over the methods for selecting various parts of the table. You have already seen how to select individual cells. The gray border on top of and to the left of the table item provides handles for selecting other parts of the table. Click any of the gray rectangles in the border above the table item. This action selects the corresponding column, as shown in the following illustration.

   

6. Click any of the gray rectangles in the border to the left of the table item. This action selects a row, as shown here.

   

7. Click the gray square in the upper-left corner of the border. This action selects the entire table. When the entire table is selected, the gray border is replaced by the sizing handles (the small white squares) for the table. You must select the entire table before you can move and size the table item.

8. In the Datasets window, expand the TransportList dataset. Drag the SerialNumber field from the Datasets window and drop it on the middle-left table cell. An expression that returns the value of the SerialNumber field is placed in the text box that occupies the middle-left table cell. The name of the field is used to create a column heading. This is placed in the upper-left table cell.

9. Drag the PurchaseDate field from the Datasets window and drop it on the center table cell. Drag the LatestRepairDate field from the Datasets window and drop it on the middle-right table cell. The report layout should now appear, as shown next.

   

10. Select the header row (the top row) by clicking the gray rectangle in the border to the left of the row.

11. Make the following changes in the Properties window:

    Property	New Value
    FontWeight (expand the Font property to find the FontWeight property)	Bold
    TextDecoration	Underline

12. In the gray border to the left of the table, click the line between the header row and the detail row. Drag it to reduce the height of the header row.

13. In the gray border to the left of the table, click the line between the detail row and the footer row. Drag it to reduce the height of the detail row.

14. Click the center cell in the table. Hold down SHIFT and click the middle-right cell in the table. Both of these cells are now selected. Make the following changes in the Properties window:

    Property	New Value
    Format	MM/dd/yyyy
    TextAlign	Left

    Note 

    Make sure you use uppercase letter Ms in the Format property. MM is the placeholder for month in a format string, whereas mm is the placeholder for milliseconds.

15. Click the Preview tab to preview the report. The report should appear as shown here.

Task Notes   In the Properties window are several instances where a group of related properties are combined under a summary property. For instance, the FontStyle, FontFamily, FontSize, and FontWeight properties are combined under the Font property. The Font property serves as a summary of the other four.

Initially, only the summary property is visible in the Properties window. A plus (+) sign to the left of a property tells you it is a summary property and has several detail properties beneath it. The summary property has a value that concatenates the values of all the detail properties underneath it.

For example, suppose the FontStyle, FontFamily, FontSize, and FontWeight properties have the following values:

FontStyle:	Normal
FontFamily:	Arial
FontSize:	10pt
FontWeight:	Bold

In that case, the Font property has this value:

Font:

Normal, Arial, 10pt, Bold

You can change the value of a detail property by editing the concatenated values in the summary property, or you can expand the summary property and edit the detail properties directly.

Transport List Report, Task 4: Add Table Grouping and Other Report Formatting

1. Click the Layout tab.

2. Right-click the gray border to the left of the table. You may need to click on the table to get the borders to appear. Select Insert Group from the Context menu. The Grouping and Sorting Properties dialog box appears.

3. Enter TransportType for the name (no spaces are allowed in the Name field).

4. Select=Fields!Description.Value from the drop-down list in the first row under Expression. You have to click in this cell to get the drop-down list to appear.

5. Uncheck the Include Group Footer check box.

6. Click OK. A new blank row is added to the table below the header row. This is the grouping row.

7. Click the leftmost cell in the grouping row. Hold down SHIFT, and then click the center and the rightmost cells in the grouping row. Right-click in any of the selected cells and select Merge Cells from the Context menu, as shown here.

   

8. Right-click in the newly merged field and select Expression from the Context menu. The Edit Expression dialog box appears.

9. Type the following after the equals sign (=), including the quotation marks and the space after the ampersand, in the Expression area:

    "Transport Type: " &

10. Select the Fields (TransportList) entry in the tree view as shown here. Note, the fields in the TransportList dataset appear in the lower-right list box.

    

11. Double-click the Description field to append it to the expression in the Expression area.

    Caution 

    If you type the field expression, rather than selecting it from the Fields area, it must be typed in the exact case shown in the Fields area. Fields, as well as parameters, are case-sensitive when used in expressions.

12. Type the following at the end of the expression in the Expression area:

     & vbCrLf & "   Cargo Capacity: " &

    A space must be before and after each ampersand (&) character.

13. Double-click the CargoCapacity field to append it to the expression in the Expression area.

14. Type the following at the end of the expression in the Expression area:

     & " Range: " &

    A space must be before and after each ampersand (&) character.

15. Double-click the Range field to append it to the expression in the Expression area. The Edit Expression dialog box should appear.

    

16. Click OK.

17. With the merged field still selected, make the following changes in the Properties window:

    Property	New Value
    BorderStyle/Bottom (expand the BorderStyle property to find the Bottom property)	Solid
    FontWeight	Bold

18. Click the Textbox report item in the Toolbox. The mouse pointer changes to a text box icon and crosshairs when you move your mouse pointer over the report layout area.

19. Click-and-drag the mouse over the entire area above the table on the report layout area. Note, when you begin dragging, the mouse pointer changes back to the usual arrow icon.

20. When you release the mouse button, after dragging, a text box is created to occupy the area you just defined. Click the text box and type the following:

     Transport List

21. With the text box still selected, make the following changes in the Properties window:

    Property	New Value
    FontSize	16pt
    FontWeight	Bold
    TextAlign	Center

22. Click the Preview tab. The report should appear as shown here.

    

23. Click Save All in the toolbar.

Task Notes   When we added the grouping, we specified an expression in Step 4. This group expression determines when a new group header is placed in the report. In the Transport List report, we used the Description field from the TransportType table in the group expression. Because our first sort in the dataset was on the TransportType.Description column, all the Photon III transports came first in the dataset, followed by the StarLifter transports, and, finally, the Warp Hauler transports. Each time the value of the group expression changes, a new group header is added to the report.

Be sure you do not confuse the grouping in the report with the GROUP BY clause we used in SQL SELECT statements. The SQL GROUP BY clause takes a number of records and combines them into a single record in the result set. The grouping in the report takes a number of records in the dataset and surrounds them with a group header and/or group footer when they are output in the report.

In Steps 9–15, we combined all the fields that need to be in the group header into one expression. This was done so we could create a multiline group header, and also to concatenate or combine the labels (Transport Type:, Cargo Capacity:, and Range:) and the contents of the three fields (Description, CargoCapacity, and Range) into one string. The three columns of the group header were merged together to create room for the resulting expression. The Visual Basic concatenation operator (&) is used to combine the values into one long string. The Visual Basic constant vbCrLf is used to put a carriage return and linefeed in the middle of the string. This causes everything following the carriage return and linefeed to be placed on the next line down, giving us a two-line group header.

Remember, table cells are always occupied by a report item. If no other report item has been placed in a cell, the cell is occupied by a text box. When multiple cells are merged, the report item in the leftmost cell expands to fill the merged table cell. The report items in the other cells involved in the merge are automatically deleted.

We created a border on the bottom of the text box in the merged cells to underline our group heading. This is easier and more efficient than adding a Line report item to the report to get the same result. This is especially true when you are trying to underline something in the middle of a table, such as our group header.

When you typed the text in Step 20, it looked like you were entering the text directly into the text box. What you were doing is changing the Value property of the text box. You can change the Value property of a text box by typing directly into the text box in the report layout area or by using the Properties window.

In addition, the Edit Expression dialog box can be used to change the Value property of a text box, as well as many other report item properties. In Step 8, we used the Context menu to bring up the Edit Expression dialog box. The Edit Expression dialog box can also be accessed through a drop-down list in the Properties window, as this illustration shows. In addition to the Value property of the text box, the Edit Expression dialog box can be used to change a number of properties of various report items. We discuss this in more detail in Chapter 7.

You probably noticed a red, jagged line that appears occasionally below the expression as you typed it in the Edit Expression dialog box. If you have ever used Microsoft Word, then you know this means something is wrong with the text you have typed. In Word, this red line indicates a spelling error. In the Edit Expression dialog box, this means a problem exists with the syntax of your expression. Hovering over the red line provides you with a brief description of the problem.