How to Group Data in Smart Markers
Possible Usage Scenarios
In some Excel reports you might need to break the data into groups to make it easier to read and analyze. One of the primary purposes for breaking data into groups is to run calculations (perform summary operations) on each group of records.
Aspose.Cells smart markers allow you to group data by fields and place summary rows in between data sets or data groups. For example, if grouping data by Customers.CustomerID, you can add a summary record every time the group changes.
Group Data Parameters in Smart Markers
Following are some of the smart marker parameters used for grouping data.
group:normal/merge/repeat
We support three types of group that you can choose between.
- normal - The group by field(s) value is not be repeated for the corresponding records in the column; instead they are printed once per data group.
- merge - The same behavior as for the normal parameter, except that it merges the cells in the group by field(s) for each group set.
- repeat - The group by field(s) value is repeated for the corresponding records.
For example: &=Customers.CustomerID(group:merge)
skip
Skips a specified number of rows after each group.
For example, &=Employees.EmployeeID(group:normal,skip:1)
subtotalN
Performs a summary operation for a specified field data related to a group by field. The N represents numbers between 1 and 11 which specify the function used when calculating subtotals within a list of data. (1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN,…9=SUM etc.) Refer to the Subtotal reference in Microsoft Excel’s help for further details.
The format actually states as: subtotalN:Ref where Ref refers to the group by column.
For example,
- &=Products.Units(subtotal9:Products.ProductID) specifies summary function upon Units field with respect to the ProductID field in the Products table.
- &=Tabx.Col3(subtotal9:Tabx.Col1) specifies summary function upon the Col3 field group by Col1 in the table Tabx.
- &=Table1.ColumnD(subtotal9:Table1.ColumnA&Table1.ColumnB) specifies summary function upon ColumnD field group by ColumnA and ColumnB in the table Table1.
How to Group Data in Smart Markers
This example shows some of the grouping parameters in action. It uses the Northwind.mdb Microsoft Access database and extract data from the table named “Order Details”. We create a designer file called SmartMarker_Designer.xls in Microsoft Excel and put smart markers into various cells in worksheets. The markers are processed to fill the worksheets. The data is placed and organized by a group field.
The designer file has two worksheets. In the first we put smart markers with grouping parameters as shown in the screenshot below. Three smart markers (with grouping parameters) are placed: &=[Order Details].OrderID(group:merge,skip:1), &=[Order Details].Quantity(subtotal9:Order Details.OrderID), and &=[Order Details].UnitPrice(subtotal9:Order Details.OrderID) go into A5, B5 and C5 respectively.
| The first worksheet in the SmartMarker_Designer.xls file, complete with smart markers |
|---|
 |
| In the second worksheet of the designer file, we put some more smart markers as shown in the figure below. We place the following smart markers: |
| &=[Order Details].OrderID(group:normal), |
| &=[Order Details].Quantity, |
| &=[Order Details].UnitPrice, |
| &=&=B(r)*C(r), and |
| &=subtotal9:Order Details.OrderID into A5, B5, C5, D5 and C6 respectively. |
| The second worksheet of the SmartMarker_Designer.xls file, showing mixed smart markers. |
|---|
 |
| Here is the source code used in the example. |