Saturday, August 19, 2017

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;

}

Sunday, March 12, 2017

Ledger Account Setup Dynamics AX 2012

Hi Guys,

The Ledger Account setup is the most important setup for Dynamics AX Implementation.

Below are the Account Types and number sequence (Range), this will help you to arrange accounts in particular range.

Account Type From To
Assets 10000 19999
Liabilities 20000 29999
Equities/Funds 30000 39999
Revenue 40000 49999
Expense 50000 59999

Sample segment
Private Sector
Company--Department ---Cost Center ---Account --Project (maybe)

Public Sector
Appropriation Year --Cost Center --Account --Project (May be)

Thursday, April 7, 2016

MB6-890 Microsoft Development Introduction - AX7 (Rainer) - Exam Topics

Exam Topics
Understand the Architecture and Development Environment (20% - 25%)
Understand the Architecture and Development Environment
This objective may include but is not limited to: identify features of Microsoft Dynamics AX; describe the development environment; describe the components in the application stack; identify cloud architecture components; explain the server architecture; describe the layer architecture.
Use Microsoft Visual Studio to manage development with Microsoft Dynamics AX
This objective may include but is not limited to: identify the windows and basic navigation of Visual Studio; describe differences between and uses for projects, models, solutions, and packages.

Use the Application Explorer to Develop New Elements (25% - 30%)
Create and manage labels and resources
This objective may include but is not limited to: create new label files; create and use labels; describe elements and uses for labels; identify best practices for labels identify uses for resources.
Create and manage data types
This objective may include but is not limited to: describe uses for base enumerations; create new base enumerations; identify best practices for base enumerations; describe primitive and extended data types; create new extended data types; identify key properties for extended data types; implement best practices for extended data types.
Create and manage tables
This objective may include but is not limited to: identify the components of a table; describe various types of table relationships; describe various types of table indexes; implement best practices for tables, relationships, and indexes.
                                                        
Read, Write and Understand Basic X++ (30% - 35%)
Describe X++
This objective may include but is not limited to: identify the characteristics of X++; use the code editor to write X++; describe the features of IntelliSense; identify basic syntax for X++; describe the use of common key words in X++; describe the features of the debugger; use the comparison tool; use the best practice checker.
Work with X++ control statements
This objective may include but is not limited to: work with variables, operators, conditional statements, and loops; use built-in functions; use communication tools.
Work with classes
This objective may include but is not limited to: create new classes; describe scoping events and parameters; describe inheritance; identify key best practices when writing X++.
Use X++ to manipulate data
This objective may include but is not limited to: identify techniques for data retrieval; explain uses for transaction integrity checking; insert, update, and delete records using X++; identify best practices for manipulating data with X++.
Manage exceptions in X++
This objective may include but is not limited to: identify exception types; use try/catch statements; describe the features of optimistic concurrency control; describe best practices for exception handling with X++ .

Manage the User Interface and Security for Developers  (20% - 25%)
Manage the user interface
This objective may include but is not limited to: identify components of various form patterns; identify common form patterns and sub patterns; identify key properties for form elements; apply and validate form patterns; describe uses for tiles; describe how to join data in forms; implement best practices for form development.
Manage user navigation
This objective may include but is not limited to: identify types of menu items; create menu items and menus; identify key properties for menu items; describe uses for menu items; implement best practices for menus and menu items.
Manage security in the development environment
This objective may include but is not limited to: describe the security architecture; describe the components of role-based security; create new privileges, duties, and roles in the application explorer; describe the extensible data security framework.

Tuesday, October 13, 2015

AX DB Restore Scripts - Moving AX DB from one server to another server

Following SQL script is very useful while moving AX DB from one server to other server. Thanks to http://www.exploreax.com.

