I am trying to build a GI that has an actual percentage margin (Sales-Costs/Sales), instead of an average. This report needs to be aggregated by day (to show the actual margin on a dashboard that is updated every time the page is refreshed)
What the query does is if the AR transaction is not released, it will pull in the costs from the SO table to work out the margin.
The GI currently looks at the ARTran and SOLine tables
I can easily get the net sales and the costs , and can work out a margin based on actual costs from the ARtran table, however I cannot seem to get this to work with unreleased AR transactions that need to pull in costs from the SO table.
Any ideas?
I have tried this formula
=(IIF([ARTran.TranAmt] > abs([ARTran.Cost]), ([ARTran.TranAmt]) * IIf( [ARTran.DrCr]='D', -1, 1 ) - (IIf( [ARInvoice.Released]=True, [ARTran.Cost], IIf( [ARTran.DrCr]='D', -1, 1 )*[SOLine.ExtCost])),(([ARTran.TranAmt]) * IIf( [ARTran.DrCr]='D', -1, 1 ) - (IIf( [ARInvoice.Released]=True, [ARTran.Cost], IIf( [ARTran.DrCr]='D', -1, 1 )*[SOLine.ExtCost]) ) )* -1)/ [ARTran.TranAmt] )*100
this one works if i am just looking at the AR Table
=100*Sum(IIF([ARTran.TranType] ='CRM', -1,1)*([ARTran.TranAmt]-[ARTran.TranCost]))/Sum(IIF([ARTran.TranType] ='CRM', -1,1)*[ARTran.TranAmt])
my cost calculation
=IIf( [ARInvoice.Released]=True, [ARTran.Cost], IIf( [ARTran.DrCr]='D', -1, 1 )*[SOLine.ExtCost])
my sales calculation
= [ARTran.TranAmt]*IIF([ARTran.TranType] ='CRM', -1,1)
