Chat Icon
Login-icon
New Matrix Report Item: Cross-Tabular Reports
New Matrix Report Item: Cross-Tabular Reports

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.

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

Dataset
Data set

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.

Drag and drop the matrix report item
Drag and drop the matrix report item

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.

Assign data set
Assign data set

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.

Row groups Column groups
Parent group | Country Parent group | Year
          Child group | Products              Child group | Quarter

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.

Assign data
Assign data

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

Add row group
Add row group

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.

Add row child group
Add row child group

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

Tablix Group dialog
Tablix Group dialog

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

Choose child group
Choose child group

Click OK to add the child group.

Row child group added
Row child group added

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.

Column child group added
Column child group added

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.

Insert column
Insert column

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

Column cell added
Column cell added

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.

Set expression
Set expression

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.

Insert expression in text field
Insert expression in text field

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

=Sum(Fields!Amount.Value)
Set aggregate values
Set aggregate values

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.

Insert row
Insert row

A new row will be inserted.

New row inserted
New row inserted

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

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

Set header text
Set header text

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

Add text dialog
Add text dialog

Now header text will be set for the units aggregate.

Column header text added
Column header text added

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.

Add total
Add total

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

Total row added
Total row added

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.

Design view after formatting
Design view after formatting

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.

Report preview of the sales data
Report preview of the sales data

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.

Label text and values added
Label text and values added

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
Add filter
Add filter

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.

Modify report header
Modify report header

Preview the report to view the sales data.

Report preview of yearly sales data
Report preview of yearly 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.

Tags:

Share this blog

Leave a Reply

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