Microsoft Dynamics AX forum
Answered
Subscribe (0)
Report
Posted on by
OrangeRocks
700
Hi,
I have a statement that does not work as expected:
changecompany( "SAM" ) { while select sum( DebitMST ), sum ( CreditMST ), TransDate, AccountNum, PeriodCode from ledgerBalancesDim group by ledgerBalancesDim.TransDate where ledgerBalancesDim.AccountNum == "777777" && ledgerBalancesDim.PeriodCode == PeriodCode::Regular && ( ledgerBalancesDim.TransDate >= mkdate( 01,01,2014 ) && ledgerBalancesDim.TransDate <= mkdate( 31,12,2014 )) { Do my thing }}
I need to go through records in the view "LedgerBalancesDim" that grouped by date and get the sum of "DebitMST" and "CreditMST".
But the result set limits only to the requirement:
ledgerBalancesDim.TransDate >= mkdate( 01,01,2014 ) && ledgerBalancesDim.TransDate <= mkdate( 31,12,2014 )
and not to the AccountNum and PeriodCode.
What is wrong with the query?
Categories:
Development / Customization / SDK
All responses (
Answers (-
Suggested answer
Code Work
15on at
Like'; var react_Snippet = '
Like'; function HandleReactionRecord(e, targetRecordId) { var reaction_ControlId = "#"+e.id; try{ if(false == false){ window.location.href = "/signin?returnUrl="+window.location.pathname+window.location.search; }else{ $(reaction_ControlId).parent().block({ message: '', overlayCSS: { backgroundColor: '#e0dede', opacity:0.6} }); var entityParams = { isReactedByUser: $(reaction_ControlId).attr("user-reaction") == "true" ? true : false, reactionEntityName: $(reaction_ControlId).attr("reactionEntityName"), contactId: userId, recordId: targetRecordId, reactionType: "Like", reactionId: $(reaction_ControlId).attr("reaction-id"), siteId: "44c1922b-d231-4183-bae6-be148059122f", comment:$(reaction_ControlId).attr("commentflag") == 'true'?true:false }; LoadReactionControl(entityParams, successCallback, errorCallback); function successCallback(createdRecordId, res, status, xhr){ $(reaction_ControlId).attr("reaction-id", createdRecordId); if(createdRecordId != "" && createdRecordId != undefined){ $(reaction_ControlId).attr("user-reaction", true); $(reaction_ControlId).empty(); $(reaction_ControlId).html(reacted_Snippet); }else{ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } function errorCallback(res){ if(res.status == 500 && $(reaction_ControlId).attr("user-reaction") == "true"){ getRecord("mspwrplt_cmty_discussionreaction", '', targetRecordId, function(){}, errRetrieve); } $(reaction_ControlId).parent().unblock(); } function errRetrieve(res){ if(res.status == 404){ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).attr("reaction-id", ''); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } } }catch(err) { console.error(err.message); $(reaction_ControlId).parent().unblock(); } }(0) Report
RE: While Select statement with sum and group by
You can try this way in your while select loop
d365code.blogspot.com/.../return-sum-of-any-table-field-using-x.html
-
Suggested answer
Chaitanya Golla
17,225on at
Like'; var react_Snippet = '
Like'; function HandleReactionRecord(e, targetRecordId) { var reaction_ControlId = "#"+e.id; try{ if(false == false){ window.location.href = "/signin?returnUrl="+window.location.pathname+window.location.search; }else{ $(reaction_ControlId).parent().block({ message: '', overlayCSS: { backgroundColor: '#e0dede', opacity:0.6} }); var entityParams = { isReactedByUser: $(reaction_ControlId).attr("user-reaction") == "true" ? true : false, reactionEntityName: $(reaction_ControlId).attr("reactionEntityName"), contactId: userId, recordId: targetRecordId, reactionType: "Like", reactionId: $(reaction_ControlId).attr("reaction-id"), siteId: "44c1922b-d231-4183-bae6-be148059122f", comment:$(reaction_ControlId).attr("commentflag") == 'true'?true:false }; LoadReactionControl(entityParams, successCallback, errorCallback); function successCallback(createdRecordId, res, status, xhr){ $(reaction_ControlId).attr("reaction-id", createdRecordId); if(createdRecordId != "" && createdRecordId != undefined){ $(reaction_ControlId).attr("user-reaction", true); $(reaction_ControlId).empty(); $(reaction_ControlId).html(reacted_Snippet); }else{ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } function errorCallback(res){ if(res.status == 500 && $(reaction_ControlId).attr("user-reaction") == "true"){ getRecord("mspwrplt_cmty_discussionreaction", '', targetRecordId, function(){}, errRetrieve); } $(reaction_ControlId).parent().unblock(); } function errRetrieve(res){ if(res.status == 404){ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).attr("reaction-id", ''); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } } }catch(err) { console.error(err.message); $(reaction_ControlId).parent().unblock(); } }(0) Report
RE: While Select statement with sum and group by
Hi,
Please refer below links:
Aggregate Functions: Differences Between X++ and SQL [AX 2012]
msdn.microsoft.com/.../aa677419.aspx
Select Statement Examples [AX 2012]
-
OrangeRocks
700on at
Like'; var react_Snippet = '
Like'; function HandleReactionRecord(e, targetRecordId) { var reaction_ControlId = "#"+e.id; try{ if(false == false){ window.location.href = "/signin?returnUrl="+window.location.pathname+window.location.search; }else{ $(reaction_ControlId).parent().block({ message: '', overlayCSS: { backgroundColor: '#e0dede', opacity:0.6} }); var entityParams = { isReactedByUser: $(reaction_ControlId).attr("user-reaction") == "true" ? true : false, reactionEntityName: $(reaction_ControlId).attr("reactionEntityName"), contactId: userId, recordId: targetRecordId, reactionType: "Like", reactionId: $(reaction_ControlId).attr("reaction-id"), siteId: "44c1922b-d231-4183-bae6-be148059122f", comment:$(reaction_ControlId).attr("commentflag") == 'true'?true:false }; LoadReactionControl(entityParams, successCallback, errorCallback); function successCallback(createdRecordId, res, status, xhr){ $(reaction_ControlId).attr("reaction-id", createdRecordId); if(createdRecordId != "" && createdRecordId != undefined){ $(reaction_ControlId).attr("user-reaction", true); $(reaction_ControlId).empty(); $(reaction_ControlId).html(reacted_Snippet); }else{ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } function errorCallback(res){ if(res.status == 500 && $(reaction_ControlId).attr("user-reaction") == "true"){ getRecord("mspwrplt_cmty_discussionreaction", '', targetRecordId, function(){}, errRetrieve); } $(reaction_ControlId).parent().unblock(); } function errRetrieve(res){ if(res.status == 404){ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).attr("reaction-id", ''); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } } }catch(err) { console.error(err.message); $(reaction_ControlId).parent().unblock(); } }(0) Report
RE: While Select statement with sum and group by
Hi Sukrut and Dinkar, thank you, it worked.
However this behavior seems strange, because I needed to group my records after TransDate only. Can you point me to the information how AX tackles this "group by" command?
-
Verified answer
startax
1,845on at
Like'; var react_Snippet = '
Like'; function HandleReactionRecord(e, targetRecordId) { var reaction_ControlId = "#"+e.id; try{ if(false == false){ window.location.href = "/signin?returnUrl="+window.location.pathname+window.location.search; }else{ $(reaction_ControlId).parent().block({ message: '', overlayCSS: { backgroundColor: '#e0dede', opacity:0.6} }); var entityParams = { isReactedByUser: $(reaction_ControlId).attr("user-reaction") == "true" ? true : false, reactionEntityName: $(reaction_ControlId).attr("reactionEntityName"), contactId: userId, recordId: targetRecordId, reactionType: "Like", reactionId: $(reaction_ControlId).attr("reaction-id"), siteId: "44c1922b-d231-4183-bae6-be148059122f", comment:$(reaction_ControlId).attr("commentflag") == 'true'?true:false }; LoadReactionControl(entityParams, successCallback, errorCallback); function successCallback(createdRecordId, res, status, xhr){ $(reaction_ControlId).attr("reaction-id", createdRecordId); if(createdRecordId != "" && createdRecordId != undefined){ $(reaction_ControlId).attr("user-reaction", true); $(reaction_ControlId).empty(); $(reaction_ControlId).html(reacted_Snippet); }else{ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } function errorCallback(res){ if(res.status == 500 && $(reaction_ControlId).attr("user-reaction") == "true"){ getRecord("mspwrplt_cmty_discussionreaction", '', targetRecordId, function(){}, errRetrieve); } $(reaction_ControlId).parent().unblock(); } function errRetrieve(res){ if(res.status == 404){ $(reaction_ControlId).attr("user-reaction", false); $(reaction_ControlId).attr("reaction-id", ''); $(reaction_ControlId).empty(); $(reaction_ControlId).html(react_Snippet); } $(reaction_ControlId).parent().unblock(); } } }catch(err) { console.error(err.message); $(reaction_ControlId).parent().unblock(); } }(0) Report
RE: While Select statement with sum and group by
Hi Pam,
As suggested by @Sukrut use AccountNum, and PeriodCode also in your Group by.
