How to order a report by sum?

(0) Share

Report

Posted on by Community Member

Hello!

I'm new in AX, so I apologize if I can't express very well.

I need to build a report (in AX 2009) which shows how much was bought from each vendor in a certain period. Ex: if I want to see january data and if we have 2 purchase orders with the vendor X, one of them costing 1,000.00 and the other one costing 2,500.00, the report would show the name of vendor X in a column and the value 3,500.00 in another one.

It's ok until there, the report is getting all data I need. But I was asked to make the report be ordered by the values (like if, in a "standard" query", I added "Order By Sum(PurchTable.LineAmount)").

I put the field PurchLine.LineAmount (in which the sum was done) under the "Order By" node in AOT, to no avail... I'm looking for the solution for some time without success. Can somebody help me, please?

Thanks in advance!
Luiz Bezerra

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (0)

Suggested answer

Brandon Ahmad 2,465 User Group Leader on at

Like (0)

Report

You could use X++ for that or an SSRS expression, but why not make it simple. Highlight the table region of the report that contains the data. Go into the Properties window and set the sort property to the column with the name "sum." Choose ascending or descending order and go about your business.

If you need help understanding what those regions are then watch my my reporting tutorial --> look for the terms properties window, and data region.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the quick answer and for your youtube link, it'll be really helpul.

I didn't find any sort property anywhere in my report... I know I could do what I need using the Report Builder, but I wanted to do it by MorphX (specially because I have more freedom adding filters and other stuff this way).

Is there some way to do it? If it helps, I want to use the following query to make a report:

Select VT.Name, Sum(LineAmount)

From VendTable VT Inner Join PurchTable PT On VT.AccountNum = PT.OrderAccount Inner Join PurchLine PL On PT.PurchID = PL.PurchID

Group By VT.Name

Order By Sum(LineAmount) Desc

Was this reply helpful? Yes No
Suggested answer

Brandon Ahmad 2,465 User Group Leader on at

Like (0)

Report

H5430.SortProperties.png"/resized-image.ashx/__size/550x0/__key/CommunityServer-Discussions-Components-Files/33/7450.sortOption.png" border="0" />

3. Then go to the properties Window. Give the sort a name. Fill out the sort by(that is what I was talking about earlier but I used report builder in my videos)and choose the expression button. Sort by the Expression =Sum(Fields!LineAmount.value)... [Note there area about 10 other ways to do this].. Also note, depending your generated AX query the column (aka field) that goes into the sort by expression statement may be different.

I've now surpassed my ability to stay awake but this should get you taken care of.. If you don't see the properties window, hit view --> properties window to select it. Take Care Luiz..

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I'm curious if you found this solution or not. It seems the only solution I can come up with is to print the report to Excel and have the user manually sort there. While that works, it's obviously not ideal.

The problem with Brandon's answer is that AX 2009 MorphX Reports do not have such layouts -- either those are SSRS reports (which I have a feeling is the case, as identified by 'DataSets' in the screenshot) or AX 2012 MorphX. Either way, that functionality does not exist with 2009 MorphX.

I have attempted doing an Order By on the aggregate field, but it seems that as you are building the query, the Order By will take precedence over the aggregate function In other words, if I have Count(RecId) and Order By RecId, it will order by the RecId of the table record, THEN find out what the count should be. This causes it to have a very strange sorting order when it finally prints.

Was this reply helpful? Yes No
Ameen1623 on at

Like (0)

Report

hi guys,

i also have same scenario but my questions is how to display the sum of total valus by column wise... in Morphx report ax 2009.

can any one help me how to do this am new to ax 2009 morphx report.

Was this reply helpful? Yes No