|
|
|
PivotTable(MemoryTable: Object, NonPivotFields: String, PivotFields: String, PivotValueFields: String, [RowTotals: Boolean]): Variant
Returns a pivoted memory table.
The NonPivotFields and PivotValuesFields are semi-colon separated list of field names from the MemoryTable. The PivotValue fields must be specified as a list of FunctionName(FieldName) pairs. Supported functions for now are Sum and Count. RowTotals boolean is optional with default False.
For example:
Dim tblSrc as Object
tblSrc = executesql("SELECT ICTran.PeriodID " & _
" , ICTran.TransactionDate " & _
" , ICTran.ProductCode " & _
" , ICProd.Description " & _
" , ICTran.DepartmentCode " & _
" ,CostValueExclusive as Cost " & _
" ,SalesValueExclusive as Sales " & _
" FROM ICTran INNER JOIN ICProd ON " & _
" ICProd.ProductCode = ICTran.ProductCode " & _
" WHERE ICTran.TransactionType IN ('I','C','U') " & _
" AND ICTran.ProductCode IN ('T10MMRD', 'DESIGN')")
tblResult = PivotTable(tblSrc, "ProductCode;Description", _
"DepartmentCode", "sum(Cost);sum(sales)",true)
CreateReport(tblResult,"Example Pivot Table")
See also the tutorials:
MB Tutorial: IC Product Quantities by Location Pivot Table
MB Tutorial: IC Products and Prices Pivot Table
PivotTable function syntax has these named arguments:
Parameter |
Description |
Memory Table |
Required. The memory table to be used to pivot data from. |
Non Pivot Fields |
Required. A semi-colon separated list of fields from the Memory Table, not to be pivoted. |
Pivot Field |
Required. The field from the Memory Table to be pivoted. |
Pivot Value Fields |
Required. A list of Function Name (Field Name) pairs. Supported functions are Sum and Count. |
Row Totals |
Optional. When True, the pivot table returned contains row totals. Defaults to False. |
After you have created a pivot table, you can use the DocumentObject function to see the attributes of the pivot table.
|
|
|
