Import data from Excel to AX through X++

static void ImportExcelData(Args _args)

{

    SysExcelApplication             application;

    SysExcelWorkbooks               workbooks;

    SysExcelWorkbook                workbook;

    SysExcelWorksheets              worksheets;

    SysExcelWorksheet               worksheet;

    SysExcelCells                   cells;

    COMVariantType                  type;

    System.DateTime                 ShlefDate;

    FilenameOpen                    filename;

    dialogField                     dialogFilename;

    Dialog                          dialog;

    #AviFiles

    // Progress Bar

    SysOperationProgress progress = new SysOperationProgress(1, NoYes::Yes); 

    StyleTable                      styleTable;  // Table name

    str                             style;

    int                             row = 0;

    #Excel

    str COMVariant2Str(COMVariant _cv,

                       int _decimals = 0,

                       int _characters = 0,

                       int _separator1 = 0,

                       int _separator2 = 0)

    {

        switch(_cv.variantType())

        {

            case (COMVariantType::VT_BSTR):

                return _cv.bStr();

            case (COMVariantType::VT_R4):

                return num2str(_cv.float(),

                                _characters,

                                _decimals,

                                _separator1,

                                _separator2);

            case (COMVariantType::VT_R8):

                return num2str(_cv.double(),

                                _characters,

                                _decimals,

                                _separator1,

                                _separator2);

            case (COMVariantType::VT_DECIMAL):

                return num2str(_cv.decimal(),

                                _characters,

                                _decimals,

                                _separator1,

                                _separator2);

            case (COMVariantType::VT_DATE):

                return date2str(_cv.date(),

                                123,

                                2,

                                1,

                                2,

                                1,

                                4);

            case (COMVariantType::VT_EMPTY):

                return "";

            default:

                throw error(strfmt("@SYS26908",_cv.variantType()));

        }

        return "";

    }

    ;

    dialog = new Dialog("ExcelUpload");

    dialogFilename      =   dialog.addFieldValue(extendedTypeStr(FilenameOpen),filename);

    dialog.filenameLookupFilter(["@SYS28576",#XLS,"@SYS28576",#XLSX]);

    dialog.filenameLookupTitle("Upload from Excel");

    dialog.caption("Excel Upload");

    dialogFilename.value(filename);

    if(!dialog.run())

        return;

    filename            =   dialogFilename.value();

    application         =   SysExcelApplication::construct();

    workbooks           =   application.workbooks();

    try

    {

        workbooks.open(filename);

    }

    catch (Exception::Error)

    {

        throw error("File cannot be opened.");

    }

    workbook    = workbooks.item(1);

    worksheets  = workbook.worksheets();

    worksheet   = worksheets.itemFromNum(1);

    cells       = worksheet.cells();

    // Progress Caption & Animation

    progress.setCaption("Copying..");

    progress.setAnimation(#AviUpdate);

    do

    {

        try

        {

            ttsbegin;

            row++;

            // Getting EquipmentId value form Excel Row wise and assigning as str

            style = COMVariant2Str(cells.item(row,1).value());         

        // While Revecing datas from Excel Sheet If invalid value presents it wil not allowe to insert and the

            //   last  record also deleted

            if (!equipId)

            {

                ttsbegin;

                _TestXls.delete();

                ttscommit;

                box::warning(strfmt("Check the value in Excel Sheet row %1", row));

                return;

            }

            else

            {

                if (row > 1)

                {

                    // Progress bar Text and Total

                    progress.setText(strfmt("Importing to Ax Table : %1", row));

                    progress.setTotal(row, 1);

                    _TestXls.clear();

                    _TestXls.Style    =  equipId;

                    _TestXls.insert();

                }

            }

            ttscommit;

        }

        catch

        {

            Error(strfmt("Upload Failed in row %1", row));

        }

        type = cells.item(row + 1, 1).value().variantType();

    } while (type!= COMVariantType::VT_EMPTY);

    info(strfmt(" Details Uploaded Successfully"));

    application.quit(); 

}

Comparing AX 2012 to AX 7

List of table should be backuped while restoring the database in Ax 2012

List of table should be backup while restoring the database in Ax 2012

for more information:
http://msdynamicsaxtips.blogspot.in/2013/11/list-of-table-in-ax-2012-should-be.html

AX 7 Development

AX 7 Development:

https://ax.help.dynamics.com/en/wiki/technical-concepts-guide/

Report Development in Ax 2012

I Have created new temp table what fields i want show in the Report.
Table Name : TransferInOutTmp                      
Contract Class: A data contract class is an X++ class which contains parm methods with the DataMemberAttribute defined at the beginning of the method. 
This class is used to define one or more parameters that will be used in a SSRS report.
[
DataContractAttribute,
SysOperationContractProcessingAttribute(classStr(TransferInOutUIBuilder))
]
public class TransferInOutContract implements SysOperationValidatable
{
     TransDate           fromDate;
     TransDate           toDate;
     InventSiteId        site;
     NoYes               shipdate,Recdate;

 }

[
   DataMemberAttribute(identifierStr('TransDate')),
    SysOperationLabelAttribute(literalstr("From Date")),
    SysOperationHelpTextAttribute(literalstr("From Date")),
    SysOperationDisplayOrderAttribute('1')
]
public TransDate parmFromDate(TransDate _fromDate = fromDate)
{
    fromDate = _fromDate;
    return fromDate;

}
[
   DataMemberAttribute(identifierStr('toDate')),
    SysOperationLabelAttribute(literalstr("To Date")),
    SysOperationHelpTextAttribute(literalstr("To Date")),
    SysOperationDisplayOrderAttribute('2')
]
public TransDate parmToDate(TransDate _toDate = toDate)
{
    toDate = _toDate;
    return toDate;

}
[
    DataMemberAttribute('InventSiteId'),
    SysOperationLabelAttribute(literalstr("Site Id")),
    SysOperationHelpTextAttribute(literalstr("Site Id")),
    SysOperationDisplayOrderAttribute('3')
]
public InventSiteId parmsite(InventSiteId _site = site)
{
    site = _site;
    return site;

}
[
   DataMemberAttribute(identifierStr('ReceiptDate')),
    SysOperationLabelAttribute(literalstr("Received")),
    SysOperationHelpTextAttribute(literalstr("Received")),
    SysOperationDisplayOrderAttribute('5')
]
public NoYes parmrecdate(NoYes _Recdate = Recdate)
{
    Recdate = _Recdate;
    return Recdate;

}
[
   DataMemberAttribute(identifierStr('NoYes')),
    SysOperationLabelAttribute(literalstr("Shipped")),
    SysOperationHelpTextAttribute(literalstr("Shipped")),
    SysOperationDisplayOrderAttribute('4')
]
public NoYes parmshipdate(NoYes _shipdate = shipdate)
{
    shipdate = _shipdate;
    return shipdate;

}
public boolean validate()
{
    boolean ret = true;

    if (fromDate > toDate)
    {
        ret = checkFailed("From date is later than to date");
    }

    return ret;

}

DP Class :This method contains the business logic and is called by reporting services to generate data.
[
SRSReportParameterAttribute(classStr(TransferInOutContract))
]
class TransferInOutDP extends SRSReportDataProviderBase
{
    TransferInOutTmp                       transferInOutTmp; //temp table
    TransferInOutContract                contract;
    InventTransferTable                    transferTable;
    InventTransferLine                     transferLine;
    InventTransferReceiveDate        fromdate,todate;
    InventSiteId                                 siteId;
    InventLocation                            location,locationloc;
    NoYes                                         shipdate,Recdate;

}
[SRSReportDataSetAttribute("TransferInOutTmp")]
public TransferInOutTmp getTmptransferInOut()
{
    select * from transferInOutTmp;
    return transferInOutTmp;
}
public void insertTransferInOut(InventTransferTable   _inventTransferTable,InventTransferLine  _inventTransferLine)
{
  transferInOutTmp.InventTransferId  = _inventTransferTable.TransferId;
  transferInOutTmp.FromWarehouse     = _inventTransferTable.InventLocationIdFrom;   transferInOutTmp.FromSiteName  =  InventLocation::find(transferInOutTmp.FromWarehouse).Name;
 transferInOutTmp.ToWarehouse       = _inventTransferTable.InventLocationIdTo;
  transferInOutTmp.ToSiteName        = InventLocation::find(transferInOutTmp.ToWarehouse).Name;
    transferInOutTmp.ItemId            = _inventTransferLine.ItemId;
    transferInOutTmp.ItemName          = _inventTransferLine.itemName();
      transferInOutTmp.TransferStatus    = _inventTransferTable.TransferStatus;
    transferInOutTmp.ReceiveDate       = _inventTransferTable.ReceiveDate;
    transferInOutTmp.ShipDate          = _inventTransferTable.ShipDate;
    transferInOutTmp.insert();
}
[SysEntryPointAttribute(false)]
public void processReport()
{
    Query                   query = new Query();
    QueryRun                         queryRun;
    QueryBuildDataSource    qbdstransferTable;
    QueryBuildDataSource    qbdstransferLine;
    QueryBuildDataSource    qbdsLocation;
    QueryBuildRange         qbr,qbr1,qbr2,qbr3,qbrshipped,qbrreceived;
   

    contract = this.parmDataContract() as TransferInOutContract;
    fromDate        =   contract.parmFromDate();
    toDate          =   contract.parmToDate();
    siteId          =   contract.parmsite();
    shipdate        =   contract.parmshipdate();
    Recdate         =   contract.parmrecdate();


    qbdstransferTable   = query.addDataSource(tableNum(InventTransferTable));
    qbdstransferLine =   qbdstransferTable.addDataSource(tableNum(InventTransferLine));
    qbdstransferLine.relations(true);
    qbdstransferLine.joinMode(JoinMode::InnerJoin);
    if(shipdate)
    {
        qbdstransferTable.addRange(fieldNum(InventTransferTable, ShipDate)).value(SysQuery::range(fromDate, toDate));
        qbdstransferTable.addRange(fieldNum(InventTransferTable,TransferStatus)).value(enum2str(InventTransferStatus::Shipped));
    }
    if(Recdate)
    {
        qbdstransferTable.addRange(fieldNum(InventTransferTable, ReceiveDate)).value(SysQuery::range(fromDate, toDate));
        qbdstransferTable.addRange(fieldNum(InventTransferTable,TransferStatus)).value(enum2str(InventTransferStatus::Received));
    }

    queryRun = new QueryRun(query);
    while (queryRun.next())
    {
        transferTable     = queryrun.get(tableNum(InventTransferTable));
        transferLine      = queryrun.get(tableNum(InventTransferLine));
        if (siteId)
        {
            location = InventLocation::find(transferTable.InventLocationIdFrom);
            if(siteId == location.InventSiteId)
            {
              this.insertTransferInOut(transferTable,transferLine);
            }
        }
    }
}

UI Builder :User Interface (UI) Builder Class is used to define the layout of the parameter dialog box that opens before a report is run in Microsoft Dynamics AX. It is used to add the customizations as well as additional fields in the dialog.

class TransferInOutUIBuilder extends SysOperationAutomaticUIBuilder

{

    Dialogfield                 dlgfromdate;

    Dialogfield                 dlgtodate;

    Dialogfield                 dlgsite;

    Dialogfield                 dialogreceiptdate;

    Dialogfield                 dialogshipdate;

   TransferInOutContract   contract;

}

public void Build()

{

    Dialog dialogObject =  this.dialog();

    contract =  this.dataContractObject();

    this.addDialogField(methodStr(TransferInOutContract,parmfromDate),contract);

    this.addDialogField(methodStr(TransferInOutContract,parmtoDate),contract);

    this.addDialogField(methodStr(TransferInOutContract,parmsite),contract);

    dialog.addGroup("Shipped");

    this.addDialogField(methodStr(TransferInOutContract,parmshipdate),contract);

    dialog.addGroup("Received");

    this.addDialogField(methodStr(TransferInOutContract,parmrecdate),contract);

}

public void initializefields()

{

    contract = this.dataContractObject();

 }

/// <summary>

/// This method is used to initialize the dialog fields after the fields are build.

/// </summary>

public void postBuild()

{

    super();

     contract = this.dataContractObject();

     dlgfromdate         = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(TransferInOutContract, parmFromDate));

     dlgfromdate.fieldControl().mandatory(true);

     dlgtodate           = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(TransferInOutContract, parmToDate));

     dlgtodate.fieldControl().mandatory(true);

     dlgsite             = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(TransferInOutContract, parmsite));

     dlgsite.fieldControl().mandatory(true);

     dialogreceiptdate   = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(TransferInOutContract, parmrecdate));

     dialogshipdate      = this.bindInfo().getDialogField(this.dataContractObject(), methodStr(TransferInOutContract, parmshipdate));

     dialogreceiptdate.registerOverrideMethod(methodStr(FormCheckBoxControl, modified), methodStr(TransferInOutUIBuilder, receivedModified), this);

     dialogshipdate.registerOverrideMethod(methodStr(FormCheckBoxControl, modified), methodStr(TransferInOutUIBuilder, shipModified), this);

}

public boolean receivedModified(FormCheckBoxControl _control)

{

    dialogshipdate.value(_control.checked(false));

    return true;

}

public boolean shipModified(FormCheckBoxControl _control)

{

  dialogreceiptdate.value(_control.checked(false));

   return true;

}

Develop a Report in Visual Studio and create a one output menuItem 

based on selection report will generate.

The Accounts Payable cube cube either does not exist or has not been processed.

After deployed the Cubes showing error message :
The Accounts Payable cube cube either does not exist or has not been processed. 

Solution:
System Administrator > Setup > Business Intelligence > Analysis Services > Analysis Servers
 Configure the Default Database "Dynamics initial" in OLAP database TabPage

How to update Label Id to String through X++ code

static void Lableid2str(Args _args)
{
RetailChannelReport     retailChannelReport;
;
ttsBegin;
while select forUpdate retailChannelReport
{
retailChannelReport.ReportName = SysLabel::labelId2String2(retailChannelReport.ReportName, 'en-us');
print retailChannelReport.ReportName;
retailChannelReport.doupdate();
 }
ttscommit;
pause;
}