Export data to EXCEL | AX 2009 | AX 2012 | Excel Report

Hi Techies,

Hope you are doing good. Recently due to INDIA GST Update in Microsoft dynamics AX most of the clients want to export data in excel to prepare GSTR excel files.

I designed a good excel worksheet with X++ and exported data as per requirement. I am sharing with you how can you design excel sheets with the help of X++ code. Please check below.

Step 1. Create a Class. This is class declaration method.

public class MKS_ExportToExcel extends Runbase
{
    #AviFiles
    DialogField         dlgFromDate,dlgToDate;
    TransDate           fromDate,toDate;

    //Excel file declaration
    SysExcelApplication  xlsApplication;
    SysExcelWorkBooks    xlsWorkBookCollection;
    SysExcelWorkBook     xlsWorkBook;
    SysExcelWorkSheets   xlsWorkSheetCollection;
    SysExcelWorkSheet    xlsWorkSheet;
    SysExcelRange        xlsRange;
    int                  row;
    str                  fileName;

    VendInvoiceJour     vendInvoiceJour;

    #DEFINE.CurrentVersion(1)
    #LOCALMACRO.CurrentList
        fromDate,
        toDate
    #ENDMACRO
}

Step 2. Create Dialog method.

protected Object dialog()
{
    Dialog      dialog = super();
    #resAppl
    ;

    // Set a title for dialog
    dialog.caption('Inward Supply Register Report');
    dialog.addTabPage('Selection Criteria');
    dialog.addGroup('Select date range');
    // Add a new field to Dialog
    dlgFromDate = dialog.addFieldValue(typeid(TransDate), fromDate,"From date");
    dlgToDate   = dialog.addFieldValue(typeid(TransDate), toDate, 'To date');

    return dialog;

}

Step 3. Create getFromDialog method.

public boolean getFromDialog()
{
    ;
    // Retrieve values from Dialog
    fromDate = dlgfromDate.value();
    toDate = dlgToDate.value();

    return super();

}

Step 4. main Method

public static void main(Args _args)
{
     MKS_ExportToExcel reportClass = new MKS_ExportToExcel();

    // Prompt the dialog, if user clicks in OK it returns true
    if (reportClass.prompt())
    {
        reportClass.run();
    }

}

Step 5. run method.

public void run()
{
    ;
    this.setExcelInitialsAndHeader();
    this.fetchDataIntoExcel();
    this.endExcel();

}

Step 6. setExcelInitialsAndHeader - To setup Excel report heading section.

public void setExcelInitialsAndHeader()
{
    COM                     range;
    COM                     autoFit;
    COM                     merge;
    COM                     WrapText;
    COM                     ColumnWidth;
    COM                     RowHeight;

    SysExcelStyles          styles;
    SysExcelStyle           style;
    SysExcelFont            font;
    SysExcelCells           cells;
    FileNameFilter          filter = ["Excel file", "*.xlsx"];
    ;

    row = 1;

    fileName = winapi::getSaveFileName(infolog.hWnd(), filter , @"c:\...\desktop", "Save as Excel file","xlsx","Report name");
    //fileName ="C:\\Windows\\Temp\\test.xlsx";

    if(!fileName)
    return;
    //Check whether the document already exists
    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);
    //throw error("File already exist");


    //Initialize Excel instance
    xlsApplication           = SysExcelApplication::construct();

    //Open Excel document
    //xlsApplication.visible(true);


    //Create Excel WorkBook and WorkSheet
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);

    Styles = xlsWorkBook.styles();
    // Create new style
    style = styles.add('Header');
    // Set font for this style to bold
    font = style.font();
    font.bold(true);
    font.color(51);


    //Excel Report Heading
    range = xlsWorkSheet.range('A1:F1').comObject();
    range.merge();
    RowHeight = range.RowHeight(30);

    xlsWorkSheet.rows().item(1).style('Title');

    range = xlsWorkSheet.range('A2:BE2').comObject();
    xlsWorkSheet.rows().item(2).style('Output');
    xlsWorkSheet.rows().item(2).horizontalAlignment(-4108);
    wrapText = range.wraptext(true);

    ColumnWidth = range.ColumnWidth(14.71);

    xlsWorkSheet.cells().item(row,1).value('GST Report');
    row++;

    //Excel column caption
    xlsWorkSheet.cells().item(row,1).value('Sr. No.');
    xlsWorkSheet.cells().item(row,2).value('Vendor Code');
    xlsWorkSheet.cells().item(row,3).value('Vendor Name');
   
    row++;

}

