Import Excel Data into Dynamics using X++ code.

Here i post code for Import Excel Data into Dynamics using X++ code.
Here the code written in Command Button clicked event, and also i  added the Excel format below of this post which i used.
void clicked()
{
SysExcelApplication             application;
SysExcelWorkbooks               workbooks;
SysExcelWorkbook                workbook;
SysExcelWorksheets              worksheets;
SysExcelWorksheet               worksheet;
SysExcelCells                        cells;
COMVariantType                 type;
System.DateTime                  ShlefDate;
FilenameOpen                     filename;
dialogField                          dialogFilename;
Dialog                               dialog;
//Table Declarations Starts
InventSize                      _InventSize;
InventBatch                    _InventBatch;
InventSerial                    _InventSerial;
InventTable                   _InventTable;
VendParameters              _vendParameters;
//Table Declartions Ends
InventBatchId                   batchNumber;
InventBatchExpDate              expdate;
itemId                          itemid;
TransDate                       poddate;
CertificatesofSterilization  Certs;
CertificatesofAnalysis     CertiAnalysis;
InventSizeId                    InventSize;
ConfigId                        _ConfigId;
InventColorId                   _InventColorId;
InventSiteId                    _InventSiteId;
WMSLocationId                   _WMSLocationId;
InventLocationId                _InventLocationId;
WMSPalletId                     _WMSPalletId;
NoYesId                         ClosedTransactions;
NoYesId                         ClosedTransQty;
str                             pONo;
str                             srNo;
real                            quantity;
int                             row;
InventBatchExpDate              ShelfLifeDate;
#Excel
// convert into str from excel cell value
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(“Excel Upoad”);
dialogFilename      =   dialog.addField(typeId(FilenameOpen));
dialog.filenameLookupFilter(["@SYS28576",#XLSX,"@SYS28576",#XLS]);
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();
try
{
ttsbegin;
do
{
row++;
pONo                    =   COMVariant2Str(cells.item(row, 1).value());
itemid                  =   COMVariant2Str(cells.item(row,2).value());
InventSize              =   COMVariant2Str(cells.item(row, 3).value());
batchNumber             =   COMVariant2Str(cells.item(row, 4).value());
poddate                 =   cells.item(row, 5).value().date();
expdate                 =   cells.item(row, 6).value().date();
srNo                    =   COMVariant2Str(cells.item(row, 7).value());
Certs                =   str2enum(Certs,cells.item(row, 8).value().bStr());
CertiAnalysis        =   str2enum(CertiAnalysis,cells.item(row, 9).value().bStr());
quantity                =   cells.item(row, 10).value().double();
_ConfigId               =   COMVariant2Str(cells.item(row, 12).value());
_InventColorId          =   COMVariant2Str(cells.item(row, 13).value());
_InventSiteId           =   COMVariant2Str(cells.item(row, 14).value());
_WMSLocationId          =   COMVariant2Str(cells.item(row, 15).value());
_InventLocationId       =   COMVariant2Str(cells.item(row, 16).value());
_WMSPalletId            =   COMVariant2Str(cells.item(row, 17).value());
ClosedTransactions      =   str2enum(ClosedTransactions,cells.item(row, 18).value().bStr());
ClosedTransQty          =   str2enum(ClosedTransQty,cells.item(row, 19).value().bStr());
if(row > 1)
{
//Insert into InventSize Table
select firstonly _InventSize where _InventSize.ItemId == itemid && _InventSize.InventSizeId == InventSize;
if(!_InventSize)
{
_InventSize.InventSizeId     =      InventSize;
_InventSize.ItemId           =      itemid;
_InventSize.insert();
}
else
{
warning(strfmt(“Item Id and InventSize (%1   –   %2) already exists”,itemid,InventSize));
}
// Insert into InventBatch Table
_InventBatch.inventBatchId      =       batchNumber;
_InventBatch.itemId             =       itemid;
_InventBatch.prodDate           =       poddate;
_InventBatch.expDate            =       expdate;
_InventBatch.insert();
// Insert into InventSerial Table
_InventSerial.InventSerialId    = srNo;
_InventSerial.ItemId            = itemid;
_InventSerial.ProdDate          = poddate;
_InventSerial.insert();
info(strfmt(“Item(%1) uploaded successfully”,itemid));
}
type = cells.item(row+1, 1).value().variantType();
}while (type != COMVariantType::VT_EMPTY);
application.quit();
ttscommit;
}
catch
{
Error(“Upload Failed”);
}
}
These are the details i used to import from Excel.

AX AOS Service Start issue -Error Code 100 for Ax 2012

If you are facing AOS Service start issue with error code 100 then you will not able to resolve issue without doing some modification in database table.


Error Investigation:

After searching error description in event-viewer I found following message.

Object Server 01:  Fatal SQL condition during login. Error message: "The internal time zone version number stored in the database is higher than the version supported by the kernel (7/8). 

After that I search on google then found solution below.

select rows from SQLSystemVariables  table in particular database then check value of  SYSTIMEZONESVERSION  column it will be 8 you need to update this value to 7 then you will able to start service.

Once service get started then you can update kernel version with 8 by using setup.

I hope you have an idea to resolve this issue.

inventitemjour

static void ItemJourUpload(Args _args)
{
    #AviFiles
    FilenameOpen filename;
    dialogField dialogFilename,DialogJournalType;
    LedgerDimensionAccount  mainAccDimension;
    int jounaltype;
    Dialog dialog= new Dialog("Excel Upoad");
    Container excelCont[], financialDimensions;
    RecId           offsetledger;
    //InventTable            inventTable;
    InventTableModule      inventtablemodule;

    int rowIdx;
    Counter linesImported;
    int lastRow,countr;
    boolean ok = true;
    LedgerDimensionAccount              ledgerDimension;
    InventTable                         inventTable;
    InventJournalName                   inventJournalName;
    InventJournalTable                  inventJournalTable;
    InventJournalTrans                  inventJournalTrans;
    NumberSeq                           numberSeq;
    Container                           con,defDimension;
    FileIOPermission                    permission;
    TextIO                              textIO, textIO1;
    //Dialog                              dialog;
    DialogField                         dialogField;
    //Filename                            Filename;
    LineNum                             lineNumber = 1;
    CustAccount                         custAccNum;
    InventJournalId                     journalNum;
    int                                 dimCount,dimCount2;

    LedgerJournalACType                 AccountType;
    InventDim                           inventDim;
    InventDimId                         dimId;
    InventJournalTrans_IN               InventJournalTrans_IN;
    //#File
    Description c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23;

    str input;

    SysExcelApplication application;
    SysExcelWorkBooks workBooks;
    SysExcelWorkSheets workSheets;
    SysExcelWorkSheet workSheet;
    SysExcelCells cells;
    //dhiBusinessType dhiBusinessType;
    PurchaseType    purchaseType;
    DocumentStatus  documentStatus;
    VersioningDocumentState   documentState;
    SysOperationProgress progress;
    struct      struct= new Struct();

    #define.CurrentVersion(1)
    #localmacro.CurrentList
    filename
    #endmacro

    #Excel
    #define.Star('*')
    #define.Space(' ')


    // convert into str from excel cell value
    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 "";
    }

    // Find last row from excel

    int findLastRow(SysExcelWorkSheet _workSheet)
    {
        SysExcelRange range;
        ;

        range = _workSheet.cells().range(#ExcelTotalRange);

        try
        {
            // Finds the row where the first contents is found.
            range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
        }
        catch (Exception::Error)
        {
            error("@SYS59926");
            return 0;
        }

        if (range)
        {
            return range.row();
        }
        else
        {
            return 0;
        }
    }


    ;


    dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
   // DialogJournalType = dialog.addField(enumstr(God_JournalToUpload),"Journal Type");
    dialog.filenameLookupFilter(["@SYS28576",#XLS,#Xlsx]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialogFilename.value(filename);

    if(!dialog.run())
    return;

    filename = dialogFilename.value();

   // ttsbegin;

    application = SysExcelApplication::construct();
    workBooks = application.workbooks();
    workBooks.open(filename,0,true);

    workSheets = workBooks.item(1).worksheets();
    // this.importExcel("Sheet1");

    input = "Sheet1";
    workSheet = workSheets.itemFromNum(1);//.itemFromName(input);
    cells = workSheet.cells();
    lastRow = findLastRow(workSheet);
    rowIdx = 2;

    progress = new SysOperationProgress();
    progress.setCaption("Excel Importing");
    progress.setTotal(lastRow);
    progress.setAnimation(#AviTransfer);
    setprefix("Excel Import");
    try{

            while (rowIdx <= lastRow)
            {
                setPrefix(strfmt("Excel Row: %1", rowIdx));

                c1                = COMVariant2Str(cells.item(rowIdx,1).value()); //a//JournalId
                c2                = COMVariant2Str(cells.item(rowIdx,2).value());//b//TransDate
                c3                = COMVariant2Str(cells.item(rowIdx,3).value());//c//Voucher no
                c4                = COMVariant2Str(cells.item(rowIdx,4).value());//d//itemid
                c5                = COMVariant2Str(cells.item(rowIdx,5).value());//e//costprice
                c6                = COMVariant2Str(cells.item(rowIdx,6).value());//f//qty
                c7                = COMVariant2Str(cells.item(rowIdx,7).value());//g//costamount
                c8                = COMVariant2Str(cells.item(rowIdx,8).value());//h
                c9                = COMVariant2Str(cells.item(rowIdx,9).value());//i
                c10               = COMVariant2Str(cells.item(rowIdx,10).value());//j
                c11               = COMVariant2Str(cells.item(rowIdx,11).value());//k
                c12               = COMVariant2Str(cells.item(rowIdx,12).value());//l
                c13               = COMVariant2Str(cells.item(rowIdx,13).value());//m
                c14               = COMVariant2Str(cells.item(rowIdx,14).value());//n
                c15               = COMVariant2Str(cells.item(rowIdx,15).value());//o
                c16               = COMVariant2Str(cells.item(rowIdx,16).value());//p
                //c17               = COMVariant2Str(cells.item(rowIdx,17).value());//q
                //c18               = COMVariant2Str(cells.item(rowIdx,18).value());//r
                //c19               = COMVariant2Str(cells.item(rowIdx,19).value());//s
                //c20               = COMVariant2Str(cells.item(rowIdx,20).value());//t
                //c21               = COMVariant2Str(cells.item(rowIdx,21).value());//u
                //c22               = COMVariant2Str(cells.item(rowIdx,22).value());//v

                linesImported++;
               // excelCont[linesImported] = conins(excelCont[linesImported] , 1 ,c1, c2, c3, c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19); // records inserted into container



                    //journalNum = conPeek(con,1);
                    ttsBegin;
                  InventJournaltrans.clear();
                    select InventJournaltable
                        where InventJournaltable.JournalId == c1;

                    select inventJournalName
                        where inventJournalName.JournalNameId == InventJournaltable.JournalNameId;

                 //  numberSeq                            = NumberSeq::newGetVoucherFromId(inventJournalName.VoucherNumberSequenceTable);
                   InventJournaltrans.initValue();
                   InventJournaltrans.JournalId = InventJournaltable.JournalId;
                   InventJournaltrans.initFromInventJournalTable(InventJournaltable);
                   InventJournaltrans.LineNum           = lineNumber;
              //  info(int2str(lineNumber));
                   InventJournaltrans.TransDate         = str2Date(c2,123); //Date;
                  // InventJournaltrans.Voucher           = numberSeq.voucher();
                   InventJournaltrans.Voucher              =   c3;//conPeek(con,3);

                   //inventJournalTrans.ProjId            = c3;
                   //InventJournaltrans.initFromProjTable(ProjTable::find(InventJournaltrans.ProjId));
                   InventJournaltrans.ItemId            = c4;
                InventJournaltrans.modifiedField(fieldNum(InventJournaltrans,ItemId));

                select inventTable where inventTable.ItemId == InventJournaltrans.ItemId;//this.ItemId;
                select inventtablemodule where inventtablemodule.ItemId == InventJournaltrans.ItemId;
                //inventtablemodule.UnitId = "NOS";
                inventtablemodule.itemid = InventJournaltrans.ItemId;
                inventtablemodule.ModuleType = ModuleInventPurchSales::Invent;
                //inventtablemodule.insert();

                   //InventJournaltrans.ProjCategoryId    = c5;
                   inventJournalTrans.CostPrice         = any2real(c5);
                   InventJournaltrans.Qty               = any2real(c6);
                   InventJournaltrans.modifiedField(fieldNum(InventJournaltrans,Qty));
                   InventJournaltrans.CostAmount        = any2real(c7);
                   //InventJournaltrans.CostPrice         = 0;//InventJournaltrans.CostAmount /InventJournaltrans.Qty;
                   //InventJournaltrans.ProjUnitID        = c8;
                   InventJournaltrans.PriceUnit         = 1;
                   //InventJournaltrans.CostAmount        = Currency::amount(InventJournaltrans.Qty * (InventJournaltrans.CostPrice / InventJournaltrans.PriceUnit));

                   //dimCount = 0;
                    ////if(c3)
                    ////{
                        ////dimCount++;
                       ////defDimension = ["Project",c3];
                    ////}
                    //if(c9)
                    //{
                        //dimCount++;
                        //defDimension += ["Region",c9];
                    //}
                    //if(c10)
                    //{
                        //dimCount++;
                        //defDimension += ["Branch",C10];
                    //}
                    //if(c19)
                    //{
                        //dimCount++;
                        //defDimension += ["Division",C19];
                    //}
                    ////if(c12)
                    ////{
                        ////dimCount++;
                        ////defDimension += ["Worker",c12];
                    ////}
                   ////defDimension = [dimCount] + defDimension;
                   ////info(strFmt("%1",con2Str(defDimension)));

                //struct = new Struct();
//
                //if(c14)
                //{
                    //struct.add("Branch",c14);
                //}
                //if(c16)
                //{
                    //struct.add("Division",c16);
                //}
                //if(c13)
                //{
                    //struct.add("Region",c13);
                //}
                //if(c15)
                //{
                    //struct.add("Department",c15);
                //}
                //if(struct.fields())
                //{
                    //InventJournaltrans.DefaultDimension = God_GetDimension::createDefaultDimension(struct);
                //}



                   inventDim.InventSiteId               = c13;
                   inventDim.InventLocationId           = c14;
                   //inventDim.wMSLocationId            = c7;
                   //inventDim.inventBatchId            = c17;
                   dimId                                = inventDim::findOrCreate(inventDim).inventDimId;

                   inventJournalTrans.inventDimId       = dimId;
                   // inventDim.initValue();
                  // inventDim.initFromInventLocation(inventDim.inventLocation());
                   //InventJournaltrans.initValue();

                //if(c9 || c10 || c19)
                //{
                   //InventJournaltrans.DefaultDimension = AxdDimensionUtil::getDimensionAttributeValueSetId(defDimension);
                //}
                //InventJournaltrans.ProjSalesCurrencyId = c16;
                //InventJournaltrans.ProjLinePropertyId = c17;
                //mainAccDimension = DimensionStorage::getDefaultAccountForMainAccountNum(c12);
                //ledgerDimension = DimensionDefaultingService::serviceCreateLedgerDimension(mainAccDimension);
//
                //InventJournaltrans.LedgerDimension = ledgerDimension;//InventJournaltable.LedgerDimension;
                InventJournaltrans.insert();
                 lineNumber++;
                InventJournalTrans_IN.clear();
                InventJournalTrans_IN.initValue();
                InventJournalTrans_IN.InventJournalTrans = InventJournaltrans.RecId;
                InventJournalTrans_IN.insert();

                //info(strFmt("%1,%2,%3,%4",InventJournaltable.JournalId,InventJournaltable.JournalId,
                //InventJournaltable.JournalType,InventJournaltrans.JournalId));

                //journalTableData = JournalTableData::newTable(journalTable);
                //journalTransData = journalTableData.journalStatic().newJournalTransData(journalTrans,journalTableData);
//
                //// Init JournalTable
//
                //journalTable.clear();
//
                //journalTable.JournalId = journalTableData.nextJournalId();
                //journalTable.JournalType = InventJournalType::Movement;
                //journalTable.JournalNameId = journalTableData.journalStatic().standardJournalNameId(journalTable.JournalType);
//
                //journalTableData.initFromJournalName(journalTableData.journalStatic().findJournalName(journalTable.JournalNameId));
//
                //// Init JournalTrans
                //select firstonly inventTable;
                //for(cnt=1;cnt<10;cnt++)
                //{
                //journalTrans.clear();
                //journalTransData.initFromJournalTable();
//
                //journalTrans.TransDate = systemdateget() + 1 div 2;
                //journalTrans.ItemId = inventTable.ItemId;
                //journalTrans.Qty = 100;
                //journalTrans.CostAmount = 100;
//
                //// Dimension details
//
                //inventDim.InventLocationId = 'GW';
                //journalTrans.InventDimId = InventDim::findOrCreate(inventDim).inventDimId;
//
                //journalTransData.create();
//
//
//
                //}
//
                //journalTable.insert();

                ttsCommit;

                rowIdx++;
                progress.setText("Importing " + c1);
                progress.setCount(linesImported);
               }
           }
           catch
       {
           error(strFmt("error in line %1",rowIdx));
       }
    info(strFmt("done! Imported %1 records",rowIdx-2));
}

WBS item upload

static void God_WBSItemUploadforlive(Args _args)
{
    #AviFiles
    FilenameOpen filename;
    dialogField dialogFilename,DialogJournalType;
    LedgerDimensionAccount  mainAccDimension;
    int jounaltype;
    Dialog dialog= new Dialog("Excel Upoad");
    Container excelCont[], financialDimensions;
    RecId           offsetledger;

    int rowIdx;
    Counter linesImported;
    int lastRow,countr;
    boolean ok = true;
    LedgerDimensionAccount              ledgerDimension;
    InventTable                         inventTable;
    InventJournalName                   inventJournalName;
    InventJournalTable                  inventJournalTable;
    InventJournalTrans                  inventJournalTrans;
    NumberSeq                           numberSeq;
    Container                           con,defDimension;
    FileIOPermission                    permission;
    TextIO                              textIO, textIO1;
    //Dialog                              dialog;
    DialogField                         dialogField;
    //Filename                            Filename;
    LineNum                             lineNumber = 1;
    CustAccount                         custAccNum;
    InventJournalId                     journalNum;
    int                                 dimCount,dimCount2;

    LedgerJournalACType                 AccountType;
    InventDim                           inventDim;
    InventDimId                         dimId;
    InventJournalTrans_IN               InventJournalTrans_IN;
    PSAActivityEstimates                pSAActivityEstimates;
    //#File
    Description c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23;

    str input;

    SysExcelApplication application;
    SysExcelWorkBooks workBooks;
    SysExcelWorkSheets workSheets;
    SysExcelWorkSheet workSheet;
    SysExcelCells cells;
    //dhiBusinessType dhiBusinessType;
    PurchaseType    purchaseType;
    DocumentStatus  documentStatus;
    VersioningDocumentState   documentState;
    SysOperationProgress progress;
    struct      struct= new Struct();

    #define.CurrentVersion(1)
    #localmacro.CurrentList
    filename
    #endmacro

    #Excel
    #define.Star('*')
    #define.Space(' ')


    // convert into str from excel cell value
    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 "";
    }

    // Find last row from excel

    int findLastRow(SysExcelWorkSheet _workSheet)
    {
        SysExcelRange range;
        ;

        range = _workSheet.cells().range(#ExcelTotalRange);

        try
        {
            // Finds the row where the first contents is found.
            range = range.find(#Star, null, #xlFormulas, #xlWhole, #xlByRows, #xlPrevious);
        }
        catch (Exception::Error)
        {
            error("@SYS59926");
            return 0;
        }

        if (range)
        {
            return range.row();
        }
        else
        {
            return 0;
        }
    }


    ;


    dialogFilename = dialog.addField(extendedtypestr(FilenameOpen));
   // DialogJournalType = dialog.addField(enumstr(God_JournalToUpload),"Journal Type");
    dialog.filenameLookupFilter(["@SYS28576",#XLS,#Xlsx]);
    dialog.filenameLookupTitle("Upload from Excel");
    dialogFilename.value(filename);

    if(!dialog.run())
    return;

    filename = dialogFilename.value();

   // ttsbegin;

    application = SysExcelApplication::construct();
    workBooks = application.workbooks();
    workBooks.open(filename,0,true);

    workSheets = workBooks.item(1).worksheets();
    // this.importExcel("Sheet1");

    input = "Sheet1";
    workSheet = workSheets.itemFromNum(1);//.itemFromName(input);
    cells = workSheet.cells();
    lastRow = findLastRow(workSheet);
    rowIdx = 2;

    progress = new SysOperationProgress();
    progress.setCaption("Excel Importing");
    progress.setTotal(lastRow);
    progress.setAnimation(#AviTransfer);
    setprefix("Excel Import");
    try{

            while (rowIdx <= lastRow)
            {
                setPrefix(strfmt("Excel Row: %1", rowIdx));

                c1              = COMVariant2Str(cells.item(rowIdx,1).value());
                c2            = COMVariant2Str(cells.item(rowIdx,2).value());
                c3           = COMVariant2Str(cells.item(rowIdx,3).value());
                c4               = COMVariant2Str(cells.item(rowIdx,4).value());
                c5                = COMVariant2Str(cells.item(rowIdx,5).value());
                c6                = COMVariant2Str(cells.item(rowIdx,6).value());
                c7                = COMVariant2Str(cells.item(rowIdx,7).value());
                c8                = COMVariant2Str(cells.item(rowIdx,8).value());
                c9                = COMVariant2Str(cells.item(rowIdx,9).value());
                c10               = COMVariant2Str(cells.item(rowIdx,10).value());
                c11     = COMVariant2Str(cells.item(rowIdx,11).value());
                c12          = COMVariant2Str(cells.item(rowIdx,12).value());
                c13               = COMVariant2Str(cells.item(rowIdx,13).value());
                c14         = COMVariant2Str(cells.item(rowIdx,14).value());
                c15             = COMVariant2Str(cells.item(rowIdx,15).value());
                c16              = COMVariant2Str(cells.item(rowIdx,16).value());
                c17             = COMVariant2Str(cells.item(rowIdx,17).value());
                c18             = COMVariant2Str(cells.item(rowIdx,18).value());
                c19             = COMVariant2Str(cells.item(rowIdx,19).value());

                linesImported++;
               // excelCont[linesImported] = conins(excelCont[linesImported] , 1 ,c1, c2, c3, c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19); // records inserted into container

                //InventJournaltrans.clear();

                    //journalNum = conPeek(con,1);
                    ttsBegin;
                    //select InventJournaltable
                      //  where InventJournaltable.JournalId == c1;

                    //select inventJournalName
                      //  where inventJournalName.JournalNameId == InventJournaltable.JournalNameId;

                  // numberSeq                            = NumberSeq::newGetVoucherFromId(inventJournalName.VoucherNumberSequenceTable);
                   //InventJournaltrans.initValue();
                   pSAActivityEstimates.ActivityNumber = c1;
                   pSAActivityEstimates.ProjTransType  = QuotationProjTransType::Item;
                   pSAActivityEstimates.Description    = c3;
                   pSAActivityEstimates.ProjCategoryId   = c4;
                   pSAActivityEstimates.ItemId           = c5;
                    pSAActivityEstimates.Quantity        = any2real(c6);
                pSAActivityEstimates.UnitCostPrice        =  any2real(c7);
                pSAActivityEstimates.UnitSalesPrice       = any2real(c8);
                pSAActivityEstimates.TotalCostPrice      = any2real(c9);
                    pSAActivityEstimates.TotalSalesPrice      = any2real(c10);
                pSAActivityEstimates.LinePropertyId        = c11;


                   //inventDim.initValue();
                   //inventDim.initFromInventLocation(inventDim.inventLocation());
                   inventDim.InventSiteId               = c12;
                   inventDim.InventLocationId           = c13;
                   dimId                                = inventDim::findOrCreate(inventDim).inventDimId;
                  // inventDim.inventDimId       = dimId;
                  // inventDim.insert();
                pSAActivityEstimates.InventDimId =dimId;


                pSAActivityEstimates.insert();

                ttsCommit;
                lineNumber++;
                rowIdx++;
                progress.setText("Importing " + c1);
                progress.setCount(linesImported);
               }
           }
           catch
       {
           error(strFmt("error in line %1",rowIdx));
       }
    info(strFmt("done! Imported %1 records",rowIdx-2));
}

space

="T :" + " " + First(Fields!Phone.Value, "TaxHeader") + Microsoft.VisualBasic.Constants.vbcrlf + "F :" + " " + First(Fields!Fax.Value, "TaxHeader") + Microsoft.VisualBasic.Constants.vbcrlf + "E :" + " " + First(Fields!Email.Value, "TaxHeader") + Microsoft.VisualBasic.Constants.vbcrlf + "W :" + " " + First(Fields!Web.Value, "TaxHeader")

 give extra space in between date digit for cheque printing in SSRS report

static void Job12(Args _args)
{

 
      Transdate dat;
      Str s1,s2,s3,s4,s5,s6,s7,s8;
      str  nh;
   
      ;
   
     dat = mkdate(1,5,2018);
 
       nh = stralpha(date2str(dat,123,2,2,2,2,4));
   
     s1 = substr(nh,1,1);
     s2 = substr(nh,2,1);
     s3 = substr(nh,3,1);
     s4 = substr(nh,4,1);
     s5 = substr(nh,5,1);
     s6 = substr(nh,6,1);
     s7 = substr(nh,7,1);
     s8 = substr(nh,8,1);

   
     info(strfmt("%1  %2  %3  %4  %5  %6  %7  %8  %9",nh,s1,s2,s3,s4,s5,s6,s7,s8));
}

 // Use Display Method for 8 Square Box which contains String Controls

   // and in those 8 display methods apply  below concept for respective controls

    SysDate DateVal;

    str     DateStrVal;

    int     DateLength;

    str     DayDigit1, DayDigit2;

    str     MonthDigit1, MonthDigit2;

    str     YearDigit1, YearDigit2, YearDigit3, YearDigit4;

    ;

    DateVal = today();

    DateStrval = date2str(DateVal,   // DateValue

                         123,        // Date Month Year Sequence

                         2,          // Display Day Format in 2 digits

                         1,          // Separator1

                         2,          // Display Month Format in 2 Digits

                         1,          // Separator2

                         4);         // Display Year Format in 4 digits

    info(DateStrVal);   

    DateLength = strLen(DateStrval);   // Length Of Date  

    info(int2Str(DateLength));   

    DayDigit1 = substr(DateStrval, 1, 1); // Getting First Digit of Date

    info(DayDigit1);

    DayDigit2 = substr(DateStrval, 2, 1); // Getting Second Digit of Date

    info(DayDigit2);

    MonthDigit1 = substr(DateStrval, 4, 1); // Getting First Digit of Month

    info(MonthDigit1);

    MonthDigit2 = substr(DateStrval, 5, 1); // Getting Second Digit of Month

    info(MonthDigit2);

    YearDigit1 = substr(DateStrval, 7, 1); // Getting First Digit of Year

    info(YearDigit1);

    YearDigit2 = substr(DateStrval, 8, 1); // Getting Second Digit of Year

    info(YearDigit2);

    YearDigit3 = substr(DateStrval, 9, 1); // Getting Second Digit of Year

    info(YearDigit3);

    YearDigit4 = substr(DateStrval, 10, 1); // Getting Second Digit of Year

    info(YearDigit4);

How to Setup Date Format on AX SSRS Report

Hi

If you want to setup a different Format Date on a SSRS report, you can follow the below options:

1- Modify date format for all date textbox present in the SSRS Report using the Format function, like =Format(Fields!TransDate.Value, "dd/MM/yyyy")

2- If you want follow the User Timezone, for all date textbox you can use ConvertUtcToAxUserTimeZoneForUser method, like =Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, =Fields!TransDate.Value, "d", Parameters!AX_RenderingCulture.Value)

Enjoy!

CurrencyExchangeHelper class in Dynamics AX 2012 [X+

CurrencyExchangeHelper class in Dynamics AX 2012 [X++]

Friends,

In Microsoft Dynamics AX 2012, the currency and exchange rate framework has been enhanced to share information across multiple legal entities.

There is a new class by name CurrencyExchangeHelper that has been introduced in AX 2012 to support this.

This class will help you to do some calculations between currencies. Some important methods to use:

calculateTransactionToAccounting

Example : This method will convert the transaction currency in to accounting currency defined in ledger Table.

static void SR_CEH_Example1(Args _args)

{

    CurrencyExchangeHelper currencyExchangeHelper;

    CurrencyCode transCurrency = ‘EUR’;

    AmountCur amountCur = 500.00;

    AmountMst amountMST;

   

    currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet());

    amountMST = currencyExchangeHelper.calculateTransactionToAccounting(transCurrency, amountCur ,true);

    info(strFmt(‘%1’,amountMST));

}

Result :

calculateAccountingToTransaction

This method calculates the transaction currency amount from an accounting currency given.

static void SR_CEH_Example2(Args _args)

{

    CurrencyExchangeHelper currencyExchangeHelper;

    CurrencyCode transCurrency = ‘EUR’;

    AmountCur amountCur;

    AmountMst amountMST = 500.00;

   

    currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::primaryLedger(CompanyInfo::findDataArea("DUM").RecId), systemDateGet());

    amountCur = currencyExchangeHelper.calculateAccountingToTransaction(transCurrency, amountMST ,true);

    info(strFmt(‘%1’,amountcur));

}

Result :

While searching through, I found that there are  parmExchangeRate1 and parmExchangeRate2  methods that to calculate based on the exchange rates that have been provided. Please refer to the below example which calculates the misc charges [markup amount] based on the exchange rates defined.

Class Name : Markup >> calcMarkupAmount

Exploring more…will post soon!

Happy Dax6ng,

AX SSRS Report times out after 10 minutes (Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)

AX SSRS Report times out after 10 minutes (Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)

Sometimes when running a report that is going to return a large data set you may get the following error message after 10 minutes:

"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"

Whenever you receive this error the first thing you should look at is your DP class and can performance be improved by rewriting some of your code. If it cant be or this is a default ax report sometimes your only option is to increase the default timeout period.

In order to increase the timeout for an AX SSRS report you need to do the following:

1.) Create a new client config(or copy an existing one.) This is the same config that could be used as your .axc file that is used to access different environments/aos. However this will have larger timeout values in it so only use this on the server mentioned in step 4. You do not need your user's applications config to have these settings.

2.) Update the binding node of "QueryServiceEndPoint" and change the sendTimeout property from "00:10:00" (10 minutes) to whatever timeout value you like in hh:mm:ss format. You can also update any other timeout setting as well if you like. (Just search for 00:10:00 within the document and replace it with your new time)

3.) Save the new config as  "Microsoft.Dynamics.AX.ReportConfiguration.axc" (https://technet.microsoft.com/en-us/library/hh389774.aspx)

4.) Copy the new file Microsoft.Dynamics.AX.ReportConfiguration.axc into the following folder on your sql/ssrs server (inside of the os/windows file system)

C:\Program Files\Microsoft SQL Server\MSRS11.<AOS>\Reporting Services\ReportServer\bin

Note: This is for a sql2012 instance. If you are running a different version of ms sql please refer to the following TechNet article to find the location of your report server bin folder https://technet.microsoft.com/en-us/library/hh389774.aspx if you need help locating it.

5.) Run the report and the report should no longer time out. You do not need to restart the aos, sql or ssrs services in order for the changes to take place once the file is in this folder it should now respect your new timeout settings


If this does not fix your issue please refer to the following sites which can also help you on time out issues with ssrs reports

TechNet: Tips to help prevent long-running reports from timing out [AX 2012]

You can also change the timeout settings on ssrs via the following method:

Global timeout setting: Go to site settings > General > Report Timeout > do not timeout report.
Report timeout setting:  Go to the report > Manage > Processing options > Report time out> do not timeout report.


There are also some timeout settings if you connect to the reporting service via sql management studio. Right click on your server node and go to properties > Advanced.


Update: Also see AX SSRS - Regular Processing (SRSReportDataProviderBase) VS PreProcessing (SrsReportDataProviderPreProcess) (fixes timeout issues on reporting)


Update 2: After applying the Microsoft.axc file to the ssrs server you may run into a security issue (Microsoft Dynamics AX 2012 Reporting extensions: Error System.Security.Permissions.EnvironmentPermission while running report)

To fix this go to the SSRS installation folder and modify rssrvpolicy.config Report_Expressions_Default_Permissions node from "Execution" to "FullTrust" (https://blogs.msdn.microsoft.com/axsupport/2012/02/02/microsoft-dynamics-ax-2012-reporting-extensions-error-system-security-permissions-environmentpermission-while-running-report/) 

Microsoft Dynamics AX 2012 SSRS REPORT ERROR: a connection attempt failed because the connected party did not properly respond after a period of time

AX SSRS - Regular Processing (SRSReportDataProviderBase) VS PreProcessing (SrsReportDataProviderPreProcess) (fixes timeout issues on reporting)

Microsoft Dynamics AX 2012 SSRS REPORT ERROR: a connection attempt failed because the connected party did not properly respond after a period of time

Sometimes in AX we need to create reports that exceed the default run-time limits defined in AX (default is 10minutes) because of the amount of data we are either calculating or returning. As discussed in one of my other posts (AX SSRS Report times out after 10 minutes) one of the ways to overcome this is to change the config files of the client/ssrs server but another way to overcome this limit and improve the speed in which a report can be ran is changing your data provider to extend the class 'SrsReportDataProviderPreProcess' instead of the regular type of 'SRSReportDataProviderBase'

I found this great blog entry over at msdn (click here) with this nice graphic shown below that explains the differences between the two. But long story short the difference is we are utilizing the aos to generate the data then handing the results to ssrs rather than handing the logic to ssrs and letting it generate the data. By doing this we can bypass the time out that is defined in ssrs because the data is already generated before we hand it off and we can improve runtime because it is now generating the data on the aos server.


Regular processing:



Pre-processing:

In order to switch from the regular process we need to make the following changes:

1. On the table that is returned as a dataset to the report make the following changes
   1. Created by - Yes
   2. CreatedTransactionId - Yes
   3. TableType - Regular
2. Change the DP class so it extends SrsReportDataProviderPreProcess
3. Within the DP class.processReport() method add the following code reportData.setConnection(this.parmUserConnection()); where reportData is the table that is returned as a dataset within your report
4. Execute a incremental cil
5. Open the SSRS report and hit refresh on the dataset. At this point you should see the field 'createdTransactionId' added to the available field.
6. Deploy report
7. Security Note: For your security object it is good to note that when using this preprocessing method you need to add the DPClass.processReport() method to the Server methods node in the privilege associated with the report