We will cover some familiar territory as we begin building reports without the Report Wizard. In Chapter 4, we used the Report Wizard to create table reports (the Customer List report and the Customer-Invoice report) and matrix reports (the Invoice-Batch Number Matrix report). We will create these types of reports once more, but this time without the aid of the wizard.
Again, we will look at the business needs of Galactic Delivery Services (GDS) and create reports to satisfy those business needs.
Building a GROUP BY clause using the Query Builder
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 would like this list to be grouped by transport type. The list will include the serial number, purchase date, and the date that the transport was last in for repairs. The list will also include the cargo capacity and range of each transport type.
Create the Chapter05 Project, Create a Shared Data Source, and Create a New Report in the Chapter05 Project
Create a Dataset
Place a Table Item on the Report and Populate It
Add Table Grouping and Other Report Formatting
Run Visual Studio .NET 2003. The Visual Studio Start page will be displayed (or select File | Close Solution from the menu if a solution is already open).
Click New Project to create a new project. This will display the New Project dialog box. (Remember, you can create a new project three different ways: Select File | New | Project from the main menu, click the New Project toolbar button, or click New Project on the Start page. All these actions achieve the same result.)
Click the Report Project icon in the Templates area of the New Project dialog box. (Be sure to click the Report Project icon and not the Report Project Wizard icon.)
Type Chapter05 for the project name. This project will contain all the reports you create in this chapter.
Click Browse to open the Project Location dialog box.
Click My Projects to go to the Visual Studio Projects folder.
In the list of folders, double-click the MSSQLRS folder.
Click Open in the lower-right corner of the Project Location dialog box. The New Project dialog box should now look like this:
Click OK in the New Project dialog box. Visual Studio will create a new project.
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.
Type the name of the Microsoft SQL Server database server that is 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.
Click the Use a Specific User Name and Password radio button.
Type GalacticReporting for the user name.
Type gds for the password.
Click the Allow Saving Password check box.
Select Galactic from the Select the Database on the Server drop-down list.
Click Test Connection. If a “Test connection succeeded.” message appears, click OK. If an error message appears, make sure the name of your database server, the user name, the password, and the database have been entered properly. If your test connection still does not succeed, make sure you have correctly installed the Galactic database.
Click OK. A new shared data source called Galactic.rds will be created in the Chapter05 project.
In the Solution Explorer, right-click the Reports folder.
Put your mouse pointer over Add in the context menu and wait for the submenu to appear. Select the Add New Item command from the context menu, as shown here.
Make sure that the Report icon is selected in the Templates area. Enter TransportList for the name. The Add New Item - Chapter05 dialog box will appear.
Click Open. A new report called TransportList.rdl will be created in the Chapter05 project. You will be taken to the Data tab of this new report.
Select <New Dataset…> from the Dataset drop-down list. The Dataset dialog box will appear.
Enter TransportList for the dataset’s name in the Dataset dialog box.
The dataset name must not contain any spaces.
“Galactic (shared)” will be selected as the data source by default. Click OK. You will return to the Data tab, which now displays the Generic Query Designer. We will use the Generic Query Designer in Chapter 6. For now, we will switch to the Query Builder and all of the helpful tools it provides.
Click the Generic Query Designer button in the Data tab toolbar as shown in the following illustration. This will unselect the Generic Query Designer and switch to the Query Builder.
Task Notes Because we will be creating several reports in the Chapter05 project, all of which will select data from the Galactic database, we started out by creating a shared data source. This will save us time as we create each of the reports. We will continue this practice throughout the remaining chapters.
In steps 19 and 20, 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. That is why we used Add New Item in step 20.
Right-click in the diagram pane (the upper area) of the Query Builder screen. Select Add Table from the context menu.
Add the following tables to the query:
Click Close to exit the Add Table dialog box.
Right-click the diamond on the connection between the Transport table and the Repair table. Select the All Rows from Transport (dbo) item from the context menu. You may need to rearrange the TransportType table, the Transport table, and the Repair table in order to see this diamond.
Check the following columns in the TransportType table:
Check the following columns in the Transport table:
Check the following column in the Repair table:
In the grid 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.
The business need for this report states that 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 Criteria column across from the RetiredDate field. Remove the check mark under the Output column across from the RetiredDate field.
Right-click in the SQL pane (the third area from the top) and select Run from the context menu. In the results pane (the bottom area), notice that several records appear for serial number P-348-23-4532-22A.
You can also run the query by clicking the Run button (the one with a red exclamation point) in the Query Builder toolbar. The Query Builder toolbar is directly below the Data, Layout, and Preview tabs.
Right-click in the diagram pane of the Query Builder screen. Select Group By from the context menu. A new column called Group By is added to the grid pane.
In the grid pane, click in the Group By column across from the BeginWorkDate row.
Use the drop-down list in this cell to select Max, as shown here.
Replace “Expr1” with “LatestRepairDate” in the Alias column across from the BeginWorkDate row.
Right-click in the SQL pane and select Run from the context menu. Notice that now only one record appears for serial number P-348-23-4532-22A.
Right-click in the results pane and select Clear Results from the context menu.
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 we join these two tables, we 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 Query Builder to group together records in the result set that have the same value.
When we 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 will return the maximum BeginWorkDate (in other words, the latest repair date) for each transport. Note that there is also a special symbol, the Greek letter sigma, next to the BeginWorkDate field in the diagram pane to signify that 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 Query Builder needs a name for this calculated column. This is known as the alias for the column. By default, the Query Builder will assign a calculated column an alias of Expr1 or something similar. In order 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 the Layout tab to begin working on the report layout.
Your installation of Visual Studio 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 rectangle containing a tool icon and the word “Toolbox” at the extreme left of the Visual Studio window. To view the actual Toolbox, place your mouse pointer on top of this rectangle. After a second or two, the Toolbox will appear. Once your mouse pointer moves off of the Toolbox, it will be automatically hidden again.
Click the Table report item in the Toolbox. The mouse pointer will change to a table icon and crosshairs when you move your mouse pointer over the report layout area, as shown here.
Click and drag the mouse over the lower three-quarters of the report layout area, as shown in the following illustration. Note that when you begin dragging, the mouse pointer changes back to the usual arrow icon.
When you release the mouse button, after dragging, a table will be 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.
We will 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 here.
Click any of the gray rectangles in the border to the left of the table item. This action selects a row, as shown here.
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.
Drag the SerialNumber field from the Fields 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.
Drag the PurchaseDate field from the Fields window and drop it on the center table cell. Drag the LatestRepairDate field from the Fields window and drop it on the middle-right table cell. The report layout should now appear.
Select the header row (the top row) by clicking the gray rectangle in the border to the left of the row.
Make the following changes in the Properties window:
FontWeight (expand the Font property to find the Font Weight property)
Click the line in the gray border between the header row and the detail row. Drag it to reduce the height of the header row.
Click the line in the gray border between the detail row and the footer row. Drag it to reduce the height of the detail row.
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:
Make sure you use uppercase M’s in the Format property. “MM” is the placeholder for month in a format string, whereas “mm” is the placeholder for milliseconds.
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 that 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:
In that case, the Font property has this value:
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.
Click the Layout tab.
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 will appear.
Enter TransportType for the name (no spaces are allowed in the Name field).
Select “=Fields!Description.Value” from the drop-down in the first row under Expression. You have to click in this cell to get the drop-down to appear.
Uncheck the Include Group Footer check box.
Click OK. A new blank row will be added to the table below the header row. This is the grouping row.
Click the leftmost cell in the grouping row. Hold down SHIFT and 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.
Right-click in the newly merged field and select Expression from the context menu. The Edit Expression dialog box appears.
Type the following after the equals sign (=), including the quotation marks and the space before the first quotation mark, in the Expression area:
"Transport Type: " &
Click the plus sign to expand the Fields node in the Fields area. You will see all the fields in the TransportList dataset.
Click the Description field. Click the Append button to append the Description field to the expression in the Expression area.
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 the other items shown in the Fields area are case sensitive.
Type the following at the end of the expression in the Expression area:
& vbcrlf & " Cargo Capacity: " &
There must be a space before and after each ampersand (&) character.
Click the CargoCapacity field. Click the Append button to append the CargoCapacity field to the expression in the Expression area.
Type the following at the end of the expression in the Expression area:
& " Range: " &
There must be a space before and after each ampersand (&) character.
Click the Range field. Click the Append button to append the Range field to the expression in the Expression area. The Edit Expression dialog box should appear.
With the merged field still selected, make the following changes in the Properties window:
BorderStyle/Bottom (expand the BorderStyle
property to find the Bottom property)
Click the Textbox report item in the Toolbox. The mouse pointer will change to a text box icon and crosshairs when you move your mouse pointer over the report layout area.
Click and drag the mouse over the entire area above the table on the report layout area. Note that when you begin dragging, the mouse pointer changes back to the usual arrow icon.
When you release the mouse button, after dragging, a text box will be created to occupy the area you just defined. Click the text box and type the following:
With the text box still selected, make the following changes in the Properties window:
Click the Preview tab. The report should appear as shown here.
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 will be added to the report.
Be sure that you do not confuse the grouping in the report with the GROUP BY clause we have 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 that 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 in order 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 actually doing was 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 shown in this illustration. 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 will discuss this in more detail later in this chapter.