Pivot table in Excel from AX 2012

Using ComExcelDocument_RU

#define.xlDatabase(1)
#define.xlPivotTableVersion15(5)
#define.PivotTableName("Pivottable1")
#define.PivotTable("PivotTable")
void buildPivotTable(BookMark _sourceBookMark,
container _columnFields,
container _rowFields,
container _dataFields,
int _sourceWorkSheet = 1,
BookMark _destBookMark = "",
int _destWorkSheet = 1,
str _destWorkSheetName = "",
BookMark _destBookMarkCell = "")
{
COM comWorkBook, comWorkSheet;
COM comPivotCaches, comPviotCache;
COM comPivotTable, comPivotField;
COM comCommandBars, comCommandBar;
int iPivotField;
BookMark destBookMark;
;

comWorkBook = m_comApplication.activeWorkBook();
comWorkSheet = this.getWorkSheet(_sourceWorkSheet);

comPivotCaches = comWorkBook.PivotCaches();
//xlDatabase means data is in the active workbook
comPviotCache = comPivotCaches.Create(#xlDatabase, strFmt("%1!%2", comWorkSheet.name(), _sourceBookMark), #xlPivotTableVersion15);

//Empty String as destination means a new WorkSheet will be created
if (_destBookMark)

{ comWorkSheet = this.getWorkSheet(_destWorkSheet); destBookMark = strFmt("%1!%2", comWorkSheet.name(), _destBookMark); }

comPivotTable = comPviotCache.CreatePivotTable(strFmt("%1!%2", _destWorkSheetName, _destBookMarkCell), #PivotTableName, #xlPivotTableVersion15);

for (iPivotField = 1; iPivotField <= conlen(_columnFields); iPivotField++)

{ comPivotField = comPivotTable.PivotFields(conPeek(_columnFields, iPivotField)); comPivotField.Orientation(2); //xlColumnField comPivotField.Position(iPivotField); }

for (iPivotField = 1; iPivotField <= conlen(_rowFields); iPivotField++)

{ comPivotField = comPivotTable.PivotFields(conPeek(_rowFields, iPivotField)); comPivotField.Orientation(1); //xlRowField comPivotField.Position(iPivotField); }

for (iPivotField = 1; iPivotField <= conLen(_dataFields); iPivotField++)

{ comPivotTable.AddDataField(comPivotTable.PivotFields(conPeek(_dataFields, iPivotField))); }

//Hide the PivotTable FieldList
comWorkBook.ShowPivotTableFieldList(true);

//Hide the PivotTable CommandBar
comCommandBars = m_comApplication.CommandBars();
comCommandBar = comCommandBars.item(#PivotTable);
comCommandBar.visible(true);
}

Example

To create pivot table use next call:

excel.buildPivotTable(strFmt("%1:%2", "A1", ComExcelDocument_RU::numToNameCell(13, row - 1)), conCols, conRows, conData, 1, "A1", 1, "Sheet2", "R3C1");

where

container conCols = ["Branch",”Region","Shop","Item type","Group","Subgroup","Item"];
container conRows = ["Branch",”Region","Shop","Item type","Group","Subgroup","Item"];
container conData = [“Sales Qty for Period PCS","Sales Amount RUB","Sales Cost"];