How To Use SUM of Total in other sections of the report
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
Page 1 / 1
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.
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
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
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
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
@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.
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.