[Solved] Sales vs Returns GI
I have been working on trying to create a GI that shows Return on sales. I am not really sure how do make this happen. I am pretty sure I have the tables I need, but in the results grid is where I run into a problem. I have to be able to total sales and returns for each customer and then divide them in the results grid correct?
I have created a mock-up using excel. I took a chance again with the GI and seems to be a lot more difficult than previously thought. I was trying to only use the Customers and AR Tran tables. I have attached an example of what I am looking for. I just don't think you can accomplish this in 2017 R2. Any Suggestions would be a big help.
I don't know that you are going to be able to get everything you are looking for above within a Generic Inquiry without creating a custom view in SQL. Doug Johnson wrote an article on how to do that once if you want to pursue that path.
Alternatively, you can do this in an Excel workbook or Power BI using a GI and OData.
You can create a GI that summarized sales by Item and shows total Sales and Total Returns. I just don't think you can get the percentage calculation in the GI since it relies on a calculation of totals of aggregated values. I don't think you can do that in a GI. (I'm sure someone will step in here and educate us both if there is.)
I have attached an XML file for a Summary AR Aged Trial Balance for your review. This is to provide an example of how to accomplish a couple of things you need to get most of the results you are looking for. The differences I can see that will need to be made are as follows:
1) You will need to use ARTran or SOLines instead of ARHistory to get your sales data. Use ARTran if you want to analyze actual revenue realized. Use SOLines if you want to analyze sales booked.
2) Group by Item ID or Item Name.
3) Use OrderType in the IIF statement on each Result row to determine if the line is associated with a sales (SO, IN, etc) or a return (RC, RM, etc)
I hope this helps.
Cannot do calculations on aggregates in GI. Also be careful when calculating multiple aggregates on joined tables that you don't get partial cartesian products in your aggregations.
I would use Power BI for this. IMHO, Power BI is an essential tool if you want to maximise your ROI with Acumatica. I am amazed at some of the reports I've built with Power BI. Learning curve is steep however.
I agree with Shawn, to do all this in Acumatica, you'll need a SQL View.
Or, as Royce pointed out, you could use Power BI. You could even just do it in Excel, but use the Power BI functionality by bringing in multiple Generic Inquiries and combining them in Excel. Excel Pivot Tables (with the new Power BI functionality) handle aggregate calculations really well.