Solved

Passing a subtotal from a subreport back to a main report

  • 9 September 2022
  • 4 replies
  • 638 views
L
Rank
Userlevel 6
Badge +5

Hello,

I have an integration with a 3rd party EDI provider into Acumatica.

I need the ability to compare the incoming EDI (derived from non-standard Acumatica tables) total amount of the EDI sales order with the Acumatica sales order total amount.

 

Example:

EDI sales order total = $50 (data stored in custom tables)

Acumatica Sales Order total = $51

Print this record on my new custom report because the sales order TOTALS don’t match.

 

Discounts are involved which are causing the discrepancies I need to report.

 

  1. I have written a report that accurately reports the Sales Order number and the total from the EDI tables.

I intend to use this as a subreport.

The total is in a group footing as it sums up the individual lines of the order.

This part works.

 

  1. I can write a main report that will accurately calculate the total of the actual Acumatica sales order.

However, this too will have the order total in a footer as a subtotal.  (main report)

 

So I have a subtotal on a group in a subreport that I want to compare to a subtotal field on a main report.  I only want to report the sales order if there is a difference between the 2 sales order totals.

 

This seemed doable until I actually tried to put this together.

Any advice?

THANKS!

icon

Best answer by BenjaminCrisman 10 September 2022, 00:00

View original
If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

4 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

Hi @ltussing03 Have you considered using Variables for this?

You can create a variable to calculate the total in the Subreport and then call this into the main report to use it there.

The formatting would like $Subreport_Variable (case sensitive) where Subreport is the name of your subreport and Variable is the name of the Variable being called in.

Have you already tried using this method?

lauraj46
Rank
Userlevel 7
Badge +7

Hi @ltussing03 ,

Create a variable in your subreport with the total.  You can reference it in the main report using a formula like this: $Subreport_Variable .  You can use create visibility expression on the various sections in the main report to conditionally display the records you want.  

Also be aware that each section in the report as well as variables have a “Process Order” property in the Report Designer.  It sounds like you may need two passes through the data to get what you need into the visibility expressions. This post has more information on Process Order:

Hope this helps!

Laura 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
L
Rank
Userlevel 6
Badge +5

Thank you all!!!!

Happy Friday!!!

If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
E
Rank

I have a question related to using a variable from the subreport. Is it possible to use it as a filter in the schema builder of the main report? For example, only display results where $Subreport_Variable > $100. To take that one step further, compare $Subreport_Variable1 and $Subreport_Variable2 in the main report.

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