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

20 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.

  3. Hi Martin, I am new to AX., I have 3 grids in a same form and i want to calculate total of amount field in each grid. For this i have also added control below for each grid. Now my problem is where i should write the above code to perform separate sum operation. I have tried to write the code on form level and override the executeQuery() on datasource level but its not working properly. The total gets calculated but not showing at the same time. for this i have to refresh the form . And if set the condition like,, the total sum should bot be greater than 100 or any value, the info arrived automatically when i open the form. Please help me out.

  4. Hello Martin,

    Really nice post. I came across this post while investigating an issue for one of the clients who recently went from AX 2012 R2 CU7 to AX 2012 R2 CU9. Let me try to explain.

    Scenario :
    *********
    The client is facing an issue after going to CU9 i.e. prior to the update, while posting an arrival journal, if they have some quantity lets say 2 and they split it into two lines and assign different locations for both the lines, then post it. After posting, when they open the post receipt screen, under lines tab, they would see a consolidated line (i.e. the lines that they splitted before posting now appearing as single line with qty 2 instead of two seperate lines with qty 1). However in CU9 version, they would see two different lines of quantity 1.

    I tried looking for differences within PurchEditLine forms of both the envrionments (CU7 and CU9), no siginficant difference. Actually tried comparing code within layers of both the environment, debugging both the envrionments simultaneously i.e. for both puchEditLine form and code/objects invloved in posting item arrival journal.

    one work around is when in CU9 version, if the user change Quantity parameter to something else and then change it back to Registered qty , the lines on the grid start appearing as single consolidated line (as it appears in CU7) version on form start. I tried debugging that code as well (i.e. Modified filed for SpecQty on datasource purchParmUpdate) however nothing significantly different there as well.

    since all the code seems same to me till this point in my investigation (i.e. CU7 and CU9), i tried building AOT, generating full CIL and synching database in CU9 envrionment, however the issue persist. while i am still trying to further investigate using trace parser and comparing the results from both the envrionments.

    While going thru your post, i thought to create a method myself and call it from executeQuery (leveraging the same approach / code as you have followed in your post). but not getting the result. definitly i am not doing it right. I am writting the following cod on a custom method on the form PurchEditLines and calling it after super from within executeQuery of the PurchParmLine dataSource.

    Copy the query
    Query query = new Query(PurchParmLine_ds.queryRun().query());

    QueryBuildDataSource qbds = query.dataSourceTable(tableNum(PurchParmLine));
    QueryRun qr;
    PurchParmLine summedUpGridRows;

    // Sum Quantity field on PurchParmLine (PurchParmLin.ReceiveNow) and adding group by clause
    qbds.addSelectionField(fieldNum(PurchParmLine, ReceiveNow), SelectionField::Sum);
    qbds.addGroupByField(fieldNum(PurchParmLine,ItemId));
    qbds.addGroupByField(fieldNum(PurchParmLine,OrigPurchId));
    qbds.addGroupByField(fieldNum(PurchParmLine,ParmId));

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

    // Get the data
    summedUpGridRows = qr.get(tableNum(PurchParmLine));

    // Setting the form datasource with updated data
    PurchParmLine_ds.setRecord(summedUpGridRows);

    //re-reading and refreshing datasource.

    PurchParmLine_ds.refresh();

    however i still see two seprate rows instead of 1 consolidated with qty 2.

    Can a advise anything like if i should take any other approach? or is there a hotfix already available for such issue. i was not able to search that due to some issue with my account and not able to get into partner source website.

    any help would be highly appreciated and i am sure would be a great learning for me from you.

    if i missed any detail i would provide more if you need. even the trace parser results.

    Regards
    Farhan

  5. Hi,

    I’m very new to AX 2012 programming so I apologize if these are silly questions. This post has been very helpful to me but can you explain what ‘Total’ refers to in your code? Is it the name of the control?

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

    Also, where should the method (updateTotal) be written? Is it on the form control? On the datasource table?

    Thank you so much.

    • Yes, Total is a RealEdit control. The comment says that the code “Sets the new sum to the control”. Total is the control, realValue() is a method setting the value and summedTrans.LineAmountMST is the value.
      updateTotal() is called in executeQuery(), as you can see at the end of the blog post.

Leave a Reply

Your email address will not be published.