Summarized values in AX form

Users sometimes want to see both individual transactions in a grid of a Dynamics AX form, and some summarized values, such as the total amount or the number of lines (often above or below the grid). Iterating through the whole datasource and getting values one by one isn’t efficient, especially if the query returns many rows. A much better solution is taking the query, modifying it to use an aggregation function (such as SUM() or COUNT()) and sending a single, efficient request to database.

My example assumes that I have a form showing customer invoice lines and I want to calculate the total amount of all lines fulfilling current filters (and show it in a separate control).


public void updateTotal()
{
    // Copy the query
    Query query = new Query(CustInvoiceTrans_ds.queryRun().query());

    QueryBuildDataSource qbds = query.dataSourceTable(tableNum(CustInvoiceTrans));
    QueryRun qr;
    CustInvoiceTrans summedTrans;

    // Sum LineAmountMst
    qbds.addSelectionField(fieldNum(CustInvoiceTrans, LineAmountMst), SelectionField::Sum);

    qr = new QueryRun(query);
    // Run the query
    qr.next();

    // Get the data
    summedTrans = qr.get(tableNum(CustInvoiceTrans));

    // Set the new sum to the control
    Total.realValue(summedTrans.LineAmountMST);
}

The first statement is extremely important, because it defines which query you want to use. I take CustInvoiceTrans_ds.queryRun().query(), because I want to respect filters defined by users. If it wasn’t the case, I would use CustInvoiceTrans_ds.query(). Both scenarios are valid; the choice depends on your functional requirements. It’s also worth noting that I modified a copy of the query. If I modified the query used by the datasource, I would actually get the summed result in my grid, which wouldn’t make sense. Then I just have to call the method every time when the datasource query executes. public void


executeQuery()
{
super();
element.updateTotal();
}
<pre>

Ref:http://dev.goshoom.net/en/2015/11/summarized-values-in-ax-form/

 

Summarization

Import Dimensions into AX 2012

Its quite common for developer to get the requirement for Importing Default Dimensions into AX 2012,

Below you an find the Dimensions import for Worker in Ax 2012:

Class_WorkerDimImport

Below is the code snippet for the dimensions import for Fixed Assets:

class Mzk_FixedAssetDimensionImport
{
}

Below method will get the dimension recId for the dimensions which we have passed:

private DimensionDefault getNewDefaultDimension(str businessUnit = &amp;quot;&amp;quot; ,
                                                str costcenter = &amp;quot;&amp;quot;,
                                                str department = &amp;quot;&amp;quot;)
{
    // Dimensions
    #define.businessUnit(&amp;quot;BusinessUnit&amp;quot;)
    #define.costcenter(&amp;quot;Costcenter&amp;quot;)
    #define.department(&amp;quot;Department&amp;quot;)

    Struct                                struct = new Struct();
    container                             ledgerDimension;
    DimensionDefault                      DimensionDefault;
    int i = 0;

    if(businessUnit)
            struct.add(#businessUnit,businessUnit);
    if(costcenter)
        struct.add(#costcenter,costcenter);
    if(department)
        struct.add(#department,department);

    ledgerDimension += struct.fields();

    if(businessUnit)
    {
        i++;
        ledgerDimension += struct.fieldName(i);
        ledgerDimension += struct.valueIndex(i);
    }
    if(costcenter)
    {
        i++;
        ledgerDimension += struct.fieldName(i);
        ledgerDimension += struct.valueIndex(i);
    }
    if(department)
    {
        i++;
        ledgerDimension += struct.fieldName(i);
        ledgerDimension += struct.valueIndex(i);
    }

    DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

    return DimensionDefault;

}

Below method isto import Dimensions from Excel sheet:

private boolean importFromExcel(Filename inputFile)
{
    SysExcelApplication         application;
    SysExcelWorkbooks           workbooks;
    SysExcelWorkbook            workbook;
    SysExcelWorksheets          worksheets;
    SysExcelWorksheet           worksheet;
    SysExcelCells               cells;
    AssetBookId                 assetBookId;
    AssetId                     assetId = &amp;quot;&amp;quot;;
    AssetBook                   assetBook;
    int row = 1;
    COMVariantType              type;
    str costcenter,department,businessunit;
    int i;

    try
    {
        application = SysExcelApplication::construct();
        workbooks = application.workbooks();

        try
        {
            workbooks.open(inputFile,0,true);
        }
        catch (Exception::Error)
        {
            throw Exception::Error;
        }

        workbook = workbooks.item(1);
        worksheets = workbook.worksheets();
        worksheet = worksheets.itemFromNum(1);
        cells = worksheet.cells();

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

        while (type != COMVariantType::VT_EMPTY)
        {
            row++;
            assetId = &amp;quot;&amp;quot;;
            assetBookId = &amp;quot;&amp;quot;;
            assetId = cells.item(row,1).value().bStr();
            assetBookId = cells.item(row,2).value().bStr();
            businessunit= cells.item(row,3).value().bStr();
            costcenter = (cells.item(row,4).value().bStr());
            department = (cells.item(row,5).value().bstr());

            ttsBegin;
            select forUpdate assetBook
                        where assetBook.AssetId == assetId
                                          &amp;amp;&amp;amp; assetBook.BookId  == assetBookId;
            if(assetBook.RecId)
            {
                if(businessunit || costcenter || department)
                {
                    assetBook.DefaultDimension = this.getNewDefaultDimension(businessunit,costcenter,department);
                    assetBook.update();
                }
                else
                {
                    info(strFmt(&amp;quot;Please check the values for AssetId:%1,BookId:%2&amp;quot;,assetId,assetBookId));
                }
            }
            else
            {
                info(strFmt(&amp;quot;Please check the values for AssetId:%1,BookId:%2&amp;quot;,assetId,assetBookId));
            }
            ttsCommit;

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

        application.quit();

        return true;
    }
    catch
    {
        return false;
    }
}

Main method code starts executing from here

public static void main(Args _args)
{
    Dialog                  dialog;
    DialogField             dialogFileName;
    SysOperationProgress    simpleProgress;
    Filename                filename;
    Container               filterCriteria;
    FileIOPermission        permission;
    Mzk_FixedAssetDimensionImport         Dimimport = new Mzk_FixedAssetDimensionImport();
    #File
    #avifiles

    dialog = new Dialog('Select Excel File');
    dialogFileName = dialog.addField(&amp;quot;Filenameopen&amp;quot;, &amp;quot;File Name&amp;quot;);
    filterCriteria = ['*.xls'];
    filterCriteria = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();

    if (dialog.run())
    {
        filename = dialogFileName.value();
        if(!filename)
            info('Filename must be filled');
        else
           Dimimport.importFromExcel(filename);
    }
    info('Done');

}