Export Data AX to Excel

 static void ExportDataGeneralJournal(Args _args)

{

    SysExcelApplication     xlsApplication;

    SysExcelWorkBooks       xlsWorkBookCollection;

    SysExcelWorkBook        xlsWorkBook;

    SysExcelWorkSheets      xlsWorkSheetCollection;

    SysExcelWorkSheet       xlsWorkSheet;

    SysExcelRange           xlsRange;

    GeneralJournalEntry         jourEntry;

    GeneralJournalAccountEntry  jourAccEntry;

    FromDate                    fromDate = mkDate(1,1,2018);

    ToDate                      toDate = mkDate(31,1,2018);

    int                     row = 1;

    str                     fileName;

    ;

    //Filename

    fileName = "Document\GeneralAccEntry.xlsx";

    //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);

    //Excel columns captions

    xlsWorkSheet.cells().item(row,1).value("Date");

    xlsWorkSheet.cells().item(row,2).value("Voucher");

    xlsWorkSheet.cells().item(row,3).value("PostingType");

    xlsWorkSheet.cells().item(row,4).value("LedgerAccount");

    row++;

    while select jourEntry

        join jourAccEntry

        where jourEntry.RecId == jourAccEntry.GeneralJournalEntry

           && jourEntry.AccountingDate >= fromDate

           && jourEntry.AccountingDate <= toDate

    {

        xlsWorkSheet.cells().item(row,1).value(jourEntry.AccountingDate);

        xlsWorkSheet.cells().item(row,2).value(jourEntry.SubledgerVoucher);

        xlsWorkSheet.cells().item(row,3).value(enum2str(jourAccEntry.PostingType));

        xlsWorkSheet.cells().item(row,4).value(jourAccEntry.LedgerAccount);

        row++;

    }

    info("Done");

    //Check whether the document already exists

    if(WinApi::fileExists(fileName))

        WinApi::deleteFile(fileName);

    //Save Excel document

    xlsWorkbook.saveAs(fileName);

    //Open Excel document

    xlsApplication.visible(true);

    //Close Excel

    //xlsApplication.quit();

    //xlsApplication.finalize();

}

Read more...

Create default from date 1 Month Ago in SSRS Expression

 DateAdd("M", -1, Today()

Read more...

Decrease Decimal in AX 2012

 

num2str0(amount, 0, 2, 1,0) // two decimal

num2str0(amount, 0, 0, 1,0) // without decimal 

Read more...

Get Invent Site from WareHouse and Item by Code X++

static void OnHandOnDate(Args _args)

{

    ItemId                      itemId;

    InventDim                   inventDimCriteria;

    InventDimParm               inventDimParm;

    InventSumDatePhysicalDim    inventSumDateDim;

    InventTable                 InventTable;

    InventLocation              Inventlocation;

    ABUSnapshot                 ABUSnapshot;

    InventSiteId                inventSiteId;

    transDate                   Dated;

    ;

    while select * from 

        InventTable 

            order by itemid

            where InventTable.itemid like 'P*'

            //where InventTable.itemid == "P000005"

    {

        

        Dated = systemDateGet();

        

        while select * from 

            Inventlocation 

                order by inventlocationid

                //where Inventlocation.InventLocationId == "CNK"

                where Inventlocation.WMSLocationIdDefaultReceipt == 'Staging'

        {

            itemId                              = InventTable.ItemId;

            inventDimCriteria.InventLocationId  = Inventlocation.InventLocationId;

            inventDimCriteria.InventStatusId    = "AVAILABLE";

    

            inventDimParm.initFromInventDim(inventDimCriteria);

            //inventSumDateDim = InventSumDatePhysicalDim::newParameters(mkdate(03,06,2022), itemId, inventDimCriteria, inventDimParm);

            // Dated

            inventSumDateDim = InventSumDatePhysicalDim::newParameters(Dated, itemId, inventDimCriteria, inventDimParm);

            info(strfmt("itemid : %1 Phs: %2 Whs : %3",itemId, inventSumDateDim.physicalQuantity(), Inventlocation.InventLocationId));

            

            ttsbegin;

            ABUSnapshot.ItemId          = itemId;

            ABUSnapshot.ProductName     = EcoResProduct::find(InventTable.Product).productName();

            ABUSnapshot.WHS             = Inventlocation.InventLocationId;

            ABUSnapshot.Site            = InventLocation::find(Inventlocation.InventLocationId).InventSite().SiteId;

            ABUSnapshot.Date            = Dated;

            ABUSnapshot.DataAreaName    = curext();

            ABUSnapshot.Qty             = inventSumDateDim.physicalQuantity();

            ABUSnapshot.insert();

            

            ttsCommit;

        }

    }

}

Read more...