I just finished creating a report that can export job ledger entries to Excel and build a Pivot Table for analysis of the data.  Global dimensions and data from the master and supplement tables are included in the export.  A similar report can be built for other ledger tables.

The first step is to create a workbook.

Fields for rows and columns are mandatory.  They can be selected from a field list.

The user can filter the job ledger entry table before exporting.  A text file will be created and opened by Excel.  I use a system object to create the text file in the system code page.  This will result in a simple pivot table in Excel.

The Excel workbook will include three sheets.  One for the pivot table, one for filter information and one for the data.  The user can now customize the pivot table to display what ever is needed and then save the workbook to a secure location.  When saving a dialog similar to the one below will appear.  Here the uses selects No and saves the workbook in an Excel format.

Then the user can update this workbook with new data.  When updating a workbook the previous data is completely replaced.  Run the same report and change the option to Update Workbook.  Select the Excel Document and the sheet names will be automatically applied with standard names.

This will give the user a fresh data and a updates pivot table that can be saved to a different file name if needed.  Then code is somewhat based on the solution from Microsoft that exports Account Schedules to pivot table and I use four basic functions as shown below.
[code htmlscript=”false”]CreateExcel()
CREATE(xlApp,TRUE,TRUE);

CreateWorkbook()
WITH "Job Ledger Entry" DO BEGIN
xlApp.Workbooks.OpenText(TextFileName);
xlWorkSheet := xlApp.ActiveSheet;
xlSheetName := GetDataSheetName;
xlWorkSheet.Name := xlSheetName;

xlApp.ActiveWorkbook.Sheets.Add();
xlWorkSheet3 := xlApp.ActiveSheet;
xlWorkSheet3.Name := GetInfoSheetName;
xlWorkSheet3.Range(‘A1’).Value := FORMAT(Text009);
xlWorkSheet3.Range(‘B1’).Value := TABLECAPTION;

