You have now seen much of what the Report Wizard can do for you when it comes to tabular reports. Now, let’s look at the other report type the Report Wizard can produce for you. Prepare yourself. You are going to enter the matrix.
What Reporting Services calls a matrix report is referred to as a crosstab or a pivot table report elsewhere. In a tabular report, you have columns from a result set across the top and rows from a result set going down the page. In a matrix report, you have row values going across the top and down the page. Matrix reports are much easier to grasp once you have seen one in action, so let’s give it a try.
Feature Highlighted
Using the matrix report type
Business Need
The accounting department processes invoices in batches. Once a week, the accounting department creates invoices to send to their customers for the deliveries made over the previous week. A batch number is assigned to each invoice as it is created. All the invoices created on the same day are given the same batch number.
The new report requested by the accounting department shows the total amount of the invoices created in each batch. The report also allows batches to be broken down by billing city and by customer. To allow this type of analysis, you need to use a matrix report.
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
If you closed the Chapter04 project, reopen it. (If you need assistance with this, see Task 1 of the previous report.)
In the Solution Explorer on the right side of the screen, right-click the Reports folder.
Select the Add New Report command from the Context menu. This starts the Report Wizard, enabling you to create an additional 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. Click Next.
Click Query Builder. The Generic Query Designer appears. Switch to the Graphical Query Designer. (If you need assistance with this, see Task 2 of the previous report.)
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.
Add the following tables to the query:
Customer (dbo)
InvoiceHeader (dbo)
Click Close to exit the Add Table dialog box.
Check the following columns in the Customer table in the order shown here:
BillingCity
Name
Check the following columns in the InvoiceHeader table in the order shown here:
BatchNumber
InvoiceNumber
TotalAmount
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 illustration.
Right-click in the results pane. Select Clear Results from the Context menu.
Click OK. You return to the Design the Query page.
Task Notes Your dataset contains the columns we need to create the matrix report. Note, we did not specify any sort order for the dataset. The matrix itself takes care of sorting the dataset and displaying things in the correct order. It presents the data in the rows and in the columns in ascending order.
Click Next. The Select the Report Type page of the Report Wizard appears.
Select the Matrix radio button.
Click Next. The Design the Matrix page of the Report Wizard appears.
Use the Columns button to place the following fields in the Displayed Fields list:
BillingCity
Name
Use the Rows button to place the following fields in the Displayed Fields list:
BatchNumber
InvoiceNumber
Use the Details button to place the following field in the Displayed Fields list:
TotalAmount
Check the Enable Drilldown check box at the bottom of the page. The Design the Matrix page should appear as shown.
Click Next. The Choose the Matrix Style page of the Report Wizard appears.
Select Generic in the style list and click Next. The Completing the Wizard page appears.
Type Invoice-Batch Number Report for the report name.
Click Finish. The Report Designer window appears.
Widen the column on the far right of the matrix, as shown in the illustration.
Click the Preview tab. A preview of your report appears.
Click the Save All button in the toolbar.
Task Notes The Invoice-Batch Number Report contains a column for each billing city and a row for each batch number. You need to scroll to the right to see all the columns in the report. The numbers in the matrix are the totals for each batch number in each billing city. For example, $1,903 was invoiced to companies in Axelburg in batch number 445.
The column headings are left-justified, whereas the numeric values are right-justified. This makes the report a bit hard to read. We discuss how to correct these types of formatting issues in Chapter 5.
Clicking the plus sign next to a batch number shows you all the invoices in that batch. If you expand batch number 445, you can see that invoice number 73040 included $938 for companies in Osmar and invoice number 73041 included $438 for companies in Axelburg.
Clicking the plus sign next to a billing city shows you all the customers in that city. If you expand Axelburg, you can see that invoice number 73041 included $438 for Bolimite, Mfg. If you click the minus sign next to batch number 445, you can see that batch number 446 included $776 for Bolimite, Mfg.