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();
}