Solved

How to numerically sort subaccounts on ARM report

  • 15 September 2023
  • 3 replies
  • 58 views
A
Rank

I have report where I note the account and it’s balance on one row, expand by subaccount on the next 

row set example

This is working great for obtaining the subaccount activity that makes up the account balance. However when printing the report, I would like to sort the subaccount string numerically. Currently, segment one is sorting largest to smallest while segment 2 is smallest to largest. Does anyone know of a custom expression for either the column set or row set to be able to sort the string smallest to largest?

 

example of report output (exported to Excel), same behavior in the Acumatica screen and when exporting to PDF
​​​​​

Thanks for any and all help on this!

icon

Best answer by Laura02 19 September 2023, 11:45

View original

3 replies

M
Rank
Userlevel 4
Badge

Have you tried using the Sort() Function on the Row Set, in your example you will use SORT(‘0300’,’0345’,’B’):

 

Marilize van Zyl
Laura02
Rank
Badge +18

Hello,

I think @marilizevanzyl is close except for 2 changes

  1. To Sort smallest to largest, you want to sort Ascending, not Descending.  (Remove D from formula.)
  2. Your row number where the subaccounts are expanded is 1145.
  3. Subaccount is located in the first column, Description column of the report, yes? if so, The first column on the report is column A and therefore your Sort formula is =SORT(‘1145’,’1145’,’A’)

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
A
Rank

Thank you both!  Yes, Laura, I simplified down to your formula for Ascending order and this works beautifully. I looked at D also but that is descending and I needed Ascending. And, sorted on Column A because that is the column the accounts, subaccounts and description live in. Here is the added row for this example.

 

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