Export to Excel with 2 dataitems

lavanyaballurgi · 2012-06-12

Hello Experts,

I recently learned the "excel buffer" features. I tried to use it for "vendor aging report" & its working great. Problem is if I choose more than 1 vendor it generates more excel files instead of adding more rows in first excel file. I added all "excel buffer" coding in second dataitem i.e. "vendor ledger entry". I feel some of the code belongs to first dataitem i.e. "vendor" but no idea which one. Can someone help? here is coding

Vendor Ledger Entry - OnPreDataItem()
gdl_Window.OPEN(Text001 +
'@\');
gdl_Window.UPDATE(1,0);
gin_TotalRecNo := Vendor.COUNTAPPROX;
gin_RecNo :=0;
grc_Excelbuffer.DELETEALL;
CLEAR(grc_Excelbuffer);
EnterCell(1, 3, Text001+ ' '+ "Vendor No.", TRUE, FALSE, FALSE);
gin_row := 3;

Vendor Ledger Entry - OnAfterGetRecord()
Vendor.SETRANGE("No.","Vendor No.");
IF Vendor.FINDFIRST THEN
gtx_name:=Vendor.Name;

gin_row := gin_row + 1;
EnterCell(gin_row, 1, FORMAT("Posting Date"), FALSE, FALSE, FALSE);
EnterCell(gin_row, 2, FORMAT("Document Date"), FALSE, FALSE, FALSE);
EnterCell(gin_row, 3, FORMAT(gtx_name), FALSE, FALSE, FALSE);
EnterCell(gin_row, 4, FORMAT("Document No."), FALSE, FALSE, FALSE);
EnterCell(gin_row, 5, FORMAT("External Document No."), FALSE, FALSE, FALSE);
EnterCell(gin_row, 6, FORMAT("Total TDS Including SHE CESS"), FALSE, FALSE, FALSE);
EnterCell(gin_row, 7, FORMAT(Amount), FALSE, FALSE, FALSE);
EnterCell(gin_row, 8, FORMAT("Remaining Amount"), FALSE, FALSE, FALSE);

gin_RecNo := gin_RecNo + 1;
gdl_Window.UPDATE(1,ROUND(gin_RecNo / gin_TotalRecNo * 10000,1));

Vendor Ledger Entry - OnPostDataItem()
gin_row := gin_row + 1;
EnterCell(gin_row, 2, 'TOTAL FOR'+' '+ gtx_name, TRUE, FALSE, FALSE);
EnterCell(gin_row, 6, FORMAT("Total TDS Including SHE CESS"), TRUE, FALSE, FALSE);
EnterCell(gin_row, 7, FORMAT(Amount), TRUE, FALSE, FALSE);
EnterCell(gin_row, 8, FORMAT("Remaining Amount"), TRUE, FALSE, FALSE);

gdl_Window.CLOSE;

grc_Excelbuffer.CreateBook;
grc_Excelbuffer.CreateSheet(Text001,Text001,COMPANYNAME,USERID);
grc_Excelbuffer.GiveUserControl;

EnterCell(lin_RowNo : Integer;lin_ColumnNo : Integer;ltx_CellValue : Text[250];lbl_Bold : Boolean;lbl_Italic : Boolean;lbl_UnderLine :
grc_Excelbuffer.INIT;
grc_Excelbuffer.VALIDATE("Row No.",lin_RowNo);
grc_Excelbuffer.VALIDATE("Column No.",lin_ColumnNo);
grc_Excelbuffer."Cell Value as Text" := ltx_CellValue;
grc_Excelbuffer.Formula := '';
grc_Excelbuffer.Bold := lbl_Bold;
grc_Excelbuffer.Italic := lbl_Italic;
grc_Excelbuffer.Underline := lbl_UnderLine;
grc_Excelbuffer.INSERT;

mohana_cse06 · 2012-06-12

Did you try by moving below code to Vendor - OnPostDataItem trigger?

grc_Excelbuffer.CreateBook;
grc_Excelbuffer.CreateSheet(Text001,Text001,COMPANYNAME,USERID);
grc_Excelbuffer.GiveUserControl;

lavanyaballurgi · 2012-06-12

doing as you said resulted in one excel file but with only one vendor's data.

Chinmoy · 2012-06-12

Try moving the following code from Vendor Ledger Entry - OnPreDataItem to Vendor - OnPreDataItem

gdl_Window.OPEN(Text001 +
'@\');
gdl_Window.UPDATE(1,0);
gin_TotalRecNo := Vendor.COUNTAPPROX;
gin_RecNo :=0;
grc_Excelbuffer.DELETEALL;
CLEAR(grc_Excelbuffer);

Chn

lavanyaballurgi · 2012-06-12

it throws error -

Microsoft Dynamics NAV Classic

The Excel Buffer already exists.

Identification fields and values:

Row No.='1',Column No.='3'

OK

Chinmoy · 2012-06-12

Check the OnAfterGetRecord codes of both Vendor and Vendor ledger entry, where you are inserting data into the Excel buffer table. The initialization of the Row no is not proper. the value is getting reset for every vendor probably. Check if there is "gin_RecNo :=0;" or something similar anywhere else in your code.

Chn

lavanyaballurgi · 2012-06-12

yes you are right the initialization of Row No. is getting reset & thus every vendor's ledger entry is over writing the previous vendor's ledger entry. But this is the only code i've & there is no more gin_RecNo :=0 anywhere else.

Chinmoy · 2012-06-12

So, move this line to OnPreReport trigger.

lavanyaballurgi · 2012-06-12

not working :shock: it gives only one excel file with one Vendor ](*,)

gerrykistler · 2012-06-12

Make sure that the finishing of the Excel file and release to the user is in the OnPostDataItem of the Vendor DataItem.

grc_Excelbuffer.CreateBook;
grc_Excelbuffer.CreateSheet(Text001,Text001,COMPANYNAME,USERID);
grc_Excelbuffer.GiveUserControl;

If that is where you have it then review the entire code in the entire report (including any sections) and make sure that your Row No. is only set to zero at the beginning of the report and nowhere else.

Savatage · 2012-06-12

If it's a working report with sections - I put the code on the section and it works great.

I have an example with the ar aging here:
https://docs.google.com/document/d/1PPM ... Zgra4/edit

To get customers aged totals I add it to ONPOSTSECTION
SectionType=Footer;
SectionWidth=21600;
SectionHeight=1692;
KeepWithNext=No;
OnPostSection=BEGIN

Row := Row + 1;
    AmtCurrent := FORMAT(AmountDue[1]);
    Amt31to60 := FORMAT(AmountDue[2]);
    Amt61to90 := FORMAT(AmountDue[3]);
    AmtOver90 := FORMAT(AmountDue[4]);
    AmtDue := FORMAT(AmountDueToPrint);

    EnterCell(Row, 1, "Cust. Ledger Entry"."Customer No.", FALSE, FALSE, FALSE);
    EnterCell(Row, 2, Customer.Name, FALSE, FALSE, FALSE);
    EnterCell(Row, 3, AmtDue, FALSE, FALSE, FALSE);
    EnterCell(Row, 4, AmtCurrent, FALSE, FALSE, FALSE);
    EnterCell(Row, 5, Amt31to60, FALSE, FALSE, FALSE);
    EnterCell(Row, 6, Amt61to90, FALSE, FALSE, FALSE);
    EnterCell(Row, 7, AmtOver90, FALSE, FALSE, FALSE);
END;

Export to Excel with 2 dataitems

Answers

Categories