Pivot tables

TreeGrid documentation

Grouping rows cells to two dimensional row and column tree.

How to create Pivot table.
1) First create the source (master) grid with layout and data. It should be plain grid without tree and any special functionality, to just show the data table.
2) If you want to permit changes in the Pivot grid and upload them to server, define the Upload_Url and other saving attribute in the master grid. 3) Define the Pivot grid only with partial layout, without Data_ source.
4) In the Pivot layout set the Pivot grid PivotMaster attribute to the master grid id.
5) Optionally in the Pivot layout
6) TreeGrid automatically creates the Pivot table data XML and reloads the Pivot grid
7) To dynamically change the pivot definition 8) Catching pivot creating events id of the source grid from where it loads data rows.
The master grid can be also hidden (<bdo Hidden='1' ... >) to show only the pivot grid.
PivotMaster is main attribute that defines the pivot relation.
If the pivot grid controls visibility of the master grid.

0	The visibility of pivot and master grids are independent
	To not display the master grid set its <bdo Hidden='1' ... >
	If the pivot settings are incomplete, the pivot grid is displayed empty.
1	The visibility of the pivot and master grids are controlled automatically to show only one grid at a time.
	Define the main div tags of both grid in the same parent. Set the master grid's main tag as display:none.
	Optionally define the Pivot row also in master grid to be used to set up the pivot grid.
	If the pivot settings are complete, the pivot grid is shown and the master grid is hidden.
	If the pivot settings are incomplete, the pivot grid is hidden (its main tag has display:none) and the master grid is shown.
	Since 9.3, if permitted saving Pivot configuration to cookies, it saves the display state of both grids to cookies.

A comma separated list of column names from master grid that will be used for row groups.
Every the column name will create one row tree level.
A comma separated list of column names from master grid that will be used for column groups.
Every the column name will create one column tree level.
A comma separated list of column names from master grid that will be used for data columns.
These columns usually contain numbers to be summed into parent column and row.
From which level the columns and rows will be expanded. It can be one number for both or two numbers comma separated as columns, rows.
For example "2,0" means the first two column levels will be expanded and all rows will be collapsed.
To expand all rows and columns set it to high number e.g. 100.
Maximal count of visible columns in the pivot table.
If the created columns exceed this limit, appropriate levels are always collapsed by default and only one cell per header can be expanded on these levels.
It also sets FastColumns to 0, it slows down the expanding / collapsing, but speeds up the table rendering.
Maximal value is 500, it is because of hard limits for colSpan attribute value (1000) in the most browsers.
Higher value slows down the grid rendering.
For 1 it shows all columns.
For 0 it hides expanded summary columns and shows the summary column only when it is collapsed.
If the second grid will be updated after the first grid is changed.
0 - no change in master or pivot grid will be updated in the second grid
1 - every change in pivot grid will be done in master grid. But no change in master grid will update the pivot grid.
2 - every change in pivot grid will be done in master grid. Only changes in master grid that don't change pivot grid structure (do not require pivot recreate) are done in pivot grid.
3 - Every change is mirrored in both grids. But change in master grid that requires recreating pivot grid must be confirmed by a user in confirm dialog.
4 - Every change is mirrored in both grids. If required, the pivot grid is automatically recreated.
In pivot grid there cannot be changed summary cells.
If and how will be created pivot values from filtered rows in master table.
0 - The filtered rows will be ignored
1 - The groups for filtered rows will be created, but the values from filtered rows will be ignored
2 - The filtered rows will be used as normal rows
Function used to summarize the values for pivot cell.
One pivot cell should contain all values from master grid rows data column, where the values in all columns PivotRows and PivotCols have appropriate (the same) value.

sum	sums all the number values
count	returns the count of the values
join	all the values will be merged as strings, separated by ValueSeparator (semicolon by default)
joinsum	all the values will be merged as strings, separated by ValueSeparator (semicolon by default), the same values will be counted and placed once as "count x value"
sumjoin	sums all the values. If some value is neither number not empty string, it merges the values as joinsum

The other actions with the values can be done in OnPivotFunc API event.
PivotFunc for particular PivotData column, the XXX is the pivot data column name.
For example <Cfg PivotData= 'COUNT' PivotFuncCOUNT='count' PivotFuncNAMES='join'/>
If not set, PivotFunc is used.
Called to calculate result of the pivot data cell.
grid is pivot grid, master is master grid.
id is the id of the pivot row that will contain or contains the value. It is full id that consists from the PivotRows value, separated by '$'.
col is the column in pivot grid that will contain or contains the value. It consist from the PivotCols values, separated by '$' and converted by PivotEscape
rows are all source rows in master grid from their values is created this cell.
values are all values from them the result can be calculated. Simply, the values are all values from master rows that have the same (actual) values in PivotRows and PivotCols columns.
result is actual result for the pivot cell calculated by actual PivotFunc.
Format for numbers resulted by PivotFunc sum / sumjoin.
The column named "MainCol" is automatically created column that contains the row tree. It is set also as the grid MainCol (<Cfg MainCol = "MainCol"/>).
You can control the created grouping rows and columns by the default rows and columns.
The predefined default rows:
The predefined default columns:

The default columns usually define format or type of the cells in these columns.
The default rows usually define the cell formulas for these group or data rows.

The default rows can define more cells at once by using the default column names as the cell name.
For example <D Name='PivotRow' PivotGroupFormula='sumc()'/> sets all the cells that belong to the default column "PivotGroup".

To speed up the pivot creating it is strongly recommended to set default width for all columns (like <DefCols><D Name='Pivot' Width='100'/></DefCols>), especially when many columns are created.
Value displayed in pivot table instead of the source cell value.
When creating pivot table, there is used cell SortValue attribute to group the same values. The PivotValue is used only for display in cell.
Called to get value for display in pivot table, val is predefined value, returns val or new value.
The row, col is the cell from the value is read. For data cell it is real cell in the source grid, but for pivot columns and rows it is a temporary row created by grouping and main column.
When creating pivot table, there is called OnGetSortValue event to group the same values. The OnGetPivotValue is used only for display in cell.
By formulas you can calculate the group columns and rows in the Pivot grid. Use the formulas in the default rows or columns.
Don't forget to set also CalcOrder, it is usually needed to set it in Pivot grid.
Use sum function to sum row children or root rows.
Use sumc function to sum column children or root columns.
Comma separated list of calculated cells to specify order in which the cells are calculated. See CalcOrder documentation.
When the CalcOrder is set in default row in Pivot table, it is possible to use the default column names (e.g. "PivotData") to reference all the columns that belong to the default column.
The calculation order of "PivotGroup" is reversed (calculates first the last column from that group).
Special combo box to select predefined pivots.
It presets these cell attribute: Type="Html" Button="Defaults" Formula=PivotRows/Cols/Data Defaults=Updated Defaults OnChange=change PivotRows/Cols/Data
Define Defaults attribute in this format:
Defaults="| R11,R12,...,R1X : C11,C12,...,C1X : D11,D12,...,D1X | R21,R22,...,R2X : C21,C22,...,C2X : D21,D22,...,D2X | R31,R32,...,R3X : C31,C32,...,C3X : D31,D32,...,D3X | ..... "
where R are comma separated PivotRows, C comma separated PivotCols and D comma separated PivotData. Always column names, not captions.
The separator '|' can be replaced by any character that is not included in any value.
The comma and colon can be replaced by values defined in the cell Separators attribute.
First character separated array of five separators for SelectPivot type cell.
Like XXXSeparators="|A|B|C|D|E"
A separates individual values in data in the Defaults array. Default is comma.
B separates groups (PivotRows, PivotCols and PivotData) in data in the Defaults array. Default is colon.
C separates individual values in display. Can contain HTML. Default is blue comma.
D separates PivotRows and PivotCols in display. Can contain HTML. Default is red plus.
E separates PivotCols and PivotData in display. Can contain HTML. Default is red equal.
Default value is "|,|:|<b style='color:blue;'>,</b> | <b style='color:red;'>+</b> | <b style='color:red;'>=</b> "
Called before the pivot grid data XML is created.
The Rows, Cols and Data are JavaScript arrays of parsed PivotRows, PivotCols and PivotData. The event can modify them, it will affect only the pivot creating, not the source values.
Return true to suppress creating the pivot grid.
Called after the pivot grid data XML is created, but before the pivot grid is reloaded with the new XML.
The Rows, Cols and Data are JavaScript arrays of parsed PivotRows, PivotCols and PivotData. The event cannot modify them.
Return true to suppress reloading the pivot grid with new pivot data.
To catch event after the pivot grid is created or rendered use standard loading events like OnLoaded, OnReady, OnRenderFinish.
Creates the pivot table according to the PivotRows, PivotCols and PivotData.
Returns false for error or incomplete settings.
Returns true for success. It calls Reload(), so it can return before the grid is created.
func is called after the grid is reloaded, in time of OnReady - before the grid is rendered.
For pivot = 0 it shows master grid, for pivot = 1 it shows pivot grid.
Since 15.0 for pivot = 2 shows both grids, for pivot = 3 shows pivot grid, but does not hide master grid if were shown both grids.
Only when set PivotControlMaster.
Escapes any text value to be usable as TreeGrid column name, it escapes all the incorrect characters as _XX or _uXXXX (like escape function).
Unescapes the values escaped by the PivotEscape.

