Is it possible to sort by calculated value/field in report designer

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.

@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 [InventoryItem.ItemClassID] (as an example): 200FENCE

Formula Expression: =CInt(Left([InventoryItem.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([ARTran.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:

https://www.acumatica.com/blog/technical-tuesday-report-from-sql-view/