Home > Ax Technical > Quick Excel report – Dynamics Ax 2009

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.

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
  1. James Chacko
    July 5, 2012 at 8:30 am

    Hi Casper, I have some reports which have pictures in them.
    I save the report as HTML and then open the HTML file with Excel and do the same.
    Although I do get all the data aligned properly, the pictures are a pain to deal with.
    First of all they are all linked (I found a work around by selecting all the pictures and doing a Paste Special – Value. But still the pictures are not aligned and I need to spend a lot of time trying to align the pictures in line with the data.
    Do you have any advice on working with pictures?
    Thanks, James.

  2. December 23, 2011 at 9:14 pm

    If you’re doing the “dirty route”, there is a much easier and FASTER way. Just do infolog(strfmt(“%1|%2|%3”)); all of your records. Then right click, copy to clipboard. Then paste in excel, and then delete the first two columns. Then Copy/paste the 3rd column to notepad, then copy/paste back into excel and when you copy/paste back INTO excel, it gives you the text import wizard if you choose to use it.

    This method lets you throw one infolog into any loop, then after a few copy/pastes get it formatted nicely in excel.

  3. Kamali
    June 4, 2011 at 6:27 am

    Hi Santosh,
    Its really very good job. Most of the customer needs the excel reports.
    “Short and Sweet”.

  4. May 20, 2011 at 9:12 am

    Awesome 🙂 Easiest way to write data into excel without actually requiring the sysExcel classes.

    • May 21, 2011 at 2:05 am

      Yes santosh. I have been effectively using it at most of my customer sites to display changes/Invalid data etc..

  1. May 19, 2011 at 3:13 am
  2. May 9, 2011 at 10:43 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: