Creating Matrix Reports

You have now seen much of what the Report Wizard can do for you when it comes to tabular reports. It is now time to look at the other report type that the Report Wizard will produce for you. Prepare yourself; we 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, we have columns from a result set across the top and rows from a result set going down the page. In a matrix report, we 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.

The Invoice-Batch Number Report

Features 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 that were 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 will show the total amount of the invoices created in each batch. The report will also allow batches to be broken down by billing city and by customer. In order to allow this type of analysis, you will need to use a matrix report.

Task Overview

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

2. Choose the Report Layout

Invoice-Batch Number Report, Task 1: Reopen the Chapter04 Project, Create a New Report in the Chapter04 Project, Select the Shared Data Source, and Create a Dataset

1. If you have closed the Chapter04 project, reopen it. (If you need assistance with this, see Task 1 of the previous report.)

2. In the Solution Explorer on the right side of the screen, right-click the Reports folder.

3. Select the Add New Report command from the context menu. This will start the Report Wizard, allowing you to create an additional report in the current project.

4. Click Next. The Select the Data Source page will appear.

5. Make sure the Shared Data Source radio button is selected and the Galactic data source is selected in the drop-down list. Click Next.

6. Click Edit. The Query Builder will appear.

7. Right-click in the diagram pane (the upper area) of the Query Builder screen. You will see the diagram pane context menu.

8. Select the Add Table command from the context menu.

9. Add the following tables to the query:

   Customer (dbo)

   InvoiceHeader (dbo)

10. Click Close to exit the Add Table dialog box.

11. Check the following columns in the Customer table in the order shown here:

    BillingCity

    Name

12. Check the following columns in the InvoiceHeader table in the order shown here:

    BatchNumber

    InvoiceNumber

    TotalAmount

13. 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 illustration.

    

14. Right-click in the Results Pane. Select “Clear Results” from the context menu.

15. Click OK. You will return to the Design the Query page.

Task Notes Your dataset contains the columns we need to create the matrix report. You will note that we did not specify any sort order for the dataset. The matrix itself will take care of sorting the dataset and displaying things in the correct order. It will present the data in the rows and in the columns in ascending order.

Invoice-Batch Number Report, Task 2: Choose the Report Layout

1. Click Next. The Select the Report Type page of the Report Wizard will appear.

2. Select the Matrix radio button.

3. Click Next. The Design the Matrix page of the Report Wizard will appear.

4. Use the Columns button to place the following fields in the Displayed Fields list:

   BillingCity

   Name

5. Use the Rows button to place the following fields in the Displayed Fields list:

   BatchNumber

   InvoiceNumber

6. Use the Details button to place the following field in the Displayed Fields list:

   TotalAmount

7. Check the Enable Drilldown check box at the bottom of the page. The Design the Matrix page should appear as shown.

   

8. Click Next. The Choose the Matrix Style page of the Report Wizard will appear.

9. Make sure that Bold is selected in the style list and click Next. The Completing the Report Wizard page will appear.

10. Type Invoice-Batch Number Report for the report name.

11. Click Finish. The Visual Studio window will appear.

    

12. Widen the column on the far right of the matrix, as shown in the illustration.

    

13. Click the Preview tab. A preview of your report will appear.

    

14. 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 will 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 will 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.