How to Export Total sum of Report's Value in Excel

Business Central/NAV Developers Forum
1

Hi All,

I have a customer sale report which is working fine with exporting data to excel. Now i add some filed to calculate total sum of values & want to export these to excel and below is my report code:-

Customer - OnPreDataItem()
CurrReport.NEWPAGEPERRECORD := PrintOnlyOnePerPage;
CurrReport.CREATETOTALS(
ValueEntryBuffer.“Sales Amount (Actual)”,
ValueEntryBuffer.“Discount Amount”, // Total of this filed i want to export into excel

ValueEntryBuffer.“Invoiced Quantity”, // Total of this filed i want to export into excel
Profit);

MakeExcelInfo()
ExcelBuf.SetUseInfoSheed;
ExcelBuf.AddInfoColumn(FORMAT(Text003),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(COMPANYNAME,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text005),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(FORMAT(Text002),FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text004),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(REPORT::“Customer/Item Sales”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text006),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(USERID,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text007),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(TODAY,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text008),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(Customer.GETFILTERS,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.NewRow;
ExcelBuf.AddInfoColumn(FORMAT(Text009),FALSE,’’,TRUE,FALSE,FALSE,’’);
ExcelBuf.AddInfoColumn(“Value Entry”.GETFILTERS,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.ClearNewRow;
MakeExcelDataHeader;

MakeExcelDataHeader()
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Customer.FIELDCAPTION(“No.”),FALSE,’’,TRUE,FALSE,TRUE,’@’);
ExcelBuf.AddColumn(Customer.FIELDCAPTION(Name),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(ValueEntryBuffer.FIELDCAPTION(“Item No.”),FALSE,’’,TRUE,FALSE,TRUE,’@’);
ExcelBuf.AddColumn(Item.FIELDCAPTION(Description),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(ValueEntryBuffer.FIELDCAPTION(“Invoiced Quantity”),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(Item.FIELDCAPTION(“Base Unit of Measure”),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(ValueEntryBuffer.FIELDCAPTION(“Sales Amount (Actual)”),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(ValueEntryBuffer.FIELDCAPTION(“Discount Amount”),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(FORMAT(Text010),FALSE,’’,TRUE,FALSE,TRUE,’’);
ExcelBuf.AddColumn(FORMAT(Text011),FALSE,’’,TRUE,FALSE,TRUE,’’);

MakeExcelDataBody()
ExcelBuf.NewRow;
ExcelBuf.AddColumn(Customer.“No.”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(Customer.Name,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(ValueEntryBuffer.“Item No.”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(Item.Description,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(-ValueEntryBuffer.“Invoiced Quantity”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(Item.“Base Unit of Measure”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(ValueEntryBuffer.“Sales Amount (Actual)”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(-ValueEntryBuffer.“Discount Amount”,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(Profit,FALSE,’’,FALSE,FALSE,FALSE,’’);
ExcelBuf.AddColumn(ProfitPct,FALSE,’’,FALSE,FALSE,FALSE,’’);

CreateExcelbook()
ExcelBuf.CreateBook;
ExcelBuf.CreateSheet(Text001,Text002,COMPANYNAME,USERID);
ExcelBuf.GiveUserControl;
ERROR(’’);

Pls help me and suggest some solution how i can export it into excel.

Thanx

Neel

2

When you need them in bottom of excel then you need to add in OnPost dataitem trigget by increasing the row values…

3

Mohana Sir,

Thanx for ur quick reply and i want to sum & export values customer wise only, because report showing total customer wise very well.

4

Then you need to add it in Onpost section of groupfooter