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.

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.

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.

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.

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.

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.

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.

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)

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

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

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.

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

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

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.

The matrix design with groupings will look like this.

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.

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

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

Calculate total sales for each Product category

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

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

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.

Calculate total product sales for each year

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

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

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

Calculate total product sales for consecutive years

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

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

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

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.

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

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.

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.

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.

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 Twitter, Facebook, LinkedIn, Pinterest, and Instagram pages for announcements about upcoming releases.