An Interactive Table Report | Microsoft SQL Server 2000 Reporting Services Step by Step (Pro-Step by Step Developer)

Now that you have a taste of how the Report Wizard works and what it can do, let’s try something a bit more complex. We will create a table report that implements an interactive feature called drilldown. With the drilldown type of report, only the high-level, summary information is initially presented to the viewers. They can then click a special area of the report (in our case, that area is designated by a plus sign) to reveal part of the lower-level, detail information. The viewers drill down through the summary to get to the detail.

The Customer-Invoice Report

Features Highlighted

Using a shared data source
Linking tables in the Query Builder
Assigning columns for page breaks and grouping
Enabling subtotals and drilldown

Business Need The accounting department would like a report listing all the Galactic Delivery Services (GDS) customers. The customers need to be grouped by Billing City, with each city beginning on a new page. The report will allow a viewer to drill down from the customer level to see the invoices for that customer.

Task Overview

Reopen the Chapter04 Project
Create a New Report in the Chapter04 Project, Select the Shared Data Source, and Create a Dataset
Choose the Report Layout

Customer-Invoice Report, Task 1: Reopen the Chapter04 Project

If you have not closed the Chapter04 project since working on the previous section of this chapter, skip to step 8. Otherwise, follow these steps, starting with step 1:

Run Visual Studio .NET 2003.
If a link to the Chapter04 project is visible on the Start Page, click this link and the Chapter04 project will open. Proceed to step 8. If a link to the Chapter04 project is not visible on the Start Page, continue with step 3.
Select File | Open Solution.
Click My Projects.
Double-click MSSQLRS.
Double-click Chapter04.
Double-click Chapter04.sln. (This is the file that contains the solution for Chapter04.)
If the CustomerList1 report is displayed in the center of the screen, click the X button in the upper-right corner of the center section of the screen to close this report.

Task Notes Opening the Chapter04 solution (Chapter04.sln) and opening the Chapter04 project (Chapter04.rptproj) actually produce the same end result, so you can do either. There is only one project in the Chapter04 solution, so that project is automatically opened when the solution is opened. When the Chapter04 project is opened, the last report you worked on will be displayed in the center of the screen. In this case, it is probably the Customer List report.

You do not need to close one report before working on another report. In fact, you can have multiple reports open at one time and use the tabs containing the report names to move between them. In most cases, however, I find that a philosophy of “the less clutter the better” works well when creating reports. For this reason, I recommend that you close all unneeded reports as you move from one report to the next.

Customer-Invoice Report, Task 2: Create a New Report in the Chapter04 Project, Select the Shared Data Source, and Create a Dataset

In the Solution Explorer on the right side of the screen, right-click the Reports folder. You will see the context menu shown here.
Select the Add New Report command from the context menu. This will start the Report Wizard, allowing you to create another report in the current project.
Click Next. The Select the Data Source page will appear.
Make sure the Shared Data Source radio button is selected and the Galactic data source is selected in the drop-down list, as shown here. Click Next. The Design the Query page will appear.
Click Edit. The Query Builder will appear.
Right-click in the diagram pane (the upper area) of the Query Builder screen. You will see the diagram pane context menu.
Select the Add Table command from the context menu.
Double-click “Customer (dbo)” in the list of tables. The Customer table is added to the query.
Double-click “InvoiceHeader (dbo)” in the list of tables. Make sure you select InvoiceHeader and not InvoiceDetail. The InvoiceHeader table is added to the query.
Click Close to exit the Add Table dialog box. Notice that the Query Builder automatically created the INNER JOIN between the Customer and the InvoiceHeader tables, as shown in the following illustration.
Right-click the gray diamond in the middle of the link joining the Customer and the InvoiceHeader tables. The join context menu will be displayed, as shown in the following illustration.
Select the Select All Rows from Customer (dbo) option from the context menu. The diamond symbol changes, as shown here.
Scroll down in the list of columns for the Customer table until the BillingCity column name is visible.
Check the box next to the BillingCity column in the Customer table.
Scroll up in the list of columns for the Customer table and check the box next to the Name column. This places the Name field after the BillingCity field in the resulting SQL query.
In the list of columns for the InvoiceHeader table, check the boxes next to the InvoiceNumber, InvoiceDate, and TotalAmount columns.
Place a “1” in the Sort Order column for the BillingCity field either by typing in the cell or by using the drop-down list.
Place a “2” in the Sort Order column for the Name field.
Place a “3” in the Sort Order column for the InvoiceNumber field.
Right-click in the SQL pane and select Run from the context menu. The query will execute, and the result set will be displayed in the results pane. The Query Builder should appear similar to the following illustration.
Right-click in the Results Pane. Select “Clear Results” from the context menu.
Click OK. This will return you to the Design the Query page.

