Deploy the default reports [AX 2012]

Complete the following procedure to open Windows PowerShell and view a list of the reports that are included with Microsoft Dynamics AX.

1. Open Windows PowerShell as an administrator by following these steps:
1. Click Start > Administrative Tools.
2. Right-click the Microsoft Dynamics AX 2012 Management Shell option.
3. Click Run as administrator.

• To deploy a specific report, enter the name of the report. For example, to deploy the CustTransList report, enter the following command:
  Windows PowerShell

  Publish-AXReport -ReportName CustTransList

• To deploy two or more specific reports, enter the names of the reports. For example, to deploy the CustTransList and CustTransOpenPerDate reports, enter the following command:
  Windows PowerShell

  Publish-AXReport -ReportName CustTransList, CustTransOpenPerDate
  

• To deploy all reports, enter the following command:
  Windows PowerShell

  Publish-AXReport –ReportName *

Restart the Reporting Services service

If you’re running Reporting Services 2008 (in either native or SharePoint integrated mode) or Reporting Services 2012 or 2014 in native mode, restart the service by following these steps:

1. Click Start > Administrative Tools > Services to open the Services management console.
2. Right-click the SQL Server Reporting Services service and choose Restart.

Refresh the AOD cache

Complete the following steps to refresh the application object directory (AOD) cache. This will clear cached application object information for all items in the application object tree (AOT) of Microsoft Dynamics AX.

1. Open the Microsoft Dynamics AX client.
2. Open the development workspace.
3. Click Tools > Caches > Refresh elements.
   After the cache has been cleared, the Infolog displays a message indicating that AOD elements have been refreshed.

Instruct users to delete usage data

Instruct Microsoft Dynamics AX users to delete usage data. Selections that users make while Microsoft Dynamics AX is running are saved as usage data. This data is used to improve the users’ experiences the next time that they run Microsoft Dynamics AX.

Instruct users to delete usage data by following these steps:

1. Open the Microsoft Dynamics AX client.
2. Click File > Tools > Options. The Options form is displayed.
3. Click Usage data.
4. On the General tab, click Reset.

Select Statement Examples [AX 2012]

General Examples

The following X++ job shows several small examples of how you can use the select statement.

static void SelectRecordExamples3Job(Args _args)
{
    CustTable custTable;  // As of AX 2012.

    // A customer is found and returned in custTable
    select * from custTable;
    info("A: " + custTable.AccountNum);

    // A customer with account number > "100" is found
    select * from custTable
        where custTable.AccountNum > "100";
    info("B: " + custTable.AccountNum);

    // Customer with the lowest account number > "100" found:
    select * 
        from custTable 
            order by accountNum
                where custTable.AccountNum > "100";
    info("C1: " + custTable.AccountNum);

    // The next customer is read
    next custTable;
    info("C2: " + custTable.AccountNum);

    // Customer with higest account number
    // (greater than 100) found: Fourth Coffee
    select * 
        from custTable 
            order by accountNum desc
                where custTable.accountNum > "100";
    info("D1: " + custTable.AccountNum);
    
    // The next record is read (DESC): Fabrikam, Inc.
    next custTable; 
    info("D2: " + custTable.AccountNum);

    // Customer with highest account number found: Fourth Coffee
    select reverse custTable 
        order by accountNum;
    info("E: " + custTable.AccountNum);

    // Customer with "lowest" name and account number
    // in the interval 100 to 1000 is found. This is Coho Winery.
    select * 
        from custTable 
            order by DlvMode
                where custTable.accountNum > "100"
                    && custTable.accountNum < "1000";
    info("F: " + custTable.AccountNum);

    // The count select returns the number of customers.
    select count(AccountNum) 
        from custTable;
    // Prints the result of the count
    info(strFmt("G: %1 = Count of AccountNums", custTable.accountNum));

    // Returns the average credit max for non-blocked customers.
    select avg(CreditMax) 
        from custTable
            where custTable.blocked == CustVendorBlocked::No;
    // Prints the result of the avg
    info(strFmt("H: %1 = Average CreditMax", custTable.CreditMax));
}
/*** Display from infolog:
Message (02:00:34 pm)
A: 4000
B: 4000
C1: 4000
C2: 4001
D1: 4507
D2: 4506
E: 4507
F: 
G: 29 = Count of AccountNums
H: 103.45 = Average CreditMax
***/

Join Sample









This X++ code sample shows how an inner join can be performed as part of an SQL select statement.

