Solved

How To Use SUM of Total in other sections of the report

  • 5 July 2022
  • 7 replies
  • 1360 views
C
Rank
Userlevel 4
Badge

Hi All,

I am looking to find a way to use the total sales amount at the bottom of Daily Sales Profitability report within the line to calculate what percent is the customer contributing to the total sales for a period of time.

Ultimately I am looking to achieve the below:
 

Customer              Net Sales    % to Total

ABC Company      100,000         10%

                            Total Sales

                              1,000,000


How would this be done? Or is there a report that already does comparison of customer to the total sales?

Kind regards,

Calvin  

icon

Best answer by lauraj46 1 August 2022, 04:06

View original

7 replies

bryanb39
Rank
Userlevel 6
Badge +5

I haven’t done it yet, but I would suspect you’d want to create a new relationship with a child alias so the grouping will include all sales that you want, class for example.  Then you can divide sum of NetAmount from ARTran by sum of NetAmount from ARTranByClass.

https://www.fiverr.com/acudatica
C
Rank
Userlevel 4
Badge

Hi @bryanb39,

Thank you for the suggestion. I am not too advance in Acumatica. Would you be able to show me how tto create a child alias or variable to sum the NetSales amount?

Thank you,

Calvin

lauraj46
Rank
Userlevel 7
Badge +7

Hi @Calvin ,

I believe you should be able to accomplish this by using variables.  The Acumatica report designer allows for two passes through the data.  Using the ProcessOrder property on variables and other controls you can control when the calculations are processed.  The trick is to calculate the grand total in a variable on the first data pass (WhileRead) and the percentage calculation on the second pass (WhilePrint).

Take a look at the Inventory Valuation report (IN615500) for an example.

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*.
C
Rank
Userlevel 4
Badge

Hi @lauraj46,

Thank you for your suggestion. I am able to somehow get one field working which gives me the result I mention above. 

My current problem is the report will also breakdown on customer level as well, meaning what is the total contribution is the customer to the total sales. I try to put the same formula but it will either be reading 0 or blank. I assume is something in the grouping but I am not able to figure it out. Would you have any idea why it is able to pull the total in one group but not another?

Below will be a screenshot of what it looks like:

Kind regards,

Calvin

1 Attachment
lauraj46
Rank
Userlevel 7
Badge +7

Hi @Calvin ,

Make sure that the ProcessOrder is set to Always on both the section and the field where you are using the $Total variable.  In your report the Footer of group2 was set to WhileRead.

See adjusted report attached.

 

Hope this helps!

Laura

1 Attachment
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*.
D
Rank
Userlevel 3
Badge

@lauraj46 

 

This didn’t work with a subreport variable and am wondering if it ever will. The subreport footer section is set to ALWAYS and it displays perfectly in the report footer but not in the header.  I even tried assigning it to a header variable in the footer section and put all fields and variables to ALWAYS but it did not work.

Is it possible to display a subreport variable total in the report header at all, no matter where the subreport lies?

I solved the issue by creating another subreport and putting it in the header, but am wondering if some combination of ALWAYS / WHILE READ /WHILE PRINT and assignment would also work.  Thx.

lauraj46
Rank
Userlevel 7
Badge +7

Hi @dgross54 ,

I’m not sure if this is possible or not.  Glad to hear that you found a workaround.

If you care to attach your report I’ll take a look for future reference.

 

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*.

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