Questions [Solved] Sales vs Returns GI

Casey,

Can you mock up what you are trying to accomplish in an Excel spreadsheet so we can make sure the answer we provide is on point?

I'm sure you know exactly what you are looking for. I'm just not sure what you want in the result grid.

Thanks!

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. 

Hey Casey,

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. 

Right-click to Download

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.

Hey Casey,

I learned this really cool technique from @wyatt-erp:
https://www.augforums.com/forums/acumatica-generic-inquiries/gi-with-2-or-more-different-values

It uses an Attribute to allow you to combine multiple disparate data sets. I think the technique would work for your desired report screenshot above.