IF GETFILTER("Work Type Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A2’).Value := FIELDCAPTION("Work Type Code") + Text010;
xlWorkSheet3.Range(‘B2’).Value := GETFILTER("Work Type Code");
END;
IF GETFILTER("Entry Type") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A3’).Value := FIELDCAPTION("Entry Type") + Text010;
xlWorkSheet3.Range(‘B3’).Value := GETFILTER("Entry Type");
END;
IF GETFILTER("Posting Date") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A4’).Value := FIELDCAPTION("Posting Date") + Text010;
xlWorkSheet3.Range(‘B4’).Value := GETFILTER("Posting Date");
END;
IF GETFILTER("Global Dimension 1 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A5’).Value := FIELDCAPTION("Global Dimension 1 Code") + Text010;
xlWorkSheet3.Range(‘B5’).Value := GETFILTER("Global Dimension 1 Code");
END;
IF GETFILTER("Global Dimension 2 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A6’).Value := FIELDCAPTION("Global Dimension 2 Code") + Text010;
xlWorkSheet3.Range(‘B6’).Value := GETFILTER("Global Dimension 2 Code");
END;
IF GETFILTER("Job No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A7’).Value := FIELDCAPTION("Job No.") + Text010;
xlWorkSheet3.Range(‘B7’).Value := GETFILTER("Job No.");
END;
IF GETFILTER("Job Task No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A8’).Value := FIELDCAPTION("Job Task No.") + Text010;
xlWorkSheet3.Range(‘B8’).Value := GETFILTER("Job Task No.");
END;
IF GETFILTER("Job Posting Group") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A9’).Value := FIELDCAPTION("Job Posting Group") + Text010;
xlWorkSheet3.Range(‘B9’).Value := GETFILTER("Job Posting Group");
END;
xlWorkSheet3.Range(‘A1:B9’).Columns.AutoFit;

IF NoOfRows &gt; 0 THEN BEGIN
ExcelBuffer.VALIDATE("Column No.",NoOfColumns);
PivotTableRange := STRSUBSTNO(‘%1!$A:$%2′,xlSheetName, ExcelBuffer.xlColID);
xlWorkSheet.Range(PivotTableRange).Columns.AutoFit;
xlPivotCache := xlApp.ActiveWorkbook.PivotCaches.Add(1,PivotTableRange);
xlPivotCache.CreatePivotTable(”,’PivotTable1’);

xlWorkSheet2 := xlApp.ActiveSheet();
xlPivotTable := xlWorkSheet2.PivotTables(‘PivotTable1′);
xlWorkSheet2.Name := GetPivotSheetName;

xlPivotField := xlPivotTable.PivotFields(RowFieldName);
xlPivotField.Orientation := 1; //xlRowField
xlPivotField.Position := 1;

xlPivotField := xlPivotTable.PivotFields(ColFieldName);
xlPivotField.Orientation := 2; //xlColumnField
xlPivotField.Position := 1;

xlPivotField := xlPivotTable.PivotFields(FIELDCAPTION("Total Price (LCY)"));
xlPivotField.Orientation := 4; //xlDataField
xlPivotField.Position := 1;
xlPivotField."Function" := 0; //sum
xlPivotField.Caption := STRSUBSTNO(Text026,FIELDCAPTION("Total Price (LCY)"));

FormatString := DELCHR(FORMAT(ROUND(1000.01,GLSetup."Unit-Amount Rounding Precision"),0),’ ” THEN BEGIN
xlWorkSheet3.Range(‘A2’).Value := FIELDCAPTION("Work Type Code") + Text010;
xlWorkSheet3.Range(‘B2’).Value := GETFILTER("Work Type Code");
END;
IF GETFILTER("Entry Type") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A3’).Value := FIELDCAPTION("Entry Type") + Text010;
xlWorkSheet3.Range(‘B3’).Value := GETFILTER("Entry Type");
END;
IF GETFILTER("Posting Date") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A4’).Value := FIELDCAPTION("Posting Date") + Text010;
xlWorkSheet3.Range(‘B4’).Value := GETFILTER("Posting Date");
END;
IF GETFILTER("Global Dimension 1 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A5’).Value := FIELDCAPTION("Global Dimension 1 Code") + Text010;
xlWorkSheet3.Range(‘B5’).Value := GETFILTER("Global Dimension 1 Code");
END;
IF GETFILTER("Global Dimension 2 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A6’).Value := FIELDCAPTION("Global Dimension 2 Code") + Text010;
xlWorkSheet3.Range(‘B6’).Value := GETFILTER("Global Dimension 2 Code");
END;
IF GETFILTER("Job No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A7’).Value := FIELDCAPTION("Job No.") + Text010;
xlWorkSheet3.Range(‘B7’).Value := GETFILTER("Job No.");
END;
IF GETFILTER("Job Task No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A8’).Value := FIELDCAPTION("Job Task No.") + Text010;
xlWorkSheet3.Range(‘B8’).Value := GETFILTER("Job Task No.");
END;
IF GETFILTER("Job Posting Group") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A9’).Value := FIELDCAPTION("Job Posting Group") + Text010;
xlWorkSheet3.Range(‘B9’).Value := GETFILTER("Job Posting Group");
END;
xlWorkSheet3.Range(‘A1:B9’).Columns.AutoFit;

END;

xlRange := xlWorkSheet.Range(‘A1’).SpecialCells(11);
ExcelBuffer.VALIDATE("Column No.",xlRange.Column);
xlWorkSheet.Range(STRSUBSTNO(‘A1:%1%2’,ExcelBuffer.xlColID,xlRange.Row)).ClearContents;
xlRange := xlWorkSheet.Range(‘$A$1’);
xlQueryTables := xlWorkSheet.QueryTables;
xlQueryTable := xlQueryTables.Add(STRSUBSTNO(‘TEXT;%1’,TextFileName),xlRange);
xlQueryTable.Name := Text025;
xlQueryTable.FieldNames := TRUE;
xlQueryTable.RowNumbers := FALSE;
xlQueryTable.FillAdjacentFormulas := FALSE;
xlQueryTable.RefreshOnFileOpen := FALSE;
xlQueryTable.RefreshStyle := 1; //xlInsertDeleteCells
xlQueryTable.SavePassword := FALSE;
xlQueryTable.SaveData := FALSE;
xlQueryTable.AdjustColumnWidth := TRUE;
xlQueryTable.RefreshPeriod := 0;
xlQueryTable.TextFilePromptOnRefresh := FALSE;
xlQueryTable.TextFilePlatform := 1252;
xlQueryTable.TextFileStartRow := 1;
xlQueryTable.TextFileParseType := 1; //xlDelimited
xlQueryTable.TextFileTextQualifier := 1; //xlTextQualifierDoubleQuote
xlQueryTable.TextFileConsecutiveDelimiter := FALSE;
xlQueryTable.TextFileTabDelimiter := TRUE;
xlQueryTable.TextFileSemicolonDelimiter := FALSE;
xlQueryTable.TextFileCommaDelimiter := FALSE;
xlQueryTable.TextFileSpaceDelimiter := FALSE;
xlQueryTable.TextFileTrailingMinusNumbers := TRUE;
xlQueryTable.Refresh(FALSE);

xlWorkSheet2.Select;
xlPivotTable := xlWorkSheet2.PivotTables(‘PivotTable1’);
xlPivotTable.RefreshTable;

ShowExcel()
xlApp.Visible := TRUE;[/code]
And after creating the text file these functions are executed.
[code htmlscript=”false”]CreateExcel;
IF ExcelOption = ExcelOption::"Create Workbook" THEN
CreateWorkbook
ELSE
UpdateWorkbook;
ShowExcel;[/code]
If you, your company or your client would like to have this solution for other ledger tables just contact me.

The zipped attachment below is encrypted, please look at the product page for more information.

Job Pivot Report

 

2 thoughts on “Creating an Excel Pivot table from a ledger table

  1. M.Hartung says:

    Hi.

    The linked Pages exists no more.

    Whats conditions to get the password for zipped attachment ?

    1. Thank you.
      Just drop me an email to talk about the zip files.

Leave a Reply

%d bloggers like this: