Is it possible to sort by calculated value/field in report designer
Hello,
The requirement is as below.
Supposing I am designing a report with report designer, and this report would show all the items ordered in today, and group by itemID. I would like to show the lines sorting by orderQty of each line item descending. Here, the order qty of each line is a calculated sum value, I did not find a way to sort by the sum value.
Can someone give me a hand on this requirement?
I know there is a workround, if I export this report into excel, and it is very easy to sort by the order qty field. However, I still would like to know is it possible to just display the lines in the sequence of sum value directly.
Page 1 / 1
If the expression is too complex it may not work in the sorting field, but it’s possible to use expressions there.
Alternatively, you could create a SQL view which already contains this calculation and then use this to sort the report.
The SQL view would likely be the most efficient I would think
Hello @BenjaminCrisman
Sorry for the late reply. As I checked, it seems that I can put NO expression in the sorting conditions in report designer. Is there something I did wrong? Please help.
And if expression are not supported, can you give me a brief material on how to use customized view in Acumatica.
Hi Ray! You can actually type into the sorting field:
If you find that the expression is too complex to get recognized, then I would use a SQL view or custom DAC.
@BenjaminCrisman Just curious if you have any insight on this.
I’m attempting to sort by a formula field as well, but when I tab off of the field, or choose ‘Apply’, the Report Designer replaces my formula with a value of ‘DELETED’ .
I’m trying to write into the ‘Data Field’ box, or even copy/paste the formula into that box.
Field value for
Formula Expression: =CInt(Left(rInventoryItem.ItemClassID],3))
Goal: return a value of 200
When I display this formula as a field in the Report Designer, I get a value of 200.
But when I try to use the formula within the sort formula, Report Designer replaces my formula with a value of ‘DELETED’ .
Example:
Any ideas?
Thanks! Arline
Hello everyone… We are trying to do the same thing. I have the same issue as @AWelty24
@BenjaminCrisman - wondering if you might be able to clarify your answer? It seems that it is not accurate.
@xkylewrightx , @AWelty24 In order to be able to use the sorting formula it must also be grouped by the same expression:
Let me know if that helps,
thanks!
@BenjaminCrisman
Appreciate the reply -
That does work technically but is not the desired result - I do not want to group by this expression, I only want to sort by it.
Take for example, any of the default profitability reports such as ‘Sales Profitability by salesperson’. Do you have any recommendations for how to sort the order of the salesperson group, by a sum of net sales (sum(eARTran.NetSalesAmount])? In this example, we would not want to group by sum of net sales, only sort an existing group
Any help is much appreciated!!
Kyle
@xkylewrightx What you are describing is precisely the need to have the expression already in the DB via turning it into a SQL View.
Once the SQL view is implemented you will see your field (added to the appropriate table) and you can select the field for use.
I’ll re-link the article where Doug Johnson demonstrates this: