CreatePivotTable from Navision through Automation

We all know that to export to excel from Navision with the help of automation server. Now we are going to create pivot table for that excel with the help of automation:

1) We need to open then work book . Before that we need to Instantiate the automation server for the Excel application

     If isclear(Xlapp) then

       create(Xlapp)              // creating the instance where xlapp is the automation server of 'Microsoft Excel 12.0 Object Libray'.application

xlapp.Visible(TRUE);

xlbook:=xlapp.Workbooks.Open('path of your Excel sheet with extension');// where xlbook is the automation server of 'Microsoft Excel 12.0 Object Libray'.Workbook

XlPivotCache:=xlapp.ActiveWorkbook.PivotCaches.Add(1,'sheetname!range of cells');// where XlPivotcache is the automation server of 'Microsoft Excel 12.0 Object Libray'.Pivotcache

XlPivotCache.CreatePivotTable('','Pivottable6'); // this will create a pivottable with name as pivottable6 and create the pivot table in the new sheet.

//now we need to give the name for the sheet where the pivot table is created.

xlsheet := xlapp.ActiveSheet();

XlPivotTable := xlsheet.PivotTables('PivotTable6');

xlsheet.Name := 'Invoice Header Pivot'; 

XLPivotField := XlPivotTable.PivotFields(fieldname);

XLPivotField.Orientation:=1;// orientation is used to update the data fields in the RowLabels

XLPivotField := XlPivotTable.PivotFields(fieldname);

XLPivotField.Orientation:=1;

XLPivotField.Position:=2;// Position is used to update the data fields based on the orientation and in which position do we need that

if we need to update the columnLabels then the Orientation is 2. If we need update the Values then the orientation is 4.

Also in the Values column we have more function called sum, count,avg, etc.

we need to select that by using

XLPivotField."Function":=0(sum),1(count),.....