Solved

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

  • 30 September 2020
  • 8 replies
  • 865 views
R
Rank
Userlevel 6
Badge +5

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.

icon

Best answer by BenjaminCrisman 12 October 2020, 16:25

View original

8 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

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

R
Rank
Userlevel 6
Badge +5

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
Rank
Userlevel 7
Badge +4

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.

Here’s an article Doug made on making a SQL view:

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

AWelty24
Rank
Userlevel 3
Badge

@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

xkylewrightx
Rank
Userlevel 5
Badge

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. 

Kyle Wright CSW, Inc. | www.cswgraphics.com
BenjaminCrisman
Rank
Userlevel 7
Badge +4

@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!

xkylewrightx
Rank
Userlevel 5
Badge

@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

Kyle Wright CSW, Inc. | www.cswgraphics.com
BenjaminCrisman
Rank
Userlevel 7
Badge +4

@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/

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved