Table breaks

xlsgen > overview > Table breaks

xlsgen business intelligence features include the ability to break tables in sections according to criterias.

The most obvious criteria is distinct values, which is straight forward on columns whose data type is a string. This mechanism allows to break on every other value, and create a section accordingly making it easier to understand the actual data.

The other criteria is a generic formula.

The capabilities are exposed in the IXlsTableBreaks interface.

Breaking on distinct values

Here is the raw data we are working with :

Data before applying breaks

The City column has a set of two distinct values : {Rockwell, Marver}. By breaking on distinct values, xlsgen creates the corresponding sections.

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

And the following result is obtained :

Breaking on distinct values

Breaking on a generic formula

Whenever the criteria to break the table is more complex than just distinct values, xlsgen exposes a generic formula mechanism, which can use one or more column names and any Excel formula. An example follows :

In the first example, the data we have is a capture from For(eign)Ex(change) currency data, which is a time series capturing forex data from 3:00 PM to 5:30 PM on a particular day, on a per minute basis.

Data before applying breaks

We may be interested in breaking all transactions per hour, therefore break on the Time column according to a criteria involving the HOUR() function :

worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Time").Breaks
pb.Formula = "=HOUR([Time])"
pb.Apply()

Doing so we obtain 3 breaks, one for the time period [3:00-3:59], one for [4:00-4:59] and one for [5:00-5:59].

Breaking on a generic formula, =HOUR([Time])

In the second example, the generic formula mechanism can be used to enforce predefined value ranges. For instance, we have a Sales index column where the data is between 0 and 100, and we would like to put all values in the [0-80] range in a particular bucket and everything above 80 in another bucket.

Data before applying breaks

worksheet.Tables.InferFromWorksheet()
IXlsTableBreaks pb = worksheet.Tables.Item(1).DataResults.Columns.ItemByName("Sales index").Breaks
pb.Formula = "=IF([Sales index] > 80; 2; 1)"
pb.Apply()

And the following result is obtained :

Breaking on distinct value ranges (inside [0-80], and above 80)