Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / OrderBy and GroupBy in...
Microsoft Dynamics AX (Archived)

OrderBy and GroupBy in ax2012 queries

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello!

I was wondering, how to write in x++ query, in order for it to group by Item and then Order ascending or descending, here is the code - and it groups by both Item and Quantity in given case, I have tried to change GroupBy to OrderBy with no positive effect.

tmpQbds.orderMode(OrderMode::GroupBy);
tmpQbds.addSortField(fieldNum(F7_ExcelReport, ItemNumber));

tmpQbds.orderMode(OrderMode::GroupBy);
if(exportOrder == ExportOrder::Amount)
{
     if(sortingType == AscendingDescending::Ascending)
     {
            tmpQbds.addSortField(fieldNum(F7_ExcelReport, TotalAmount), SortOrder::Ascending);
     }
     else
      { 
             tmpQbds.addSortField(fieldNum(F7_ExcelReport, TotalAmount), SortOrder::Descending);
       }
}
else
{
      if(sortingType == AscendingDescending::Ascending)
      {
            tmpQbds.addSortField(fieldNum(F7_ExcelReport, TotalQuantity), SortOrder::Ascending);
      }
      else
      {
           tmpQbds.addSortField(fieldNum(F7_ExcelReport, TotalQuantity), SortOrder::Descending);
      }
}

After fallow some aggregate functions, for certain fields. If neede,  I am able to post more code.

*This post is locked for comments

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 231,305 Most Valuable Professional on at
    RE: OrderBy and GroupBy in ax2012 queries

    Unfortunately your code doesn't do anything like that.

    Create a view based on your table. Add grouping by ItemNumber and all fields you need, with appropriate aggregations. Let's say that the sum of quantities is called SumOfQty.

    What you have to do then? Use the view in a query (in the same way as if it was a table) and sort it by SumOfQty field.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: OrderBy and GroupBy in ax2012 queries

    Yes, exactly what I try to achieve.

  • Martin Dráb Profile Picture
    Martin Dráb 231,305 Most Valuable Professional on at
    RE: OrderBy and GroupBy in ax2012 queries

    If you don't group by Qty, you must use some aggregation of Qty (otherwise it would be empty). But the aggregated value will be the same regardless of how you order quantities. Therefore I assume that you would like to sort by the aggregated value, such as SUM(Qty).

    Is it what you're trying to achieve?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: OrderBy and GroupBy in ax2012 queries

    Well it does not change the situation, I need to group by Item ID and sort by Qty, not group By ItemId and Qty.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: OrderBy and GroupBy in ax2012 queries

    You can also use QueryBuildDataSource.addGroupByField method

    msdn.microsoft.com/.../querybuilddatasource.addgroupbyfield.aspx

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,494 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans