Pivot.PivotColumns Property (ExPivot)

The PivotColumns property specifies the list of columns that shows the summarized data. The PivotColumns property has effect only if it is valid and the PivotRows property is set. The DisplayPivotFields property specifies the number of maximum columns to be added during the execution of the current layout. Use the Layout property to save or restore the control's view once the user closes/runs the application. The Aggregates collection holds a collection of Aggregate functions the user can display to summarize the data. Use the FormatPivotAggregate / FormatPivotTotal property to display aggregate functions in a different format. The FormatConditionalAppearances helps you to provide conditional-format for your data, or in other words, ability to highlight values that matches a specified expression.

For instance:

"sum(5)[bold,content=numeric]", adds a single column that displays the sum of data column with the index 5, and its content will be displayed as numeric and in bold.
"sum(5)[content=numeric]/12", adds a column for each unique value found in the data column with the index 12, and each column displays the sum of data column with the index 5 as numeric, associated with the value of the data column.
"sum(5)[content=numeric]/12;6", groups by data columns with the index 12 and 6, and adds a new column for each unique value found, by displaying the sum of data column with the index 5 as numeric, for the associated values in the data columns.
"sum(5)[bold,content=numeric],sum(5)[content=numeric]/12;6", adds a single column that displays the sum of data column with the index 5 in bold as numeric, and follows the group-by data columns shown earlier.

The PivotColumns in BNF notation is:

PivotColumns ::= "<Aggregate>[,<Aggregate>]"
Aggregate ::= <AggregateKey>(<Index>)[<Options>] | <AggregateKey>(<Index>)[<Options>]/<Index>[:<Order>][<Options>][;<Index>[:<Order>][<Options>]]
Index ::= <Digit>[<Digit>]
Digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9
Order ::= A | D
Options ::= \[<Option> | ,<Option>\]
Option ::= <CheckOption> | content=<RadioOption>

where

AggregateKey, is any Key in the Aggregates collection
Index, is the index of the Column, a value between 0 and DataColumns.Count - 1
CheckOption is any Key in the FormatAppearances collection
RadioOption is any Key in the FormatContents collection

The following screen shot shows how the columns in the control's list, may look like if the PivotColumns property is "sum(5)[bold,content=numeric]":

The following screen shot shows how the columns in the control's list, may look like if the PivotColumns property is "sum(5)[content=numeric]/12":

The following screen shot shows how the columns in the control's list, may look like if the PivotColumns property is "sum(5)[content=numeric]/12;6":

The following screen shot shows how the columns in the control's list, may look like if the PivotColumns property is "sum(5)[bold,content=numeric],sum(5)[content=numeric]/12;6":

The control can load data using one of the following methods:

Import method, loads data from a CSV file or from specified text.
DataSource property, assigns an ADO/DAO record set to be loaded.
LoadXML method, loads an XML file previously saved using the SaveXML method
The user can drag and drop any TXT or XML files to the control.

Once the data is loaded to the control, the user can drag and drop columns to summarize the data or set the following properties in the following order:

PivotRows property, specifies the list of columns that determines the first column in the control's list
PivotColumns property, specifies the list of columns that determines the rest of the columns in the control's list
PivotTotals property, specifies the list of aggregate functions to be displayed in the control's list

		Type	Description
		String	A String expression that indicates the list of data columns that shows the summarized data. For instance, the "sum(5),sum(5)/12" display the SUM for the column with the index 5, and adds a new column for each value found in the column with the index 12, by displaying the SUM of column with the index 5.