 | Aspose.Cells for Reporting Services provides a new feature for designing matrix in Excel. |
Matrix Template
A Matrix in Aspose.Cells Report template consists of Corner, Rows Groups, Columns Groups and Data portions. A sample matrix is shown in the following figure.
Matrix Corner
A Matrix Corner is located in the upper-left corner, or upper-right corner for right-to-left (RTL) layouts. This area is automatically created when you add both row groups and column groups to a matrix data region. In this area, you can merge cells embedded textbox report item.
Matrix Columns Groups Area
Matrix Columns Groups are located in the upper-right corner (upper-left corner for RTL layout). This area is automatically created when you add a column group. The cells in this area represent members of the column groups hierarchy, and display the column group instance values. In the figure, the cells that display and are nested column groups, and the cell that displays is an adjacent column group.
Matrix Rows Groups Area
Matrix Rows Groups are located on the lower-left corner (lower right for RTL layout). This area is automatically created when you add a row group. The cells in this area represent members of the row groups hierarchy, and display row group instance values. In the figure, the cells that display and are nested row groups.
Matrix Data Area
The Matrix Data is located in the lower right corner (lower left for RTL layout). The Matrix Data displays detail and grouped data. In this example, only aggregated data is used. By default, the cells in a group row or column that contain simple expressions that do not include an aggregate function, evaluate to the first value in the group. In the figure, the cells display the aggregate totals for the line totals for all sales order.
Creating Matrix Template
Preparing for Creating Matrix
Before creating a matrix report user must create data sources, datasets and Report Parameters (optional) first according to the instructions described in the section DataSources and Queries . In the sample we use the AdventureWorks sample database that ships with SQL Server Reporting Services 2008.
To create a new matrix, follow the steps:
- Open MS excel.
- Click Open Report to open an RDL Report file that contains the data sources, datasets and Report Parameters created in advance. Once the file has been opened successfully all its information is available for use, for example, its datasets are listed in the DataSet combo box.
Open MS Excel Sheet and select data set.
Set row groups and column groups through Set Group.
Merge cells to set matrix corner.
Set matrix corner through insert formula.
Click set attribute to set matrix attribute.
It consists of name, range, group and order.
Clicking modify attribute can check and modify all matrix attributes of current worksheet.
Save, publish and review report.
The output of the sample template is shown as the following.