The sample also shows an order by clause that has each field qualified by a table name. This enables you to control how the retrieved records are sorted by using only oneorder by clause.
static void SelectJoin22Job(Args _args)
{
    CustTable xrecCustTable;
    CashDisc xrecCashDisc;
    struct sut4;

    sut4 = new struct("str AccountNum; str CashDisc; str Description");

    while select firstOnly10 *
        from xrecCustTable
            order by xrecCashDisc.Description
                join xrecCashDisc
                    where xrecCustTable.CashDisc ==
                        xrecCashDisc.CashDiscCode
                        && xrecCashDisc.Description LIKE "*Days*"
    {
        sut4.value("AccountNum", xrecCustTable.AccountNum );
        sut4.value("CashDisc", xrecCashDisc.CashDiscCode );
        sut4.value("Description", xrecCashDisc.Description );

        info(sut4.toString());
    }
/*********  Actual Infolog output
Message (02:29:37 pm)
(AccountNum:"1101"; CashDisc:"0.5%D10"; Description:"0.5% 10 days")
(AccountNum:"4001"; CashDisc:"0.5%D10"; Description:"0.5% 10 days")
(AccountNum:"1102"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"1201"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"2211"; CashDisc:"0.5%D30"; Description:"0.5% 30 days")
(AccountNum:"1202"; CashDisc:"1%D15"; Description:"1% 15 days")
(AccountNum:"1203"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2212"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2213"; CashDisc:"1%D07"; Description:"1% 7 days")
(AccountNum:"2214"; CashDisc:"1%D07"; Description:"1% 7 days")
*********/
}

Group By and Order By









This X++ code sample shows that the fields in the group by clause can be qualified with a table name. There can be multiple group by clauses instead of just one. The fields can be qualified by table name in only one group by clause. Use of table name qualifiers is recommended.

The order by clause follows the same syntax patterns that group by follows. If provided, both clauses must appear after the join (or from) clause, and both must appear before the where clause that might exist on the same join. It is recommended that all group by and order by and where clauses appear immediately after the last join clause.
static void SelectGroupBy66Job(Args _args)
{
    CustTable xrecCustTable;
    CashDisc xrecCashDisc;
    struct sut4;

    sut4 = new struct("str AccountNum_Count; str CashDisc; str Description");

    while select
        count(AccountNum)
        from xrecCustTable
            order by xrecCashDisc.Description
                join xrecCashDisc
        group by
            xrecCashDisc.CashDiscCode
                    where xrecCustTable.CashDisc ==
                        xrecCashDisc.CashDiscCode
                        && xrecCashDisc.Description LIKE "*Days*"
    {
        sut4.value("AccountNum_Count", xrecCustTable.AccountNum );
        sut4.value("CashDisc", xrecCashDisc.CashDiscCode );
        sut4.value("Description", xrecCashDisc.Description );

        info(sut4.toString());
    }
/*********  Actual Infolog output
Message (02:45:26 pm)
(AccountNum_Count:"2"; CashDisc:"0.5%D10"; Description:"")
(AccountNum_Count:"3"; CashDisc:"0.5%D30"; Description:"")
(AccountNum_Count:"4"; CashDisc:"1%D07"; Description:"")
(AccountNum_Count:"1"; CashDisc:"1%D15"; Description:"")
(AccountNum_Count:"1"; CashDisc:"2%D30"; Description:"")
(AccountNum_Count:"1"; CashDisc:"3%D10"; Description:"")
*********/
}


Happy DAXing
........

delete_from [AX 2012]

You can delete multiple records from a database table by using a delete_from statement. This can be more efficient and faster than deleting one record at a time by using thexRecord .delete method in a loop.

If you have overridden the delete method, the system interprets the delete_from statement into code that calls the delete method one time for each row that is deleted.

Example 1: Comparing Multi-record Delete Techniques

The code examples in this section show that some techniques for deleting multiple records are more efficient than other techniques.

Example 1a: Delete_From to Delete Multiple Records

The following X++ code example is an efficient way to delete multiple records.

static void DeleteMultiRow1aJob(Args _args)
{
    MyWidgetTable tabWidget;
    ;
    delete_from tabWidget
        where tabWidget .quantity <= 100;
}

Example 1b: Delete Method Plus ForUpdate Keyword


The following X++ code example is inefficient. It issues a separate SQL delete call to the database server for each record. The xRecord .delete method never deletes more than one record per call.
static void DeleteMultiRow1bJob(Args _args)
{
    MyWidgetTable tabWidget; // extends xRecord.
    ;
    ttsBegin;
    while select
        forUpdate
        tabWidget
        where tabWidget .quantity <= 100
    {
        tabWidget .delete();
    }
    ttsCommit;
}

Example 2: Delete Plus Inner Join Logic







