A pivot table is a data processing tool used to query, organize and summarize data or information between spreadsheets, tables or databases. Dragging and dropping fields into a pivot table facilitates rotational, or pivotal, structural changes.
Using excel automation we can create the excel Pivot table report from Navision
This is one example to analyze the customer ledger entries records in table 21
Create one blank report with dataitem Cust. Ledger Entry and update the reports Processonly status to true
Add below variable’s in report (click the image to view the enlarge image)

Cust. Ledger Entry - OnPreDataItem()
CREATE(Excel);
Excel.Workbooks.Add();
Sheet:=Excel.ActiveSheet;
Sheet.Name := 'CUST_LEDGER';
Window.OPEN(Text00001);
Excel.Visible(TRUE);
I:=5;
Sheet.Range('A1').Value:= "Cust. Ledger Entry".TABLECAPTION;
Sheet.Range('C1').Value:= 'USER ID ';
Sheet.Range('D1').Value:=USERID;
Sheet.Range('C2').Value:= 'DATE ';
Sheet.Range('D2').Value:=TODAY;
UpdateExcelrange;
Sheet.Range(ExcelRange[1]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Entry No.");
Sheet.Range(ExcelRange[2]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Posting Date");
Sheet.Range(ExcelRange[3]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Customer No.");
Sheet.Range(ExcelRange[4]).Value:=Cust.FIELDCAPTION(Cust.Name);
Sheet.Range(ExcelRange[5]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Document No.");
Sheet.Range(ExcelRange[6]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry".Description);
Sheet.Range(ExcelRange[7]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Global Dimension 1 Code");
Sheet.Range(ExcelRange[8]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Global Dimension 2 Code");
Sheet.Range(ExcelRange[9]).Value:="Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Amount (LCY)");
Sheet.Range(ExcelRange[10]).Value:='YEAR';
UpdateExcelrange is a function to update the Excelrange array variable
in excel YEAR column will update with month + year such as if 1st January 2013 then year value will be Jan 2013
Cust. Ledger Entry - OnAfterGetRecord()
CLEAR(Cust);
IF Cust.GET("Cust. Ledger Entry"."Customer No.") THEN ;
K:=ROUND(((I-5)/"Cust. Ledger Entry".COUNT) * 10000,1);
Window.UPDATE(1, K);
"Cust. Ledger Entry".CALCFIELDS("Cust. Ledger Entry"."Amount (LCY)");
UpdateExcelrange;
Sheet.Range(ExcelRange[1]).Value:="Cust. Ledger Entry"."Entry No.";
Sheet.Range(ExcelRange[2]).Value:="Cust. Ledger Entry"."Posting Date";
Sheet.Range(ExcelRange[3]).Value:="Cust. Ledger Entry"."Customer No.";
Sheet.Range(ExcelRange[4]).Value:=Cust.Name;
Sheet.Range(ExcelRange[5]).Value:="Cust. Ledger Entry"."Document No.";
Sheet.Range(ExcelRange[6]).Value:="Cust. Ledger Entry".Description;
Sheet.Range(ExcelRange[7]).Value:="Cust. Ledger Entry"."Global Dimension 1 Code";
Sheet.Range(ExcelRange[8]).Value:="Cust. Ledger Entry"."Global Dimension 2 Code";
Sheet.Range(ExcelRange[9]).Value:="Cust. Ledger Entry"."Amount (LCY)";
Sheet.Range(ExcelRange[10]).Value:=FORMAT("Cust. Ledger Entry"."Posting Date",0,'')+' '+
FORMAT("Cust. Ledger Entry"."Posting Date",0,'');
the Pivot table is creating on the on post data item trigger
Updated one option field in request form , user can select customer or dimension, based on the choice the pivot table row values will change
Cust. Ledger Entry - OnPostDataItem()
xlPivotCache:=Excel.ActiveWorkbook.PivotCaches.Add(1,'CUST_LEDGER!A6:J'+FORMAT(I));
xlPivotCache.CreatePivotTable('','PivotTable1');
Sheet :=Excel.ActiveSheet();
xlPivotTable := Sheet.PivotTables('PivotTable1');
Sheet.Name := 'Cust_Ledger_Pivot';
xlPivotField := xlPivotTable.PivotFields('YEAR');
xlPivotField.Orientation := 1;
xlPivotField.Position := 1;
IF "Pivot Base"="Pivot Base"::Customer THEN
xlPivotField := xlPivotTable.PivotFields(Cust.FIELDCAPTION(Cust.Name))
ELSE IF "Pivot Base"="Pivot Base"::Dimension THEN
xlPivotField := xlPivotTable.PivotFields("Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Global Dimension 1 Code"));
xlPivotField.Orientation := 1;
xlPivotField.Position := 2;
xlPivotField := xlPivotTable.PivotFields("Cust. Ledger Entry".FIELDCAPTION("Cust. Ledger Entry"."Amount (LCY)"));
xlPivotTable.AddDataField( xlPivotField);
xlPivotField := xlPivotTable.DataPivotField;
xlPivotField := xlPivotTable.PivotFields('YEAR');
xlPivotField.Orientation := 2;
xlPivotField.Position := 1;
pivot table output

code for update excel range function
UpdateExcelrange()
I+=1;
ExcelRange[1]:='A'+FORMAT(I);
ExcelRange[2]:='B'+FORMAT(I);
ExcelRange[3]:='C'+FORMAT(I);
ExcelRange[4]:='D'+FORMAT(I);
ExcelRange[5]:='E'+FORMAT(I);
ExcelRange[6]:='F'+FORMAT(I);
ExcelRange[7]:='G'+FORMAT(I);
ExcelRange[8]:='H'+FORMAT(I);
ExcelRange[9]:='I'+FORMAT(I);
ExcelRange[10]:='J'+FORMAT(I);
you can download the object and sample output from here
Tips
For any other update use macro functionality from excel
steps
- start record the macro
- update the required changes in excel file
- Stop macro
- Edit macro then you will get the visual basic code check the image

- Convert VB code to Cal code update navision
8.487495
76.948623