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(); }
Hi Martin, Really Thanks…
İ tried your code… its working but my result is 0 (zero)… i must control it again… 🙁
Hi. This is very helpful, thanks for sharing your knowledge on dynamics ax. This solution is great and makes me think that I still have a lot to learn.
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.
Thanks Martin
It helped me lot to put list page total in control really appreciates your great contributions
Afridi
Excellent post Martin. Keep writing these special scenarios. It helped a lot
Excellent post Martin. Keep writing these special scenarios. It helped a lot
Thank You Martin
Hi Martin;
I want to get the display method values summary sum. How can I do that?
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.
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.
Have you tried it with a single datasource (and single grid) first? Are you using unbound controls?
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
This is now discussed in the following thread in Dynamics User Group forum: Post Receipt grid showing split lines rather one consolidated lines.
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.
After the code this message shows to me. Method (“methodname”) is not an edit/display method.
It seems that you’re trying to use a display method somewhere but it’s not declared correctly. Note that my solution above doesn’t use any display method, therefore you’re doing something else than what I did.
Yes, It’s my mistake Martin, I use your code and give the method name on Data Method in the Real Edit field on design that’s why this message shows.
Hello Martin,
I want to make the sum of lines when select them and the default value of default selected line in the field. How to do it?
Many thanks
When you want only the selected lines, not all lines returned by the query, the approach discussed in this blog post won’t help.
You must iterate selected lines one by one (MultiSelectionHelper class will help you with that) and add values together by yourself.
Hi Martin,
Thanks Martin for answering me, can you clarify to me how to do it. do i use simply MultiSelectionHelper class in the same methods TotalUpdate() and excuteQuery() or do i try other methods.
Thanks again.
If I understand your problem correctly, you don’t want to do anything on query execution, so it’s not clear to me what you’re trying to achieve in executeQuery().
This is not a good place for such a discussion. Please create a new thread in a discussion forum (at community.dynamics.com or dynamicsuser.net) and explain your problem in detail there. There you can also easily show your source code, attach images etc., and you’ll reach many more people than just me.