X++ does not support an inner join on the delete_from statement. Therefore you cannot use the unmodified join keyword on the delete_from statement. However, there are other ways to logically accomplish an inner join.

The examples in this section show techniques for achieving inner join logic by a sequence of X++ statements.

Example 2a: Delete Method Plus Inefficient Inner Join Logic


The following X++ code example is inefficient. It issues a separate SQL delete call to the database server for each record.
static void DeleteInnerJoin2aJob(Args _args)
{
    MyWidgetTable tabWidget; // extends xRecord.
    ;
    ttsBegin;
    while select
        forUpdate
        tabWidget
        join tabGalaxy
            where
                tabWidget .GalaxyRecId == tabGalaxy .RecId
                && tabGalaxy .isTrusted == 0
    {
        tabWidget .delete();
    }
    ttsCommit;
}

Example 2b: Delete_From Plus Efficient Inner Join Logic


The following X++ code example is relatively efficient. It issues a separate delete_from statement for each loop iteration. However, each delete_from statement can delete multiple records, a subset of all the records that the job deletes.
static void DeleteInnerJoin2bJob(Args _args)
{
    MyWidgetTable tabWidget; // extends xRecord.
    ;
    ttsBegin;
    while select
        from tabGalaxy
            where tabGalaxy .isTrusted == 0
    {
        delete_from tabWidget
            where tabWidget .GalaxyRecId ==
                  tabGalaxy .RecId;
    }
    ttsCommit;
}

Example 3: Delete_From Notexists Join







You can use the notexists join keyword pair in a delete_from statement.

The delete_from statements in the following X++ code example are efficient. The notexists join clause enables the delete_from statement to delete a specific set of rows. In this example the delete_from statement removes all the parent order header rows for which there are no child order line rows.

You can also use the exists join clause on the delete_from statement.
static void DeleteFromNotexists3bJob(Args _args)
{
    GmTabOrderHeader tabOHeader;
    GmTabOrderLine tabOLine;
    AddressState tabAddressState;
    str 127 sOH_Info;
    str 127 sOL_Data;
    int64 i64OHRecId;
    ;
    delete_from tabOLine;
    delete_from tabOHeader;

    // Inserts into parent table.

    sOH_Info = "Albert needs tires.";
    insert_recordset tabOHeader
        (OH_Info)
        select firstOnly sOH_Info from tabAddressState;

    sOH_Info = "Benson wants plastic.";
    insert_recordset tabOHeader
        (OH_Info)
        select firstOnly sOH_Info from tabAddressState;

    // Obtain a OrderHeader RecId,
    // use it to insert one child row.

    sOL_Data = "4 re-treads.";
    while select firstOnly tabOHeader
            order by OH_Info
            where tabOHeader .OH_Info like "A*"
    {
        i64OHRecId = tabOHeader .RecId;
        insert_recordset tabOLine
            (OL_Data ,OrderHeaderRecId)
            select firstOnly
                sOL_Data ,i64OHRecId
                from tabAddressState;
        break;
    }

    // Before the delete notexists.
    // Display all parent, and then all child rows.

    while select tabOHeader
        order by OH_Info
    {
        info(strFmt(
            "Before: OHeader:  OH_Info==%1 , RecId==%2"
            ,tabOHeader .OH_Info ,tabOHeader .RecId
            ));
    }
    while select tabOLine
        order by OL_Data
    {
        info(strFmt(
            "Before: OLine:  OL_Data==%1 , OrderHeaderRecId==%2"
            ,tabOLine .OL_Data ,tabOLine .OrderHeaderRecId
            ));
    }

    // Delete_From NotExists Join, to remove from the
    // parent table all order headers without children.

    delete_from tabOHeader
        notexists join tabOLine
            where tabOHeader .RecId ==
                tabOLine .OrderHeaderRecId;

    info(strFmt
        ("%1 is the number of childless OHeader records deleted."
        ,tabOHeader.rowCount()));

    // After the delete notexists.
    // Display all parent, and then all child rows.

    info("- - - - - - - - - - - - - - -");
    while select tabOHeader
        order by OH_Info
    {
        info(strFmt(
            "After: OHeader:  OH_Info==%1 , RecId==%2"
            ,tabOHeader .OH_Info ,tabOHeader .RecId
            ));
    }
    while select tabOLine
        order by OL_Data
    {
        info(strFmt(
            "After: OLine:  OL_Data==%1 , OrderHeaderRecId==%2"
            ,tabOLine .OL_Data ,tabOLine .OrderHeaderRecId
            ));
    }
/**************  Actual Infolog output
Message (12:54:14 pm)
Before: OHeader:  OH_Info==Albert needs tires. , RecId==5637144608
Before: OHeader:  OH_Info==Benson wants plastic. , RecId==5637144609
Before: OLine:  OL_Data==4 re-treads. , OrderHeaderRecId==5637144608
1 is the number of childless OHeader records deleted.
- - - - - - - - - - - - - - -
After: OHeader:  OH_Info==Albert needs tires. , RecId==5637144608
After: OLine:  OL_Data==4 re-treads. , OrderHeaderRecId==5637144608
**************/
}

Happy DAXing
........

insert_recordset [AX 2012]

The ListOfFields in the destination table must match the list of fields in the source tables. Data is transferred in the order that it appears in the list of fields. Fields in the destination table that are not present in the list of fields are assigned zero-values as in other areas in X++. System fields, including RecId, are assigned transparently by the kernel in the destination table.

insert_recordset  DestinationTable  (  ListOfFields  )

select  ListOfFields1  from  SourceTable  [ where  WhereClause  ]

[ join  ListOfFields2  from  JoinedSourceTable 

[ where  JoinedWhereClause  ]]

Example 1: Sub-select that has a Group By

The records, myNum and mySum, are retrieved from the table anotherTable and inserted into the table myTable. The records are grouped according to myNum, and only themyNum records with a value less than or equal to 100 are included in the insertion.

insert_recordset myTable (myNum, mySum)
    select myNum, sum(myValue) 
        from anotherTable 
        group by myNum 
        where myNum <= 100;

Example 2: Comparing the Performance of Traditional Inserts and Insert_recordset Inserts







By comparing the following two X++ code examples, you can see the performance implications of different designs.

Example 2a: Copy Data for Each Record


The following code example shows the traditional way of copying data.
ttsBegin;
while select * from sqlDictionary
    where sqlDictionary.tabId > 0
{
    bufferDictionary.tabId        = sqlDictionary.tabId;
    bufferDictionary.fieldId      = sqlDictionary.fieldId;
    bufferDictionary.array        = sqlDictionary.array;
    bufferDictionary.name         = sqlDictionary.name;
    bufferDictionary.sqlName      = sqlDictionary.sqlName;
    bufferDictionary.fieldType    = sqlDictionary.fieldType;
    bufferDictionary.strSize      = sqlDictionary.strSize;
    bufferDictionary.shadow       = sqlDictionary.shadow;
    bufferDictionary.rightJustify = sqlDictionary.rightJustify;
    bufferDictionary.nullable     = sqlDictionary.nullable;
    bufferDictionary.flags        = sqlDictionary.flags;
    bufferDictionary.insert();
    countTables++;
    operationProgress.setCount(countTables);
}
ttsCommit;

Example 2b: Set Based Operation


The following code example achieves the same outcome as the previous example, but this example is much faster. This example uses the insert_recordset statement.
void copySQLDictionary2DictionaryLine()
{
    SqlDictionary sqlDictionary;
    ;
 
    ttsBegin;
    insert_recordset bufferDictionary(tabId, fieldId, array, 
        name, sqlName, fieldType, strSize, shadow, 
        rightJustify, nullable, flags)
    select tabId, fieldId, array, name, sqlName, fieldType, 
        strSize, shadow, rightJustify, nullable, flags 
        from sqlDictionary
        where sqlDictionary.tabId > 0;
    ttsCommit;
}

Performance Comparison Between 2a and 2b


When copying 14,311 rows, the number of trips to the SQL backend has been reduced from 14,213 to 6. The SQL backend time used has been reduced from 174 seconds to 10 seconds, which is a factor of 17. The overall reduction in time is larger because the new approach uses almost no resources from either the client or the Application Object Server (AOS).

Example 3: Data Assignments from Variables







This X++ code example shows that the insert_recordset statement can insert data that is provided in variables. In this example, the keyword firstonly is used so that only one row is inserted.
static void InsertVariable3Job(Args _args)
{
    TableAlphabet    tabA2;
    BankAccountTable tabB3;
    str  1 sLetter = "a";
    str 16 sExampleWord = "apple";
    ;
    DELETE_FROM tabA2;

    INSERT_RECORDSET tabA2
        (Letter ,ExampleWord)
    select firstonly
        sLetter ,sExampleWord // Variables.
    from tabB3;

    WHILE SELECT * from tabA2
    {
        info(tabA2 .Letter + " , " + tabA2 .ExampleWord);
    }
/***********  Actual Infolog output
Message (04:03:52 pm)
a , apple
***********/
}

Example 4: Joins







The following X++ code example shows a join of three tables on an insert_recordset statement that has a sub-select. Also, a while select statement with a similar join is shown.

