Archive

Posts Tagged ‘excel’

Quick Excel report – Dynamics Ax 2009

May 9, 2011 7 comments

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.

Create a similar code for your need. [Follow inline comments to understand the code]

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);
}

You can continue to either watch the video or text based on your convenience.

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.

Advertisements