Declare @AOS varchar(30) = '[AOSID]' --must be in the format '01@SEVERNAME'
---Reporting Services---
Declare @REPORTINSTANCE varchar(50) = 'AX'
Declare @REPORTMANAGERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/Reports_AX'
Declare @REPORTSERVERURL varchar(100) = 'http://[DESTINATIONSERVERNAME]/ReportServer_AX'
Declare @REPORTCONFIGURATIONID varchar(100) = '[UNIQUECONFIGID]'
Declare @REPORTSERVERID varchar(15) = '[DESTINATIONSERVERNAME]'
Declare @REPORTFOLDER varchar(20) = 'DynamicsAX'
Declare @REPORTDESCRIPTION varchar(30) = 'Dev SSRS';

---SSAS Services---
Declare @SSASSERVERNAME varchar(20) = '[YOURSERVERNAME]\AX'
Declare @SSASDESCRIPTION varchar(30) = 'Dev SSAS'; -- Description of the server configuration

---BC Proxy Details ---
Declare @BCPSID varchar(50) = '[BCPROXY_SID]'
Declare @BCPDOMAIN varchar(50) = '[yourdomain]'
Declare @BCPALIAS varchar(50) = '[bcproxyalias]'
---Service Accounts ---
Declare @WFEXECUTIONACCOUNT varchar(20) = 'wfexc'
Declare @PROJSYNCACCOUNT varchar(20) = 'syncex'
---Help Server URL---
Declare @helpserver varchar(200) = 'http://[YOURSERVER]/DynamicsAX6HelpServer/HelpService.svc'

---Outgoing Email Settings---
Declare @SMTP_SERVER varchar(100) = 'smtp.mydomain.com' --Your SMTP Server
Declare @SMTP_PORT int = 25
---DMF Folder Settings ---
Declare @DMFFolder varchar(100) = '\\[YOUR FILE SERVER]\AX import\ '

---Email Template Settings---
Declare @EMAIL_TEMPLATE_NAME varchar(50) = 'Dynamics AX Workflow QA - TESTING'
Declare @EMAIL_TEMPLATE_ADDRESS varchar(50) = 'workflowqa@mydomain.com'
---Email Address Clearing Settings---
DECLARE @ExclUserTable TABLE (id varchar(10))
insert into @ExclUserTable values ('userid1'), ('userid2')

--List of users separated by | to keep enabled, while disabling all others
Declare @ENABLE_USERS NVarchar(max) = '|Admin|TIM|'
--List of users separated by | to disable, while keeping all the rest enabled all others
--Declare @DISABLE_USERS NVarchar(max) = '|BOB|JANE|'

--*****BEGIN UPDATES*******---

---Update AOS Config---
delete from SYSSERVERCONFIG where RecId not in (select min(recId) from SYSSERVERCONFIG)
update SYSSERVERCONFIG set serverid=@AOS, ENABLEBATCH=1
where serverid != @AOS -- Optional if you want to see the "affected row count" after execution.

---Update Batch Servers---
delete from BATCHSERVERGROUP where RecId not in (select min(recId) from BATCHSERVERGROUP group by GROUPID)
update BATCHSERVERGROUP set SERVERID=@AOS
where serverid != @AOS -- Optional to see "affected row count"
update batchjob set batchjob.status=4 where batchjob.CAPTION = '[BATCHJOBNAME]'
update batch set batch.STATUS=4 from batch inner join BATCHJOB on BATCHJOBID=BATCHJOB.RECID AND batchjob.CAPTION = '[BATCHJOBNAME]'

---Update Reporting Services---
delete from SRSSERVERS where RecId not in (select min(recId) from SRSSERVERS)
update SRSSERVERS set
    SERVERID=@REPORTSERVERID,
    SERVERURL=@REPORTSERVERURL,
    AXAPTAREPORTFOLDER=@REPORTFOLDER,
    REPORTMANAGERURL=@REPORTMANAGERURL,
    SERVERINSTANCE=@REPORTINSTANCE,
    AOSID=@AOS,
    CONFIGURATIONID=@REPORTCONFIGURATIONID,
    DESCRIPTION=@REPORTDESCRIPTION
