Pivot table in Excel from AX 2012

Updated: 15.02.2016

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"];

 

 

Other blog posts

24.05.2018
Disclaimer
08.05.2018
We apply OKR methodology in Neti since 2014. We started from a quarterly planning, but then understood that no ambitious goals can be achieved per quarter. So we switched to semi-annual intervals....
11.04.2018
As you know, Microsoft recently has launched Dynamics 365 Business Central. And we are proud to announce we have launched intracompany training for sales and development departments:Why do we need it...

Subscribe for blog updates

Get updated with our blog new articles via your email!


Subscribe now