Task Notes The Galactic data source that you created in the first report is a shared data source. As such, the wizard defaults to using this shared data source on the Select the Data Source page any time a new report is created.

In the Query Builder, when a second table is added to the query, the column names from each table are compared. If the Query Builder finds two columns with the same name and data type, it will create a JOIN based on those columns. You saw this in steps 8 and 9 in this task.

The business need for this report states that the report should include all GDS customers. As you saw in Chapter 3, some customers may not have invoices. Therefore, in order to include all the customers in the report, we need to use a LEFT OUTER JOIN between the Customer table and the InvoiceHeader table. This is accomplished by selecting “Select All Rows from Customer (dbo),” as was done in step 12 of this task.

Customer-Invoice Report, Task 3: Choose the Report Layout

Click Next. The Select the Report Type page of the Report Wizard will appear.
Make sure that the Tabular radio button is selected and click Next. The Design the Table page of the Report Wizard will appear.
With the BillingCity field highlighted in the Available Fields list, click Page. The BillingCity field will be moved to the Displayed Fields list.
With the Name field highlighted in the Available Fields list, click Group. The Name field will be moved to the Displayed Fields list.
With the InvoiceNumber field highlighted in the Available Fields list, click Details. The InvoiceNumber field will be moved to the Displayed Fields list.
With the InvoiceDate field highlighted in the Available Fields list, click Details. The InvoiceDate field will be moved to the Displayed Fields list.
With the TotalAmount field highlighted in the Available Fields list, click Details. The TotalAmount field will be moved to the Displayed Fields list. The Design the Table page will appear.
Click Next. The Choose the Table Layout page of the Report Wizard will appear. This page appears in the Report Wizard because we put fields in the Grouping area on the Design the Table page.
Check the Include Subtotals check box.
Check the Enable Drilldown check box. The Choose the Table Layout page will appear.
Click Next. The Choose the Table Style page of the Report Wizard will appear.
Make sure that Bold is selected in the style list and click Next. The Completing the Report Wizard page will appear.
Type Customer-Invoice Report for the report name.
Click Finish. The Visual Studio window will appear.
Widen the Name column as you did with the previous report.
Click the table cell directly under the Invoice heading. This cell will be highlighted, as shown in the illustration.
Press DELETE on your keyboard to remove the nonsensical totaling of the invoice numbers.
Click the Preview tab. A preview of your report will appear.
Click the plus sign across from “Bolimite, Mfg” to view the invoices for this company, as shown here.
Click the blue triangle just below the Preview tab to advance to the next page of the report. The blue triangle is highlighted in the following illustration.
You can continue to work with the report preview to get a feel for the way report navigation and drilldown works. (For instance, you may want to try clicking the minus sign.)
Click the Save All button in the toolbar.

Task Notes When we created the Customer List report, we put all the columns from the dataset into the detail line of the report. This time, we put the BillingCity column in the Page area of the table layout. Because of this, the Report Wizard created a report that begins a new page every time there is a new value in the BillingCity column. In addition, the value of the BillingCity column appears at the top of each report page.

The following illustration shows the dataset used in the Customer-Invoice report. The first 13 rows have a value of “Axelburg” for the BillingCity column. Therefore, Axelburg appears at the top of page 1 of the report. All the rows with Axelburg in the BillingCity column will be on page 1 of the report.

click to expand

Using the Report Wizard, we put the Name column in the Group area of the table layout. This means that the report will create a new group each time the value of the Name column changes. Again, looking at the preceding illustration, you can see that the first three rows have a value of “Bolimite, Mfg” in the Name column. Therefore, these three rows will be combined in the first group on page 1 of the report.

By checking the Enable Drilldown checkbox, you told the Report Wizard to create a report where the detail lines for each grouping are initially hidden. The detail lines for a group become visible when the plus sign for that group is clicked. By checking the Include Subtotals check box, you told the Report Wizard to total any numeric columns in the detail and to show those totals in the group header for each group.

Let’s look again at the first few rows of the dataset shown in the preceding illustration. The first three rows have a value of “Bolimite, Mfg” in the Name column. Because of this, these three rows are grouped together for the report shown after step 18 in Task 3. In this report, the number 1260.0000 appears across from Bolimite, Mfg. This is the total of all the invoices in the detail rows for Bolimite, Mfg.

Because the Report Wizard tried to add up any and all numeric columns, it also created an entry in the grouping for a total of the invoice numbers. Adding up the invoice numbers does not result in a meaningful value. Therefore, we deleted this grouping entry in steps 16 and 17 of this task.