Step 6. endExcel -  To Setup process while closing excel.

public void endExcel()
{
    ;
    // Set the column width to autoFit
    xlsWorkSheet.columns().autoFit();
    //Save Excel document
    xlsWorkbook.saveAs(fileName);


    xlsWorkbook.comObject().save();
    xlsWorkbook.saved(true);
    //Open Excel document
    xlsApplication.visible(true);

}

Step 7. fetchDataIntoExcel - Used to put data into excel rows.

public void fetchDataIntoExcel()
{
    VendTable               vendTable;
    CompanyInfo             companyInfo = CompanyInfo::find();
    TaxWithholdTrans_IN     tdsTable;
    LedgerJournalTrans      ledgerJournalTrans;

    ;
    progress = new RunbaseProgress();
    progress.setCaption("Export To Excel in progress...");
    progress.setAnimation(#AviTransfer);

    while select vendInvoiceJour
    order by    vendInvoiceJour.InvoiceDate
        where vendInvoiceJour.InvoiceDate >= fromDate
        && vendInvoiceJour.InvoiceDate <= toDate

    {
        vendTable = VendTable::find(vendInvoiceJour.InvoiceAccount);
        progress.setText(strfmt("Vendor %1", vendTable.Name));

        xlsWorkSheet.cells().item(row,1).value(row-2);
        xlsWorkSheet.cells().item(row,2).value(vendInvoiceJour.InvoiceAccount);
        xlsWorkSheet.cells().item(row,3).value(vendTable.name);

row++;
    }


}

Step 8. PACK and UnPack methods.

container pack()
{
    return [#CurrentVersion,#CurrentList];

}


public boolean unpack(container _packedClass)
{
    int version = conPeek(_packedClass,1);

    switch (version)
   {
        case #CurrentVersion:
            [version,#CurrentList] = _packedClass;
            break;
        default:
            return false;
    }
    return true;

}

How can I see the SQL query that the Dynamics AX Kernel is generating for my Form?

When you are debugging your solution you sometimes might want to see the SQL Query the Dynamics AX Kernel has generated for your Form. 

While you can of course trace this directly at your Database Server, you also can do this in X++ using the QueryBuildDataSource class.

Let's imagine you have a Form that has PurchTable as Data Source. You have to override the executeQuery method of the PurchTable Data Source and add the following code:

public void executeQuery()
{
   Query q;
   QueryBuildDataSource qbds;
   ;
   q = this.query();
   qbds = q.dataSourceName("PurchTable"); //Replace with the                                                 current Table
   info( qbds.toString() );

   super();
}

Announcing Compatibility Certification of SQL Server 2012 and SQL Server 2012 SP1 with Dynamics AX 2009 SP1

Dynamics AX In-Market Engineering Team is proud to announce the following compatibility of released version of Dynamics AX with SQL Server 2012 and SQL Server 2012 SP1.
Required Hotfix with KB Number: 2836535

Microsoft Product	Dynamics AX Versions
SQL Server 2012 and SQL Server 2012 SP1	Dynamics AX 2009 SP1

The System requirements of Dynamics AX 2009 SP1 have been updated. 

Reference : https://community.dynamics.com/ax/b/dynamicsaxsustainedengineering/archive/2013/04/18/announcing-compatibility-certification-of-sql-server-2012-and-sql-server-2012-sp1-with-dynamics-ax-2009-sp1.aspx#.UX7LR8pYWSo 

Add User to admin group from SQL / back end In AX 2009

Hi All,

When your user is not having any permissions to make changes in AX and you are unable to change the permission from ax then u can try to change the user group of that user from SQL studio manager i.e. back end.

Select the database on which you have to make changes.
check the user id and information with the following queries.

Select * from USERGROUPINFO

Select * from USERGROUPLIST 
(check which is the group assigned to your user and which you need)


Here i know that the usergroup ADMIN is having all the rights so i'm adding my user to admin group

Update USERGROUPLIST set GROUPID='Admin' where USERID='Kishr'

Make Form/Report run automatically when dynamics Ax Starts

When ax starts Kernel Creates an instance of Info class . 
Info contains StartupPost() method used to execute the code every time ax starts.

Following example opens InventTable Form automatically when you start ax.

void startupPost()
{
SysSetupFormRun formRun;
Args args = new Args();
;

args.name(formstr(InventTable));
formRun = classfactory::formRunClassOnClient(args);
formRun.init();
formRun.run();
formRun.detach();
}

So if you have any task that has to be executed every time ax start , then this is the best place to put your code.

Source : http://learnax.blogspot.in/search?updated-min=2010-01-01T00:00:00-08:00&updated-max=2011-01-01T00:00:00-08:00&max-results=50

Axapta(X++) Glossary with their definition

Widely used Axapta Terms and their Definition

ALC

Microsoft Dynamics AX Label Description files have the extension .alc. Also see ALD and ALI. 

ALD

Label data files have the extension .ald (AX Label Data). Also see ALC and ALI. 

ALI

Microsoft Dynamics AX Label Index files have the extension .ali. Also see ALC and ALD. 

AOS

Application Object Server is windows service used to coordinate with different components of Dynamics Ax. 

AOT

Application object tree , repository that stores metadata information about the objects created in Axapta.

Base data

Data which is customer-dependent. Examples: Customers, Vendors, Items. 
This is often data from an existing system, which must be entered or imported into 
Microsoft Axapta. 


Control

Graphical object, such as a text box or command button that you put on a form or 
report to display data, perform an action, or make the form or report easier to 
read. 

CRUD

An abbreviation for the four basic database operations: Create, Read, Update, 
Delete. 

DCOM

Distributed COM 

Default data

Data which is customer-independent. Examples: Zip codes, Address formats, Time 
Intervals, Units, Unit conversions, VAT parameters, Transaction texts. 
Note 
When a user modifies default data, it becomes custom data, which is customer- 
dependent. This means that default data can sometimes be customer dependent, 
because of historic reasons, such as Chart of Accounts. 

Domain 

Collection of one or more companies. Domains enable you to define user groups that 
have the same permissions in more than one company while allowing the same user 
groups to have other permissions within other companies. 

EDT

Extended Data Type: a user-defined data type based on a primitive data type or 
container. 

IDE

Integrated Development Environment. MorphX is the Microsoft Dynamics AX IDE. 

IntelliMorph

The Runtime Environment embedded in Microsoft Dynamics AX, that draws menus, forms, 
and reports for Windows- and Web-clients with the correct contents, size, and 
layout according to: 
the language your texts are displayed in. 
what features you can access. 
how wide you want the fields on your installation. 
the formats you are using for dates and numbers. 

MorphX

The Development Environment of Microsoft Dynamics AX, including: 
Data Dictionary 
Tools for creating menus, forms and reports for Windows- and Web clients 
Compiler and debugger for the object oriented programming language X++ 
Version control system 
Label (multi language text) systems 

Overload

Provide more than one method with the same name but with different signatures to 
distinguish them. 
Overloading is not supported by X++. Also see override. 

Override

Replace the superclass's implementation of a method with one of your own. The 
signatures must be identical. 

Note 
Only non-static methods may be overridden. 

Record ID 

A record ID uniquely identifies a row of data in a table. Record IDs are integers. They are assigned and managed by Microsoft Dynamics AX. 

super () 

Reference to the system class that contains the required method. When super() is used, the system method is automatically used. 

this 

Reference to the current object. this is frequently used as a parameter to methods 
that need an object reference.for e.g at Table level if it used gives you the 
selected record. 

TTS 

Transaction Tracking System. For more information, see Transaction Integrity. 

WinAPI

Windows Application Programming Interface. Contains System level API's 
like "WinAPI::shellExecute("file.exe");" for opening applications from axapta.

Source : http://learnax.blogspot.in/2010_01_01_archive.html

For Ax Developers

Keep following things in your mind before you start coding in Ax.

1. Whenever you came accross new functionality , don't start coding immediately please first cross check whether the same/similar kind of functionality is there in the standard product. By doing so you will save time for coding,learn ax coding style and also this will help you to understand the various functionalities in the standard product.

2. Start with pseudo-code , just draw sketch on paper how you want the data to flow , which will help you in understanding the different override methods in Axapta.

3. Code should be modular , Instead of writing all the code in one method please write plug and play functions.

Source :http://learnax.blogspot.in/2010_01_01_archive.html

X++ Code Optimzation

Following are the coding tips to improve your Axapta system's performance:
1. Smart Joins : Try to Use Joins instead of nested while loop 
wherever possible.
2. Select Statement : Mention the field names in the select statement 
instead of feteching entire row , this will reduce 
data amount to transfer from database.
e.g " Select Itemid from inventTable "

3. Display Methods : Make sure that generic display methods should be 
moved at table level and cached by 
using"Formdatasource.cacheAddmethod". 
4. Local caching : Use Variables for storing the constantly used 
caculated values in loop , by doing so you can 
reduce the calls to the database and different 
layers.
5. Monitor the Database Calls : For bulk records 
updation,deletion,insertion use 
RecordSet Based operator like 
update_recordset , delete_from and insert_recordset .
6. Aggregate Function: Use sum, avg, minof, maxof and count where 
applicable. Because this can utilize the database’s 
built-in function instead of calculating and analyse 
data in Axapta.

Source : http://learnax.blogspot.in/2010_01_01_archive.html

Copying License in Dynamics Ax

Axapta stores the License data in the following two tables.

1.SysLicenseCodeSort
2.SysConfig

So if you copy data of these tables(from Running instance) and import at your instance , this will serve your purpose.

Temporary data insert

CustTable  CustTableTmp;
;
CustTableTmp.setTmp();
while select CustTable
{
    CustTableTmp.data(CustTable.data());
    CustTableTmp.doInsert();
}

Select CustTableTmp;

How to enable / disable form control through code in Ax 2009

Hi Friends,

I was designed new form for one of the functionality and while opening the form i suppose to set the properties of form controls. For this i  used the following line code.

1. Set the Auto Declaration property to true for that form control.

2. In the general methods of the form override the init() method.

3. In Init method after super() i used the following code line to set the properties

    

    element.control(control::ControlName).enabled(false);

    E.g. element.control(control::ToDate).enabled(false);

4. While writing this line of code you will not get any intellisense after Control::

     You have to just type your control name and compile the method and it's done.

Hope this will help anyone.

Kiss'shor.. :)

Date functions in AX 2009

Hi....

These are some of the functions,from where we can get the day or month or year from the date...
Here is the below example....

static void date_Functions(Args _args)
{
    Transdate    d;
    ;
    
    d = today();
    
    info(strfmt("Date - %1",d));
    
    //Gets the month for the given date...
    info(strfmt("Month - %1",mthofYr(d)));
    
    //Gets the month name from the given date...
    info(strfmt("Month Name - %1",mthname(mthofYr(d))));
    
    //Gets the day for the given date...
    info(strfmt("Day - %1",dayOfMth(d)));
    
    //Gets the day name from the given date...
    info(strfmt("Day Name - %1",dayname(dayOfMth(d))));
    
    //Gets the year for the given date...
    info(strfmt("Year - %1",year(d)));
    
    //Gets the current weekday number from the date...
    info(strfmt("Weekday number - %1",dayOfwk(d)));
    
    //Gets the day of the year from the given date...
    info(strfmt("Day of year - %1",dayOfyr(d)));
    
    //Gets the week of the year from the given date...
    info(strfmt("Week of the year - %1",wkofyr(d)));
}

Source : http://kollidynamics.blogspot.in/search?updated-max=2011-08-27T02:41:00-07:00&max-results=4&start=8&by-date=false

Sequence of methods in the FORM level in AX

Hi...

This gives the information of method calls in the form level while

1. Opening the Form.

2. Creating/Updating/Deleting the record in the Form.

3. Closing the Form.

Sequence of Methods calls while opening the Form

Form --- init ()

Form --- Datasource --- init ()

Form --- run ()

Form --- Datasource --- execute Query ()

Form --- Datasource --- active ()

Sequence of Methods calls while closing the Form

Form --- canClose ()

Form --- close ()

Sequence of Methods calls while creating the record in the Form

Form --- Datasource --- create ()

Form --- Datasource --- initValue ()

Table --- initValue ()

Form --- Datasource --- active ()

Sequence of Method calls while saving the record in the Form

Form --- Datasource --- ValidateWrite ()

Table --- ValidateWrite ()

Form --- Datasource --- write ()

Table --- insert ()

Sequence of Method calls while deleting the record in the Form

Form --- Datasource --- validatedelete ()

Table --- validatedelete ()

Table --- delete ()

Form --- Datasource --- active ()

Sequence of Methods calls while modifying the fields in the Form

Table --- validateField ()

Table --- modifiedField ()

Source:  http://kollidynamics.blogspot.in/2011/08/sequence-of-methods-in-form-level-in-ax.html