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. Let’s 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.
Using a shared data source
Linking tables in the Graphical Query Designer
Assigning columns for page breaks and grouping
Enabling subtotals and drilldown
Business Need The accounting department would like a report listing all Galactic Delivery Services (GDS) customers. The customers need to be grouped by Billing City, with each city beginning on a new page. The report allows a viewer to drill down from the customer level to see the invoices for that customer.
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
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 the Business Intelligence Development Studio or Visual Studio 2005.
If a link to the Chapter04 project is visible on the Start Page, click this link and the Chapter04 project opens. Proceed to Step 8. If a link to the Chapter04 project is not visible on the Start Page, continue with Step 3.
Click My Projects.
Double-click Chapter04.sln. (This is the file that contains the solution for Chapter04.)
If the CustomerList 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) produce the same end result, so you can do either. Only one project is 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 is 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 among 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 you close all unneeded reports as you move from one report to the next.
In the Solution Explorer on the right side of the screen, right-click the Reports folder. You see the Context menu shown here.
Select the Add New Report command from the Context menu. This starts the Report Wizard, enabling you to create another report in the current project.
Click Next. The Select the Data Source page appears.
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 appears.
Click Query Builder. The Generic Query Designer appears. Click the Query Designer toggle button. The Query Designer switches to the Graphical Query Designer.
Right-click in the diagram pane (the upper area) of the Query Designer screen. You 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 the Query Designer automatically creates 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 is displayed, as shown in the following illustration.
Choose the Select All Rows from Customer option from the Context menu. The diamond symbol changes, as shown in the next illustration.
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 Execute SQL from the Context menu. The query executes, and the result set is displayed in the results pane. The Query Designer should appear similar to the following illustration.
Right-click in the results pane. Select Clear Results from the Context menu.
Click OK. This returns you to the Design the Query page.
Task Notes The Galactic data source 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 Designer, when a second table is added to the query, the column names from each table are compared. If the Query Designer 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 through 10 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, so to include all the customers in the report, you need to use a LEFT OUTER JOIN between the Customer table and the InvoiceHeader table. You can accomplish this by choosing Select All Rows from Customer, as you did in Step 12 of this task.
Click Next. The Select the Report Type page of the Report Wizard appears.
Make sure the Tabular radio button is selected and click Next. The Design the Table page of the Report Wizard appears.
With the BillingCity field highlighted in the Available Fields list, click Page. The BillingCity field is moved to the Displayed Fields list.
With the Name field highlighted in the Available Fields list, click Group. The Name field is moved to the Displayed Fields list.
With the InvoiceNumber field highlighted in the Available Fields list, click Details. The InvoiceNumber field is moved to the Displayed Fields list.
With the InvoiceDate field highlighted in the Available Fields list, click Details. The InvoiceDate field is moved to the Displayed Fields list.
With the TotalAmount field highlighted in the Available Fields list, click Details. The TotalAmount field is moved to the Displayed Fields list. The Design the Table page appears as shown here.
Click Next. The Choose the Table Layout page of the Report Wizard appears. This page appears in the Report Wizard because we put fields in the Group 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 appears as shown.
Click Next. The Choose the Table Style page of the Report Wizard appears.
Select Generic in the style list, and then click Next. The Completing the Wizard page appears.
Type Customer-Invoice Report for the report name.
Click Finish. The Report Designer window appears.
Widen the Name column as you did with the previous report.
Click the table cell directly under the Invoice heading. This cell is 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 appears.
Click the plus sign in front of Bolimite, Mfg to view the invoices for this company, as shown here.
Click the Next Page button (the blue triangle just below the Preview tab) to advance to the next page of the report. The Next Page button 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.
Using the Report Wizard, we put the Name column in the Group area of the table layout. This means 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 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 check box, 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, so we deleted this grouping entry in Steps 16 and 17 of this task.