Design SSRS Matrix Report using Tablix Wizard

This section describes the steps to design an Annual Sales Report by Country and Bank using Tablix Wizard.

Create dataset

To present data using the tablix wizard, create a dataset and bind the required data within the tablix wizard. In this design section, the following dataset query is used for dataset creation.

SELECT [Sales].[vStoreWithDemographics].[CustomerID],
[Sales].[vStoreWithDemographics].[Name],
[Sales].[vStoreWithDemographics].[ContactType],
[Sales].[vStoreWithDemographics].[Title],
[Sales].[vStoreWithDemographics].[FirstName],
[Sales].[vStoreWithDemographics].[MiddleName],
[Sales].[vStoreWithDemographics].[LastName],
[Sales].[vStoreWithDemographics].[Suffix],
[Sales].[vStoreWithDemographics].[Phone],
[Sales].[vStoreWithDemographics].[EmailAddress],
[Sales].[vStoreWithDemographics].[EmailPromotion],
[Sales].[vStoreWithDemographics].[AddressType],
[Sales].[vStoreWithDemographics].[AddressLine1],
[Sales].[vStoreWithDemographics].[AddressLine2],
[Sales].[vStoreWithDemographics].[City],
[Sales].[vStoreWithDemographics].[StateProvinceName],
[Sales].[vStoreWithDemographics].[PostalCode],
[Sales].[vStoreWithDemographics].[CountryRegionName],
[Sales].[vStoreWithDemographics].[AnnualSales],
[Sales].[vStoreWithDemographics].[AnnualRevenue],
[Sales].[vStoreWithDemographics].[BankName],
[Sales].[vStoreWithDemographics].[BusinessType],
[Sales].[vStoreWithDemographics].[YearOpened],
[Sales].[vStoreWithDemographics].[Specialty],
[Sales].[vStoreWithDemographics].[SquareFeet],
[Sales].[vStoreWithDemographics].[Brands],
[Sales].[vStoreWithDemographics].[Internet],
[Sales].[vStoreWithDemographics].[NumberEmployees] FROM [Sales].[vStoreWithDemographics]

Refer to the Create Data section and create a dataset using the above query. The AdventuresWorks database is used here.

Add matrix using tablix wizard

  1. The tablix wizard is listed in the item panel under the Data Regions category. Tablix Wizard listed in item panel

  2. Drag and drop the tablix wizard report item into the design area from the item panel. Drag and drop tablix report item into design area

  3. The available datasets in the report will be listed in the drop-down. Choose a dataset from the drop-down list. Tablix wizard with Dataset

  4. Drag and drop AnnualSales into the Values section. Tablix wizard with row values

  5. Hover on the AnnualSales field to enable the Settings icon, then click the Settings icon (highlighted below) to open the aggregation menu. Tablix wizard config icon

    You can set the aggregation type by which you want to compute the selected value. Tablix wizard with aggregation

  6. Drag and drop CountryRegionName into the Columns group and BankName into the Rows group. Tablix wizard with column and values

  7. Click on the Ok button. The matrix will now be rendered on the design surface as shown below, Matrix simple design

Format matrix design

In the design below, the report header has been added, and the background color and font styles in the matrix cells have been updated to enhance the report design.

Formated Tablix Wizard

Refer to the Cell Properties section to style the matrix cells and Show or hide header and footer section to add or remove header/footer in the report.

Report preview

In the report preview, the Annual Sales Report will be displayed as shown below, Matrix overall design

Download the above report design from this link.