How Create Excel Pivot table report from Navision?

Standard

microsoft-excel-logoA 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)

pivot table

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

12

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

  1. start  record the macro
  2. update the required changes in excel file
  3. Stop macro
  4. Edit macro then you will get  the visual basic code check the imageCapture
  5. Convert VB code to Cal code  update navision
Advertisements

6 thoughts on “How Create Excel Pivot table report from Navision?

  1. This looks complicated. Im sure its very effective, but at what quantity of sheets to be processed like this is it worth using code instead of creating the tables manually? Im getting into using pivot tables right now, and so far the guide i have found ( http://www.excel-aid.com/excel-create-a-pivot-tablecreating-a-basic-pivot-table-from-excel-data.html ) tells me, that its fairly fast and easy to do. So i wonder whether or not i should use your code, if i only have a few sheets to be processed every week.

    Like

  2. Hai Nguyen

    Thanks for your post. It’s very helpful. Can you please also guide how to create pivot chart from the pivot cache? Thank you.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s