A variable is used to supply the inserted value for one column. The str variable must be declared with a length that is less than or equal to the maximum length of the corresponding database field.
static void InsertJoin42Job(Args _args)
{
    GmTabDepartment tabDept2;
    GmTabEmployee tabEmpl3;
    GmTabProject tabProj4;
    GmTabEmployeeProject tabEmplProj5;
    str 64 sDescriptionVariable = "From variable.";
    ;
    DELETE_FROM tabEmplProj5;

    INSERT_RECORDSET tabEmplProj5
        (
        Description
        , EmployeeRecId
        , ProjectRecId
        )
    Select
        sDescriptionVariable
        , RecId
    from
        tabEmpl3
        join
            tabDept2
            where tabEmpl3 .DepartmentGuid == tabDept2 .DepartmentGuid
        join RecId
            from tabProj4
            where tabDept2 .DepartmentGuid == tabProj4 .DepartmentGuid
    ;

    info(int642str(tabEmplProj5 .rowCount())
        + " ==Number of rows inserted.");

    WHILE SELECT *
        from
            tabEmplProj5
            join tabEmpl3
                where tabEmplProj5 .EmployeeRecId == tabEmpl3 .RecId
            join tabProj4
                where tabEmplProj5 .ProjectRecId == tabProj4 .RecId
    {
        info(
            tabEmpl3 .EmployeeName
            + "  --works on--  "
            + tabProj4 .ProjectName
            + " (" + tabEmplProj5 .Description + ")."
            );
    }
/*****************  Actual Infolog output
Message (01:05:41 pm)
4 ==Number of rows inserted.
Alice  --works on--  Project ZZZ (From variable.).
Alice  --works on--  Project YY (From variable.).
Beth  --works on--  Project ZZZ (From variable.).
Beth  --works on--  Project YY (From variable.).
*****************/
}


Happy DAXing
........

update_recordset [AX 2012]

Example 1: Assigning from a Math Expression

MyTable myTableBuffer;
;
update_recordset myTableBuffer
setting field1 = field1 * 1.10;

Example 2: Using a Where Clause
MyTable myTableBuffer;
;
update_recordset myTableBuffer
setting
    field1 = 1,
    field2 = fieldX + fieldY
where field1 == 0;
Example 3: Joining Tables in an Update
static void Join22aJob(Args _args)
{
    TableEmployee tabEmpl;
    TableDepartment tabDept;
    TableProject tabProj;
    ;
    update_recordset tabEmpl
    setting
        currentStatusDescription = tabDept .DeptName
            + ", " + tabProj .ProjName
    join tabDept
        where tabDept .DeptId == tabEmpl .DeptId
    join tabProj
        where tabProj .ProjId == tabEmpl .ProjId;

    info(strFmt("Number of records updated is %1."
        ,tabEmpl .rowCount()));
}

Happy DAXing
.........

Get User's selected records and update values

Hi,
Here we will see that how to get (fetch) user's selected records only and apply update method on selected records only (i.e. there are thousands records and user wants to update value only in 10 then user will select 10- records and click on the action button then action should be performed).
Scenario- 1: 

• Create "Button", add it into related form then expand that Button, add Clicked() method and add logic.
• Here CustPackingSlipTrans is the data source of that form where this clicked method is mapped

// <purpose> to update "boolean" value on the basis of user's selected records
void clicked()
{
    CustPackingSlipTrans CustPackingSlipTransLoc,CustPackingSlipTransLocal;
    super();
    //recordcount = ReservationCancellation_ds.recordsMarked().lastIndex();
    CustPackingSlipTransLoc = CustPackingSlipTrans_ds.getFirst(1);
    while(CustPackingSlipTransLoc)
    {
        ttsBegin;
        //CustPackingSlipTransLoc.MSASNExport = NoYes::No;
        //CustPackingSlipTransLoc.update();
        update_recordSet CustPackingSlipTransLocal
            setting MSASNExport = NoYes::No
            where CustPackingSlipTransLocal.RecId == CustPackingSlipTransLoc.RecId;
        ttsCommit;
        CustPackingSlipTransLoc = CustPackingSlipTrans_ds.getNext();
    }
    //info(CustPackingSlipTransLoc.SalesId);
    CustPackingSlipTrans_DS.refresh();
    CustPackingSlipTrans_DS.research();
}

Happy DAXing
...................

Export Region- wise Data from AX to csv with seperate file name

how to export transactions with default dimensions to Excel

We recently had a support request where customer wanted to see all fixed asset transactions with financial dimensions in an Excel file. The code below solved the problem. I hope this code example can help people who are thinking of creating a simple xls report or want to see all default dimension values with transactions in one table.
Problem: On the fixed asset transaction form (Fixed assets -> Inquiries -> Fixed asset transactions) you will see the tab 'Financial dimensions' but it is not possible to personalize it so that the dimensions will appear on the Overview tab so that we could export/copy them to excel together with the rest of the data from the Overview tab. So, in short, we need to see fixed asset transactions together with the dimensions either in a report (that we can then export to excel) or on the fixed asset transactions form (Fixed assets -> Inquiries -> Fixed asset transactions) so that we can export this data to excel.
Solution: The following code was proposed that exports all fixed asset transactions to an Excel file together with all dimensions. Because the set of dimensions can be different on different transactions, we're adding dimension columns dynamically in the loop.

---

public static void main(Args _args)
{
    AssetTrans assetTrans;  
    SysExcelApplication application;
    SysExcelWorkBooks workbooks;
    SysExcelWorkBook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    SysExcelCell cell;
    int row;
    DimensionAttributeValueSetItemView dimAttrSet;
    DimensionAttribute dimAttr;
    str dimAttrStr;
    Map dims;
    int dimNum;
    ;
    application = sysExcelApplication::construct();
    workbooks = application.workbooks();
    workbook = workbooks.add();  
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@');

    dims = new Map(Types::String, Types::Integer);

    //generate header    row++;
    cell = cells.item(row, 1);  
    cell.value("Voucher");
    cell = cells.item(row, 2);  
    cell.value("Transaction date");
    cell = cells.item(row, 3);  
    cell.value("Fixed asset number");
    cell = cells.item(row, 4);  
    cell.value("Transaction type");
    cell = cells.item(row, 5);
    cell.value("Amount");
    cell = cells.item(row, 6);  
    cell.value("Fixed asset group");

    //generate lines    while select assetTrans
    //The following loop will provide the data to be populated in each column    {
        row++;
        //add fixed asset trans data        cell = cells.item(row,1);      
        cell.value(assetTrans.Voucher);
        cell = cells.item(row,2);
        cell.value(assetTrans.TransDate);
        cell = cells.item(row,3);
        cell.value(assetTrans.AssetId);
        cell = cells.item(row,4);
        cell.value(enum2str(assetTrans.TransType));
        cell = cells.item(row,5);
        cell.value(assetTrans.AmountCur);
        cell = cells.item(row,6);
        cell.value(assetTrans.AssetGroup);

        // add dimensions        while select dimAttrSet
            where dimAttrSet.DimensionAttributeValueSet == assetTrans.DefaultDimension
        join Name from dimAttr
            where dimattr.RecId == dimAttrSet.DimensionAttribute
        {
            if (!dims.exists(dimAttr.Name)) // if dim column does not exists            {
               //add dimension column               dims.insert(dimAttr.Name, dimNum + 7);
               dimNum++;
               cell = cells.item(1, dims.lookup(dimAttr.Name));
               cell.value(dimAttr.Name);
            }
           //add dimension value           cell = cells.item(row, dims.lookup(dimAttr.Name));
           cell.value(dimAttrSet.DisplayValue);
        }
    }
    application.visible(true); // opens the excel worksheet}

Happy DAXing

................

How to Import/ Export Data from Microsoft Dynamics AX to CSV file

Exporting the Data from AX to CSV file

Hi....
here are the examples of how to deal import/ export data functionality in AX 2012

Import data from csv file into AX 2012

Here is an example of importing SubProgram financial dimension from CSV file into AX 2012...

static void ReadCsvFile(Args _args)

{

    #File

    IO  iO;

    DimensionFinancialTag   dimFinTag, dimFinTagOrig;

    FilenameOpen        filename = "C:\\Users\\v-vimsin\\Documents\\Vimal\\DIXF\\SubProgram.csv";//To assign file name

    Container           record;

    boolean first = true;

    

    iO = new CommaTextIo(filename,#IO_Read);

    if (! iO || iO.status() != IO_Status::Ok)

    {

        throw error("@SYS19358");

    }

    while (iO.status() == IO_Status::Ok)

    {

        record = iO.read();// To read file

        if (record)

        {

            if (first)  //To skip header

            {

                first = false;

            }

            else

            {              

                dimFinTag.Value                 = conpeek(record, 3);//To peek record

                dimFinTag.Description           = conpeek(record, 1); 

                dimFinTag.FinancialTagCategory  = conpeek(record, 2);

                

                select dimFinTagOrig 

                    where dimFinTagOrig.FinancialTagCategory == dimFinTag.FinancialTagCategory

                       && dimFinTagOrig.Value == dimFinTag.Value;

                if (!dimFinTagOrig)

                    dimFinTag.insert();

                

                info(strfmt('%1--%2',dimFinTag.Value,dimFinTag.Description));

            }

        }

    }

}

Export data from AX 2012 into csv file

Here is an example of exporting the AX data to a CSV file ...

Example- 1: 
static void ExportDataToCSV(Args _args)
{
    Query                             q;
    QueryBuildDataSource    qbds;
    QueryBuildRange            qbr;
    QueryRun                       qr;
    CommaIO                       commaIO;
    FileName                        fileName;
    InventTable                     inventTable;
    ;
   
    fileName       = WINAPI::getTempPath() + "ItemDetails" + ".csv";
    commaIO      = new CommaIO(fileName,'W');
   
    q                  = new Query();
    qbds             = q.addDataSource(tablenum(InventTable));
    qbr               = qbds.addRange(fieldnum(InventTable,ItemId));
   
    qr                = new QueryRun(q);
   
    commaIO.write("ItemId","Item Name","Item Type","Item GroupId","Dimension GroupId","Model GroupId");
    while( qr.next() )
    {
        inventTable = qr.get(tablenum(InventTable));
       
        commaIO.write(inventTable.ItemId,inventTable.ItemName,enum2str(inventTable.ItemType),inventTable.ItemGroupId,
                      inventTable.DimGroupId,inventTable.ModelGroupId);
    }
   
    WINAPI::shellExecute(fileName);
}

Example- 2: 
public static void main(Args _args)
{
    Commaio file;
    container line;
    InventTable inventTable;
    #define.filename("C:\\dpk_Items.csv")
    #File

    ;
    file = new Commaio(#filename , #io_write); or  file = new Commaio(#filename , 'W');
    //file.outFieldDelimiter(';');
    if( !file || file.status() != IO_Status::Ok)
    {
        throw error("File Cannot be opened");
    }
    while select inventTable
    {
        line = [inventTable.ItemId,inventTable.ItemName];
        file.writeExp(line);
    }
}

Note: As per this you can save the document in any format such as doc,txt,xls only.

Happy DAXing.....

Create/ Export Excel BOM Report Template from X++ in AX 2012

BOM Excel Template report:

Path: Product information management/common/release products

Select each grid record whose Production type is BOM or Formula as shown in below screen and click Lines in BOM Engineer action pane or Lines in Formula Engineer action pane

Add Export to excel button on version Group for exporting excel report template

Override Export to Excel click button which will call a new class through action menu item

void clicked()
{
     BOMVersion      BOMVersionlocal;
    str             menuItemStr;
    MenuFunction    menuFunction;
    Args            args = new args();
 
    super();
    //getFirst method gets all the selected records in the grid
    BOMVersionlocal = BOMVersion_ds.getFirst(1,true);
    args.record(BOMVersionlocal);
 
    menuItemStr = menuitemActionStr(SL_BOMExport);   
    menuFunction = new MenuFunction(menuItemStr, MenuItemType::Action);
    if (BOMVersionlocal.RecId != 0)
    {
       menuFunction.run(args);
    }
    else
    {
        warning('No record selected');
    }
 
}

Create a new Class which exporting BOM header and Lines into excel

public static void Main(Args _args)
{
    ItemId          itemid;
    BOMVersion      BOMVersion;
 
    BOMVersion = _args.record();
 
    itemid = BOMVersion.ItemId; 
 
    SL_BOMExport::exportBOM(itemid,BOMVersion.BOMId);
}

public static void exportBOM(ItemId _itemId, BOMId _bomId)
{ 
    BOMVersion               BOMVersion;
    BOM                      BOM;
    Description              personalNumbervalue;
 
    SysExcelApplication     application;
    SysExcelWorkBooks       workbooks;
    SysExcelWorkBook        workbook;
    SysExcelWorksheets      worksheets;
    sysExcelWorksheet       worksheet;
    SysExcelCells           cells;
    SysExcelCell            cell;
    SysExcelFont            SysExcelFont;
 
    int                     row = 1;
 
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();            //gets the workbook object
    workbook = workbooks.add();                     // creates a new workbook
    worksheets = workbook.worksheets();             //gets the worksheets object
    worksheet = worksheets.itemFromNum(1);          //Selects the first worksheet in the workbook to insert data
    cells = worksheet.cells();
    cells.range('A:A').numberFormat('@'); // numberFormat ‘@’ is to insert data as Text 
 
    //header
    worksheet.cells().item(1,1).value(CompanyInfo::Find().Name);
    worksheet.cells().item(1,1).font().bold(true);
 
    worksheet.cells().item(2,1).value("List of item formula / BOM");
    worksheet.cells().item(2,1).font().bold(true); 
 
    // bom header
    worksheet.cells().item(4,1).value("BOM");
    worksheet.cells().item(4,1).font().bold(true);
 
    worksheet.cells().item(5,1).value("Name");
    worksheet.cells().item(5,1).font().bold(true);
 
    worksheet.cells().item(6,1).value("Site");
    worksheet.cells().item(6,1).font().bold(true);
 
    worksheet.cells().item(4,4).value("From date");
    worksheet.cells().item(4,4).font().bold(true);
 
    worksheet.cells().item(5,4).value("To date");
    worksheet.cells().item(5,4).font().bold(true);
 
    worksheet.cells().item(6,4).value("From qty");
    worksheet.cells().item(6,4).font().bold(true);
 
    worksheet.cells().item(4,7).value("Active");
    worksheet.cells().item(4,7).font().bold(true);
 
    worksheet.cells().item(5,7).value("Approved by");
    worksheet.cells().item(5,7).font().bold(true);
 
    worksheet.cells().item(6,7).value("Approved");
    worksheet.cells().item(6,7).font().bold(true);
  
     select BOMVersion
           where BOMVersion.ItemId == _itemId
           && BOMVersion.BOMId     == _bomId;
 
        cell = cells.item(4,2);
        cell.value(BOMVersion.BOMId);
 
        cell = cells.item(5,2);
        cell.value(BOMVersion.Name);
 
        cell = cells.item(6,2);
        cell.value(InventDim::find(BOMVersion.InventDimId).InventSiteId);
 
        cell = cells.item(4,5);
        if (BOMVersion.FromDate != dateNull())
        {
            cell.value(BOMVersion.FromDate);
        }
        else
        {
            cell.value("");
        }
 
        cell = cells.item(5,5);
        if (BOMVersion.ToDate != dateNull())
        {
             cell.value(BOMVersion.ToDate);
        }
        else
        {
             cell.value("");
        }
        cell = cells.item(6,5);
        cell.value(BOMVersion.FromQty);
 
        cell = cells.item(4,8);
        cell.value(enum2Value(BOMVersion.Active));
 
        cell = cells.item(5,8);
        personalNumbervalue = HcmWorker::find(BOMVersion.Approver).name();
        cell.value(personalNumbervalue);
 
        cell = cells.item(6,8);
        cell.value(enum2Value(BOMVersion.Approved));
 
    row = 6 ;
    row+=2; 
 
     // header lines
 
       worksheet.cells().item(row,1).value("Item number");
       worksheet.cells().item(row,1).font().bold(true);
 
       worksheet.cells().item(row,2).value("Product name");
       worksheet.cells().item(row,2).font().bold(true);
 
       worksheet.cells().item(row,3).value("Configuration");
       worksheet.cells().item(row,3).font().bold(true);
 
       worksheet.cells().item(row,4).value("Quantity");
       worksheet.cells().item(row,4).font().bold(true);
 
       worksheet.cells().item(row,5).value("Unit");
       worksheet.cells().item(row,5).font().bold(true);
 
       worksheet.cells().item(row,6).value("Per series");
       worksheet.cells().item(row,6).font().bold(true);
 
       worksheet.cells().item(row,7).value("Size");
       worksheet.cells().item(row,7).font().bold(true);
 
       worksheet.cells().item(row,8).value("Color");
       worksheet.cells().item(row,8).font().bold(true);
 
       worksheet.cells().item(row,9).value("Style");
       worksheet.cells().item(row,9).font().bold(true);
  
     while select BOMVersion
           where BOMVersion.ItemId == _itemId
            &&   BOMVersion.BOMId  == _bomId
         join BOM
            where BOMVersion.BOMId == BOM.BOMId
 
    { 
            row++;
 
            cell = cells.item(row,1);
            cell.value(BOM.ItemId);
 
            cell = cells.item(row,2);
            cell.value(BOM.itemNameGrid());
 
            cell = cells.item(row,3);
            cell.value(InventDim::find(BOM.InventDimId).configId);
 
            cell = cells.item(row,4);
            cell.value(BOM.BOMQty);
 
            cell = cells.item(row,5);
            cell.value(BOM.UnitId);
 
            cell = cells.item(row,6);
            cell.value(BOM.BOMQtySerie);
 
            cell = cells.item(row,7);
            cell.value(InventDim::find(BOM.InventDimId).InventSizeId);
 
            cell = cells.item(row,8);
            cell.value(InventDim::find(BOM.InventDimId).InventColorId);
 
            cell = cells.item(row,9);
            cell.value(InventDim::find(BOM.InventDimId).InventStyleId); 
    }
 
    application.visible(true);  // opens the excel worksheet 
}

Happy DAXing

..........