Quick Excel report – Dynamics Ax 2009

Excel is the most convenient software to examine changes in data. Specially when you have made them code and wanted it to be tracked. That’s is one of the many reasons why excel tops to be the most used software in the world.
This article in detail will tell you how to create an ad hoc excel sheet just in case you wanted to show data changes, compile information from different tables etc.

static void JobExportToExcel(Args _args)

{

SalesLine salesLine, oldSalesLine;

str text;

TextIO io;

#WinAPI

;

//Column header for the excel. The header fields and the data field must match

//in number and order

//You can use anything as a delimiter but # is convenient as this rarely appears

//as data (other choices ~, |)

text = 'Order # Customer # Item # Lot id # New tax group # Old tax group \n';

select * from salesLine

where salesLine.TaxItemGroup == 'GST' ||

salesLine.TaxItemGroup == 'Free';

while (salesLine)

{

//save this incase you wanted to show the old value

oldSalesLine = salesLine.orig();

if (salesLine.TaxItemGroup == 'GST')

{

salesLine.TaxItemGroup = 'CAPITAL';

}

else

{

salesLine.TaxItemGroup = 'Free';

}

//append the text

text += strfmt('%1 # %2 # %3 # %4 # %5 # %6 \n', salesLine.SalesId, salesLine.CustAccount, salesLine.ItemId, salesLine.InventTransId, salesLine.TaxItemGroup, oldSalesLine.TaxItemGroup);

//do it after the update text otherwise you loose the orig

salesLine.update();

next salesLine;

}

//So far no excel :-)...that comes later

io = new TextIO(WinAPI::getFolderPath(#CSIDL_DESKTOPDIRECTORY) + '\\exportToExcel.txt', 'w');

io.write(text);

} step 1. Create a delimited text IO file through a job as shown above step 2. Open Excel (Don’t try rightclick open with). setp 3. Click Open file -> Select “ALL files” in the export dialog step 4: select the textIO file that was recently given step 5: Specify delimited in the dialog and enter the delimiter (# in this case) step 6. Click finish your data is in. Just format the column size.