New Matrix Report Item: Cross-Tabular Reports

We are excited to announce that the Bold Reports Designer supports a new matrix widget as of the September 2020 Bold Reports release. In this blog, we are going to design a yearly sales report of a company using the matrix report item.

First, launch the Bold Reports Enterprise server demo site, and then refer to the steps to create a report to create a new report.

Create a data set

Create a data set to bind the required data to the matrix report item. You can download this sample data file Sales.json to proceed with designing the report. Refer to the blog Connecting Your Reports to a JSON Data Source to see how to connect the supplied JSON data to your report.

Using the data set we created, we will now design a yearly sales report to display the list of products and their sales data for each year.

Add a matrix to the report

Drag a matrix report item from the item panel to the design surface.

The next step is to assign the data set to the matrix report item. Select the matrix and open the Properties panel. The matrix properties will be listed here. Under the Data category, you can find the Dataset property. From the drop-down, choose the data set we created.

For our report design we will group the Country and Products fields vertically, and the Year and Quarter fields horizontally. Thus grouping will be in the following structure.

Assign data

As per the described group structure, assign the Country data set field to the row group in the matrix. Click on the data assign menu icon for the matrix row group cell and select Country from the data assign menu. The Country field will replace the default row group in the matrix.

Notice in the grouping panel that Country1 has replaced the default RowGroup.

Similarly, assign Year to the column group. It will replace the default ColumnGroup.

Add child group

Now we will add a child row group to the matrix. In the Row Groups pane, open the group menu in Country1 group field.

Click the Child Group option under the Add Group category. The Tablix Group dialog will open to configure the grouping.

Choose the Products field as the child group in the Group by drop-down.

Click OK to add the child group.

You can notice that the child group Products has been added in the matrix as well as in the grouping panel. Similarly, add Quarter as a column child group for Year.

Display aggregate data

We are going to display the sales amount and units of each product. So, we need two columns in the data cell. Right-click on the data cell and insert a column inside the group to the right.

Now, another column cell will be inserted in the matrix data cell.

We are going to display the units sold and the total amount for each product. Hence, we need to set expressions for both data cells. Click on the data assign menu icon for the first data cell. Choose Add Expression from the menu.

The expression builder window will be opened. Add the following expression in the text area and click OK.

=Sum(Fields!Units.Value)

This expression generates the sum of units sold.

In the same way, assign the following expression in the second data cell to generate the total sales amount.

=Sum(Fields!Amount.Value)

Set header text

We need to set header text for the aggregates. First, insert a new row below the [Quarter] cell. Right-click on the [Quarter] cell and insert a row inside the group, below.

A new row will be inserted.

Right-click on the cell below the [Quarter] cell and select Split Cells.

Set the header text of the first aggregate cell by clicking on the cell’s data assign menu icon. Choose Add Text from the menu.

In the Add Text dialog, set the header text as Units Sold in the text area and click Add.

Now header text will be set for the units aggregate.

Similarly, set the header text as Sold Amount for the amount aggregate.

Add total for row group

Let’s add a row group to display the total units sold and the total sales amount in each quarter. In the grouping panel, select Add Total > After for the Country row group.

This adds a row in the matrix to display the total sales units and the amount.

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, alignments, background colors, and made some adjustments in the width and height of the cells in the matrix data region. The border color for the empty cells has been set to white. I also added a header for the report and removed the unused spaces in the body and footer region in the design surface.

Preview report

You can preview the report at design time using the built-in Bold Reports Viewer to ensure the report design is as expected. Click the Preview button and switch to preview mode.

In preview, our report will display the product sales by country versus the sales year, as in the following screenshot.

To view the subsequent year’s sales data, we need to scroll the page horizontally. Let’s modify the report to display sales data of each year in a separate page. For this we need to create a report parameter with the financial years as the available values. Then we are going to filter the year in the data set according to the year chosen in the parameter during preview.

Add report parameter

Create a parameter with the name Year and prompt Financial Year. Define available values for the parameter as the financial years that exist in the data set. Refer to the following screenshot and add the value and label text for all the years.

Click OK and then Save the parameter.

Add filter

The next step is to add a filter for the data set. Open the filter dialog for the data set that we created. Add a filter to match the year in the data set.

Set the following expression in the Value field to match the years that end with the values assigned to the parameter.

="*" + Parameters!Year.Value

Click OK and save the filter. The matrix will now display the sales data of the financial year that is filtered by the user.

Next, append the report parameter value in the report header to display the filtered year.

Preview the report to view the sales data.

The report is generated as expected and the sales data is displayed based on the year filtered in the parameter drop-down. You can switch the year in the parameter field drop-down to display the sales data for that year.

Conclusion

I hope this blog provided basic information about the matrix report item and how to create a report using it in Bold Reports Designer. For additional information on the matrix report item, you can go through the documentation. I have also linked the generated .rdl file for further reference.

If you have any questions, please post them in the comments section below. You can also contact us by submitting your questions 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 releases.