Solved

Report Designer Totals

  • 27 September 2023
  • 8 replies
  • 173 views
K2a
Rank
Userlevel 4
Badge +1
  • K2a
  • Jr Varsity III
  • 92 replies

Ok, I'm sure I'm missing something super simple. 
i have a report that I want to summarize open/unpaid balances for a customer where certain Sales Orders are Flagged (with a manual) Checkbox. 

I have the schema and all data flowing properly in the report EXCEPT when it comes to totaling the report.   I assumed that I could accomplish this with grouping, but that doesn't seem to work.

In the report you can see i have 2 Sales Orders.  The report should calculate the total unpaid, but it is circling through and adding up the unpaid balance from ALL the lines on the SO.  

I thought if i put the 'totals' in a group and grouped by Customer AcctCD that it would sum at that level but I"m obviously missing something: 

 

icon

Best answer by K2a 29 September 2023, 17:29

View original
1 Attachment

8 replies

darylbowman
Rank
Badge +11

Do you know if your way of calculating the order quantity is different than Acumatica does it? If you could eliminate SOLine from your joins, the issue would go away. SOOrder has a field called OrderQty (also visible on the Sales Order screen). Could you use that and eliminate the SOLine join?

 

Alternatively, have you tried moving the total fields from groupFooterSection3 to groupFooterSection2?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
K2a
Rank
Userlevel 4
Badge +1

Do you know if your way of calculating the order quantity is different than Acumatica does it? If you could eliminate SOLine from your joins, the issue would go away. SOOrder has a field called OrderQty (also visible on the Sales Order screen). Could you use that and eliminate the SOLine join?

 

Alternatively, have you tried moving the total fields from groupFooterSection3 to groupFooterSection2?

Daryl,

It’s a good thought, but the challenge I run into is that the QTY on a sales order contains both Stock and Non-Stock items (i.e. a Cup and the Decoration Service).  If there were 10 Cups, the total qty would be 20 (10 cups, 10 decoration fees), so they QTY is essentially duplicated unless I have the “IsStkItem” to leverage as a limiter for the QTY and that is only at the line level. 

 

 

darylbowman
Rank
Badge +11

That’s why I asked 🙂

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
K2a
Rank
Userlevel 4
Badge +1

That’s why I asked 🙂

I don’t understand why the “group” function doesn’t work as I would expect, which is to grab distinct value at the ORDER level (my grouping) and sum that?!   

You got me thinking though.  Say this is my scenario :

Order 1, 5 lines, each line has an ‘unpaidbalance’ of 100 due to the row proliferation

Order 2, 3 lines each line has an ‘unpaidbalance’ of 50 due to the row proliferation

So the SUM i’m getting to is (100 x 5) and (50 x 3) or 650. 

I can’t divide 650 by the count of rows (8) because that gives me 81.25.  
i need to take 500/5 + 150/3  

How can I do THAT!?

darylbowman
Rank
Badge +11

The group by doesn't work because you're grouping by SOOrder.RefNbr and each line from SOLine joined to SOOrder has that RefNbr.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
darylbowman
Rank
Badge +11

How can I do THAT!?

 

This could possibly work:

=Sum(SOOrder_unpaidBalance/Count([SOLine.LineNbr]))

This is not necessarily actual field names. I don't have the DAC in front of me.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
K
Rank
Userlevel 5
Badge

If this is just regarding the customer total, and the group2 totals (838.65 + 3267.34) are correct, I would use a variable which increments the header values on each group2 and resets at group1 to keep track of the actual customer total.

K2a
Rank
Userlevel 4
Badge +1

Thanks Everyone for the suggestions.  @darylbowman i did try that but it did not work as expected. 

 

I was able to create a sub report that totaled at the customer level and passed the parameters that way. 

 

took a month and a day to get all the fields to line up...but it seems to be working. 

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