Table subtotals

xlsgen > overview > Table subtotals

An additional xlsgen business intelligence feature is the support for subtotals, that is the ability to aggregate data along a numeric axis and accordingly to table breaks. The aggregate function lets one customize the process. Among the functions are :

function	description
sum	adds each value along the axis
min	computes the minimum value along the axis
max	computes the maximum value along the axis
average	computes the arithmetic mean value along the axis
count	computes how many values along the axis

The capabilities are exposed in the IXlsTableSubtotals interface.

Breaks must be created prior adding one or more subtotals.

There can be more than one subtotal along a given axis. For instance, one can compute the minimum and maximum along an axis.

Creating subtotals

Here is the raw data we are working with :

Raw data before creating subtotals

We are willing to know what are the minimum and maximum values of the sales index according to each city. It's very simple to obtain it :

worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("City").Breaks
pb.DistinctValues()

IXlsTableColumn tcSales = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Sales index")

IXlsTableSubtotal sts = pb.Subtotals.Add(tcSales)
sts.BuiltinFunction = xlsgen.enumSubtotalFunction.subtotalfunc_min
sts.Label = "min = "

IXlsTableSubtotal stm = pb.Subtotals.Add(tcSales)
stm.BuiltinFunction = xlsgen.enumSubtotalFunction.subtotalfunc_max
stm.Label = "max = "

pb.Apply()

And the following result is obtained :

Min and max subtotals

Internally, xlsgen does build actual Excel formulas, computes them and put their results in cells in the footer of each break.