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

14.11.2017
Why outsourcing company is more effective than In-house IT-specialists There are situations when in-house specialists spend most of their time awaiting requests or they cannot get their work...
26.09.2017
Microsoft Dynamics AX program decisions are constantly improving. At first, innovations come to European companies, and only after that to Russian enterprises.We aspire to be one step ahead in this...
28.08.2017
Notes of the summit and of Dynamics market in Russia. Neti has been specializing in AX development since 2003. Our sphere of interest is the cutting-edge MS Dynamics technology. We find, study...

Subscribe for blog updates

Get updated with our blog new articles via your email!


Subscribe now