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

14 Comments

  1. Hi Martin
    Great solution and implemented without issue on my production overview screen – thanks!
    Do you have any tips how I could employ a similar method to sum the values of a field which have been calculated using a data method? (ie I want to sum a calculated weight). Thanks again.

    • You obviously can’t call X++ functions from SQL aggregation function. You could reimplement the method as a computed column, or maybe you should change your data model to better support your requirements (namely by saving the calculated value on the record).

      • Thanks Martin. Looking into this further it appears the column I want to sum uses a display method. Presumably then your solution is not going to work unless I change the data model to save the value? Thanks again.

        • “My solution” is using SQL. That should say everything.

          For SQL queries (whether we’re talking about SUM(), simple field selections or anything else), you need something that exists inside SQL server (which obviously isn’t the case of X++ methods). In my previous reply, I gave you two suggestions how to convert your code (you seem to miss the option with computed columns).

          If you don’t want to use either and you want to continue with X++ code, you obviously can’t use SQL for querying it. You would have to do the calculation in application layer; which would be much slower.

          • Thanks Martin, all good. I appreciate your time and advice. I’ll school myself up on computed columns. Thanks again.

  2. What should be changed to get the total records the Datasource executequery fetches?
    try this with Selectionfield::Count but get the error: not a single-group group function
    when starting the form.

    • Just changing Sum to Count and the field to a numeric one (typically RecId) works.
      The error suggests that the aggregation can’t be added to your particular query, therefore it would help to know your query.
      You may want to ask in a discussion forum, which is better suited for inserting long code snippets and things like that.

Leave a Reply

Your email address will not be published.