Creating Matrix Reports


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.

The Invoice-Batch Number Report

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

  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 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 starts the Report Wizard, enabling you to create an additional report in the current project.

  4. Click Next. The Select the Data Source page appears.

  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 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.)

  7. Right-click in the diagram pane (the upper area) of the Query Designer screen. You 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 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.

    image from book

  14. Right-click in the results pane. Select Clear Results from the Context menu.

  15. 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.

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

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

  2. Select the Matrix radio button.

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

  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.

    image from book

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

  9. Select Generic in the style list and click Next. The Completing the Wizard page appears.

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

  11. Click Finish. The Report Designer window appears.

    image from book

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

    image from book

  13. Click the Preview tab. A preview of your report appears.

    image from book

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




Microsoft SQL Server 2005 Reporting Services
MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))
ISBN: 0735622507
EAN: 2147483647
Year: 2007
Pages: 115

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net