where SERVERID != @REPORTSERVERID -- Optional if you want to see the "affected row count" after execution.

---Update SSAS Services---
delete from BIAnalysisServer where RecId not in (select min(recId) from BIAnalysisServer)
update BIAnalysisServer set
    SERVERNAME=@SSASSERVERNAME,
    DESCRIPTION=@SSASDESCRIPTION,
ISDEFAULT = 1
WHERE SERVERNAME <> @SSASSERVERNAME -- Optional where clause if you want to see the "affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Parameters---
Update SysEmailParameters set SMTPRELAYSERVERNAME = @SMTP_SERVER, @SMTP_PORT=@SMTP_PORT
---Update DMF Settings---
update DMFParameters set SHAREDFOLDERPATH = @DMFFolder
where SHAREDFOLDERPATH != @DMFFolder --Optional to see affected rows

---Set BCPRoxy Account---
update SYSBCPROXYUSERACCOUNT set SID=@BCPSID, NETWORKDOMAIN=@BCPDOMAIN, NETWORKALIAS= @BCPALIAS
where NETWORKALIAS != @BCPALIAS --optional to display affected rows.
---Set WF Execution Account---
update SYSWORKFLOWPARAMETERS set EXECUTIONUSERID=@WFEXECUTIONACCOUNT where EXECUTIONUSERID != @WFEXECUTIONACCOUNT
---Set Proj Sync Account---
update SYNCPARAMETERS set SYNCSERVICEUSER=@PROJSYNCACCOUNT where SyncServiceUser!= @PROJSYNCACCOUNT
---Set help server URL---
update SYSGLOBALCONFIGURATION set value=@helpserver where name='HelpServerLocation' and value != @helpserver

---Update Email Templates---
update SYSEMAILTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
update SYSEMAILSYSTEMTABLE set SENDERADDR = @EMAIL_TEMPLATE_ADDRESS, SENDERNAME = @EMAIL_TEMPLATE_NAME where SENDERADDR!=@EMAIL_TEMPLATE_ADDRESS OR  SENDERNAME!=@EMAIL_TEMPLATE_NAME;
---Update User Email Addresses---
update sysuserinfo set sysuserinfo.EMAIL = '' where sysuserInfo.ID not in (select id from @ExclUserTable)

---Disable all users except for a specific set---
update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @ENABLE_USERS) = 0
---Disable specific users---
---update userinfo set userinfo.enable=0 where  CharIndex('|'+ cast(ID as varchar) + '|' , @DISABLE_USERS) > 0

--Clean up server sessions
delete from SYSSERVERSESSIONS
--Clean up client sessions.
delete from SYSCLIENTSESSIONS

reference : http://www.exploreax.com/blog/blog/2015/10/13/ax-db-restore-scripts-full-script/

Wednesday, June 17, 2015

How to get Paper sizes in AX 2012

static void Job1(Args _args)
{
    System.Drawing.Printing.PrinterSettings printSetting = new System.Drawing.Printing.PrinterSettings();
    System.Drawing.Printing.PaperSize  size = new System.Drawing.Printing.PaperSize();
    System.Collections.ArrayList    paperSizes;
    System.Collections.IEnumerator  enumerator;

    paperSizes= printSetting.get_PaperSizes();
    enumerator = paperSizes.GetEnumerator();

    while (enumerator.MoveNext())
    {
        size = enumerator.get_Current();
        info(size.get_PaperName());   
    }

}

Thursday, May 14, 2015

How to delete Label Files from AX 2012

Here are the steps to delete Label Files from AX 2012:

  • Create a new model. You could call it "Test".
  • Open the AOT and move the Label File(s) you want to get rid of to the new model.
  • Close AX and stop the AOS.
  • Use AXUtil to delete the new model.
  • Select the label files (name start with 'ax' and followed with label id. select all files) and Delete from the server folder; C:\Program Files\Microsoft Dynamics AX\60\Server\...\bin\Application\Appl\Standard
  • Start the AOS.
  • Skip the upgrade wizard.