Import Fixed Assets From Excel to AX using X++

static void importAsset2(Args _args)
{
    Dialog                      dialog;
    Dialogfield                 dialogfield;

    SysExcelApplication         application;
    SysExcelWorkbooks           workBooks;
    SysExcelWorkbook            workBook;
    SysExcelWorksheets          workSheets;
    SysExcelWorksheet           workSheet;
    SysExcelCells               cells;

    AssetLocation               assetLocation;
    AssetGroup                  assetGroup;
    AssetTable                  assetTable;
    AssetBook                   assetBook;
    AssetBookTable              assetBookTable;

    AssetLocationId             location;
    AssetGroupId                assetGrp;
    AssetBookId                 assetBkId;
    AssetId                     assetId;
    Name                        name;
    AssetAcquisitionDate        assetAcqDate;
    AssetAcquisitionPrice       assetAcqPrice;
    AssetDepreciate             assetDepreciate;
    AssetServiceLife            assetServiceLife;
    AssetPostingProfile         assetPostingProfile;
    AssetStatus                 assetStatus;

    Filename                    fileName;
    COMVariantType              type;
    int                         row = 1   ;
    int                         recordcnt;
    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 "";
        }

    application =   SysExcelApplication::construct();
    workBooks   =   application.workbooks();
    dialog      = new Dialog("FileOpen");
    dialogfield = dialog.addField(extendedTypeStr(Filenameopen), "File Name");
    dialog.run();

    if (dialog.run())
    {
    filename = (dialogfield.value());
    }

    //fileName    =   @"C:\Users\Sandeep.Madupu\Desktop\Mine.xlsx";
    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();

    do
    {

        row++;
        location    = cells.item(row, 11).value().bStr();
        assetGrp    = cells.item(row, 1).value().bStr();
        assetBkId   = cells.item(row, 3).value().bStr();
        assetId     = cells.item(row, 2).value().bStr();
        name        = cells.item(row, 4).value().bStr();
        assetAcqDate = cells.item(row, 6).value().date();
        assetDepreciate = str2enum(assetDepreciate,cells.item(row, 10).value().bStr());
        assetPostingProfile = cells.item(row, 7).value().bstr();
        assetServiceLife = cells.item(row, 8).value().double();
        assetAcqPrice   =  cells.item(row, 5).value().double();

        ttsBegin;

        select assetLocation where assetLocation.Location == location;
        select assetGroup where assetGroup.GroupId == assetGrp;
        select assetBookTable where assetBookTable.BookId == assetBkId;
        select assetTable where assetTable.AssetId == assetId;
        select assetBook where assetBook.BookId == assetBkId;

        if(!assetLocation::find(location).Location)
        {
            assetlocation.clear();
            assetLocation.initValue();
            assetLocation.Location = location;
            assetLocation.Name     = location;
            assetLocation.insert();
        }

        if(!assetGroup.GroupId)
        {
            assetGroup.clear();
            assetGroup.initValue();
            assetGroup.GroupId  =   assetGrp;
            assetGroup.Name     =   assetGrp;
            assetGroup.Location =   location;
            assetGroup.insert();

        }
       
        if(assetTable.AssetId)
        {

            select forUpdate assetTable where  assetTable.AssetId    == assetId;

            ttsBegin;
            assetTable.initValue();
            assetTable.AssetId  =   assetId;
            assetTable.Name     =   name;
            assetTable.AssetGroup = assetGrp;
            assetTable.Location   = location;
            assetTable.Update();
            ttsCommit;
        }

        else
        {
            assetTable.clear();
            assetTable.initValue();
            assetTable.AssetId  =   assetId;
            assetTable.Name     =   name;
            assetTable.AssetGroup = assetGrp;
            assetTable.Location   = location;
            assetTable.insert();
        }

        if(!assetBookTable.BookId)
        {
            assetBookTable.clear();
            assetBookTable.initValue();
            assetBookTable.BookId = assetBkId;
            assetBookTable.Description = assetBkId;
            assetBookTable.insert();

        }
     
       select forupdate assetBook join  assetTable  where assetBook.AssetId == assetTable.AssetId
                                                            && assetTable.AssetId == assetId
                                                            && assetBook.BookId   == assetBkId;

        if(assetBook.BookId)
        {

            ttsBegin;
            assetBook.BookId    =   assetBkId;
            assetBook.AcquisitionDate = assetAcqDate;
            assetBook.AcquisitionPrice = assetAcqPrice;
            assetBook.ServiceLife      = assetServiceLife;
            assetBook.PostingProfile   = assetPostingProfile;
            assetBook.Status           = assetStatus::NoAcquisition;
            assetBook.Depreciation     = assetDepreciate;
            assetBook.AssetId          = assetTable.AssetId;
            //assetBook.selectForUpdate(true);
            assetBook.Update();
            ttsCommit;

    }

        else
        {
            assetBook.clear();
            select assetBookTable where assetBookTable.BookId == assetBkId;

            select assetTable where assetTable.AssetId == assetId;

            assetBook.initValue();
            assetBook.BookId           =   assetBkId;
            assetBook.AcquisitionDate  = assetAcqDate;
            assetBook.AcquisitionPrice = assetAcqPrice;
            assetBook.ServiceLife      = assetServiceLife;
            assetBook.PostingProfile   = assetPostingProfile;
            assetBook.Status           = assetStatus;
            assetBook.Depreciation     = assetDepreciate;
            assetBook.AssetId          = assetTable.AssetId;
            assetBook.insert();
        }



        ttsCommit;

        type = cells.item(row+1, 1).value().variantType();
        info(strFmt("Records %1-%2 inserted",assetTable.AssetId,assetTable.Location));

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

        application.quit();
        workbooks.close();
        info("Done");
    }

Developing Query based SSRS Report.

Let’s see how to get the customer details in a SSRS  Query  Based report.

Create a query in ax.
In AOT select Queries and right click select New Query.

In properties, change the Name property as SSRS_CustomerList.

Expand the SSRS_CustomerList query, select data source node and right click select new data source.

In properties, change the name as Custtable and select the custtable in Table Property.

And expand the custtable data source, field properties change the dynamic property to yes and in data source node right click add new data source and change the property as shown below image.

In relation node right click new relation, change the properties as below.

Now we created Customer list query.

Open visual Studio, Select new Project

Select the Project type as Microsoft Dynamics Ax, select Report model and change the Name and as

SSRS_CustomerList and then click Ok.

In solution explorer, right click add report as shown below.

Then change the property of Report name as CustList.

Right click Add Dataset and change the Properties as below

In properties tab, select Query as Data Source Type,

change the name property as customerlist and select the toggle button it pop up a window.

In that window select the ssrs_customerlist query as shown below.

Click Next, and select the fields as show below and then click OK.

Now will come to Design part, select the design node and right click select new Auto Design.

Change the property Name as CustomerListDesign.

Now right click the customerautodesign , ADD table as shown below image.

Drag the fields from dataset >> customerlist>>field to design>>customerlistdesign>>data and save it.

Design Part is completed. Now can see the preview of the report. Just follow the below image

Deploy the Report to Report server:

In solution explorer select the report and right click and click the deploy button.

Deploy the Project into AX:

Select the report in solution explorer, right click and click ADD SSRS_CustomerList to AOT.

We can observer in AOT under Visual studio Project>>Dynamics AX model Projects.

And also we can observer the CustList under ssrs report in the AOT.

Create display menu function for cust list report.

Follow the below images change the properties for the display menu item as shown below.