Formulas

Formulas used especially for the pivot tables, but they can be used also in other grid.
They are similar to the standard aggregate functions, but they aggregate columns instead of rows.
These functions are named the same as standard aggregate functions, with "c" suffix. Please note, they have different parameters!

Basic aggregate functions

Sums cell values in actual row. It sums values in child columns of the actual column (or the column col, if set).
The child columns are listed in ExpandCols attribute of the header row. You can specify the header row by headerid or it is used the first header where the cell col has ExpandCols attribute.
If col set to empty string (not null), it sums values in root columns => the columns in that the headerid header cell has set ExpandCols, but only the columns not listed in any ExpandCols attribute in that header. If the headerid is not set, it checks the first header.
condition is JavaScript code called as method of TCalc for every cell processed. It should return true or false if the cell should be added or not. It can read the variables Grid, Row and Col to get the cell.
Returns count of rows. See sumc function.
Runs the formula for every row.
The formula is running in context of TCalc like standard formula.
The actual temporary result is stored in variable Result, formula should read and use it. Return value of formula is stored back to Result and is available for the next row formula.
Result is 0 on beginning.
The Row variable contains actual row, the Col variable the actually iterated column.
The formula can contain any JavaScript expression.
See sumc function.

Special aggregate functions

Returns maximum value from the column. If there is no row to iterate, returns "". See sumc function.
Returns minimum value from the column. If there is no row to iterate, returns "". See sumc function.
Sums all dates in column with Range=1 Type="Date". It just creates new range from all intervals and updates it to not contain duplicate value cross intervals.
See sumc function.
Sums all squares of values. See sumc function.
Counts all non blank values in the column. See sumc function.
Counts all blank values in the column. See sumc function.
Multiplies all values in column. See sumc function.

String aggregate functions

Joins values in row. The values are separated by ValueSeparator (semicolon by default).
Joins cell values in actual row. It joins values in child columns of the actual column (or the column col, if set).
The values are separated by ValueSeparator (semicolon by default).
The same values will be counted and placed once as "count x value". If the input values are already joined values, it splits them and recalculates the counts.
Sums values in actual as standard sumc function.
If any value is a not empty string, it joins the values as joinsumc.
If all the values are empty strings or no values are in the column, returns empty string instead of zero.