Chat Icon
Login-icon
Create a Matrix Report Using Bold Reports Designer
Create a Matrix Report Using Bold Reports Designer

Create a Matrix Report Using Bold Reports Designer

A matrix data region displays summarized data grouped by columns and rows. A simple matrix design contains a row group, a column group, a corner cell, and a aggregated cell. By default, each cell in a matrix contains a text box.

Matrix architecture
Matrix architecture

For example, we can use a matrix data region to design business reports that demonstrate year over year analysis of data. Now, let’s see how to create a matrix report to display such year over year analysis data using Bold Reports Designer.

As a first step, launch the Bold Reports On-Premise server demo site, referring to the steps to create a report, and create a new report.

New report
New report

To showcase the data in a matrix, I am going to create a data set using the following query. It runs against the AdventureWorks2012 database.

SELECT Top 50 PC.Name AS ProdCat, PS.Name AS SubCat, DATEPART(yy, SOH.OrderDate) AS OrderYear, 'Q' + DATENAME(qq, SOH.OrderDate) AS OrderQtr,SUM(SOD.UnitPrice * SOD.OrderQty) AS Sales FROM Production.ProductSubcategory PS INNER JOIN Sales.SalesOrderHeader SOH INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID ON PS.ProductSubcategoryID = P.ProductSubcategoryID INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID GROUP BY DATEPART(yy, SOH.OrderDate), PC.Name, PS.Name, 'Q' + DATENAME(qq, SOH.OrderDate), PS.ProductSubcategoryID

Drag the table to the design surface and assign data to it.

Adding table and assigning data
Adding table and assigning data

To convert the basic table structure to matrix design, we need to add row groups and column groups.

Add grouping

For this report design, I am going to group the product category and subcategory data fields vertically and order year and order quarter data fields horizontally. Thus, grouping will go like the following structure.

Row groups Column groups
Parent group | Product category

              Child group | Subcategory

Parent group | Order Year

            Child group | Order Quarter

Add row groups

Refer to the grouping and totals blog for the step-by-step procedure for adding grouping in a tablix data region. Then, add row groups like in the previous structure.

Table design with row groups
Table design with row groups

Add column groups

There is no column group in the table design, by default. You can add a column group using the cell menu. Select the third column’s first cell and right-click. In the cell menu, click on the Parent Group in the Column Group category.

Matrix architecture
Column group menu

Create OrderYear and OrderQtr groups as the parent and child groups, respectively. The basic table structure has been changed to matrix structure by adding the row and column groups.

Matrix structure with row and column groups
Matrix structure with row and column groups

Display aggregate data

To display the sales amount of each product category in grouping levels, refer to the Assign expression in tablix cell documentation and assign the following expression in the fourth row, third cell.

=Sum(Fields!Sales.Value)
Matrix architecture
Add aggregated data

Switch to preview mode. Notice that the matrix data region displays the sales amount of each quarter for each product in separate rows.

Matrix architecture
Preview with initial matrix design

The details group in the matrix data region causes this data display in preview.

Matrix architecture
Details group indication

So, let’s delete the details group.

Delete details group

To delete a Details group, click on the icon in the right corner of the Details group member field in the grouping panel.

Matrix architecture
Delete group menu

Click the Delete Group option in the menu. In the Delete Group dialog, select Delete group only  and click OK.

Matrix architecture
Delete group dialog

Let’s preview the report. Now the sales amount for each quarter is displayed in a single row for every product.

Matrix architecture
Preview with initial matrix design

Delete rows and columns

The matrix design has some unused rows and columns. Refer to the Insert or delete column and Insert or delete row documentation and delete the rows and columns highlighted in the following screenshot.

Matrix architecture
Unused rows and columns

The matrix design with groupings will look like this.

Matrix architecture
Matrix design

A matrix report has been created with row groups and column groups. The next step is to add totals to the report.

Add total

Now, let’s add row and column group totals to the matrix design.

Calculate total sales for each subcategory

To add a total for each product subcategory, add a total after the SubCat group field.

Total menu
Total menu

This action will add an adjacent new row to the SubCat group.

Total menu
Matrix design with subtotal

Now, switch to preview mode. A new row will display the total sales of each quarter for each product category along the column.

Total menu
Preview: Matrix with subtotal

Calculate total sales for each Product category

To add a total for each product category, add a total after the ProdCat group field.

Total menu
Total menu

This action will add an adjacent new row to the ProdCat group in the matrix design.

Total menu
Matrix design with grand total

Switch to preview mode. Navigate to the last page and a new row will display the total sales of products for each quarter along the column.

Total menu
Preview: Matrix with grand total

Calculate total product sales for each year

To add a total for each year, add a total after the OrderQtr group field.

Total menu
Total menu

This action will add an adjacent new column to the OrderQtr group in the matrix design.

Total menu
Matrix design with totals column

Now, switch to the preview. A new column will display the total sales of products for each year along the row.

Total menu
Preview: Matrix with totals column

Calculate total product sales for consecutive years

To add a total for consecutive years, add a total after the OrderYear group field.

Total menu
Total menu

This action will add an adjacent new column to the OrderYear group in the matrix design.

Total menu
Matrix design with grand total column

Switch to the preview. A new column will display the total sales of products for consecutive years along the row.

Total menu
Preview: Matrix with grand total column

Merge cells

After adding row and column groups, corner cells are created in the matrix design. You can use the corner cell to display any label content or data. The number of corner cells in a matrix design is based on the number of row and column groups in the matrix. Four cells are not used in this report and they can be merged into one. To merge the corner cells, refer to the Merge cells in corner area documentation.

Formatting
Matrix design with corner cell

You can also merge the first two cells in the grand total row.

Formatting
Merge grand total rows

Format data

By default, the summary data for the Sales field displays a general number. To format the Sales field to display the number as currency, set the currency formatting for Sales field cells using the Format property.

Formatting
Currency formatting

Format appearance of report

Now, let’s add some formatting in the matrix data region to improve the look and feel of the report. In the following design, I have applied font colors, a font family, alignments, and background colors, and made some adjustments in the width and height of the cells in the matrix data region.

Formatting
Matrix design view

Refer to the tablix report item documentation to explore tablix data regions more.

Preview report

You can preview the report using the built-in Report Viewer. The report displays the product names in their categories and the sales of products over years in matrix format.

Year by year sales analysis preview
Year by year sales analysis preview

Conclusion

I hope this blog provided a basic knowledge about matrix data regions and how to create a report using them in Bold Reports Designer. To explore further, go through our sample reports and Bold Reports documentation. I have also attached the generated .rdl file for further reference.

If you have any questions, please post them in the comments section. You can also contact us through our contact page or, if you already have an account, you can log in to submit your support question.

Bold Reports now comes with a 15-day free trial with no credit card information required. We welcome you to start a free trial and experience Bold Reports for yourself. Give it a try and let us know what you think!

Stay tuned to our official TwitterFacebookLinkedInPinterest, and Instagram pages for announcements about upcoming releases.

Tags:

Share this blog

Leave a Reply

Your email address will not be published. Required fields are marked *