I did a Gross Margin in Access trying to match the Vantage report. The
new Progress report in Ver 8.0 isn't linear (data going horizontal and
vertical in the same record) so can't cut and paste into Excel anymore.
I filter the dates through a form but here's the rest my SQL:
SELECT DISTINCT PUB_ShipDtl.JobNum, PUB_InvcDtl.InvoiceNum,
PUB_InvcHead.InvoiceDate, PUB_InvcDtl.PartNum, PUB_InvcDtl.OurShipQty AS
[Ship Qty], Round([ourshipqty]*[MtlUnitCost],2) AS [Material Cost],
Round([LbrUnitCost]*[ourshipqty],2) AS [Labor Cost],
Round([ourshipqty]*[BurUnitCost],2) AS [Burden Cost],
Round([ourshipqty]*[MtlBurUnitCost],2) AS [Mtrl Burden],
Round([ourshipqty]*[SubUnitCost],2) AS [Subcontract Cost],
PUB_Part.PartDescription, PUB_Part.ProdCode, PUB_InvcDtl.UnitPrice,
[mtlunitcost]+[lbrunitcost]+[burunitcost]+[mtlburunitcost]+[subunitcost]
AS [Unit Cost], Round([ourshipqty]*pub_invcdtl.unitprice,2) AS [Total
Price], Round([ourshipqty]*[unit cost],2) AS [Total Cost], Round([total
price]-[total cost],2) AS [Margin $], IIf([total
price]<>0,Format([margin $]/[total price],'Percent'),0) AS [Margin%],
ODBCUSER_CUSTOMER_VIEW.Name AS Customer, ODBCUSER_CUSTOMER_VIEW.CustID
FROM ((PUB_InvcHead INNER JOIN (PUB_InvcDtl LEFT JOIN PUB_ShipDtl ON
(PUB_InvcDtl.PartNum = PUB_ShipDtl.PartNum) AND (PUB_InvcDtl.OrderNum =
PUB_ShipDtl.OrderNum) AND (PUB_InvcDtl.OrderRelNum =
PUB_ShipDtl.OrderRelNum) AND (PUB_InvcDtl.Company =
PUB_ShipDtl.Company)) ON (PUB_InvcHead.InvoiceNum =
PUB_InvcDtl.InvoiceNum) AND (PUB_InvcHead.Company =
PUB_InvcDtl.Company)) LEFT JOIN PUB_Part ON PUB_InvcDtl.PartNum =
PUB_Part.PartNum) INNER JOIN ODBCUSER_CUSTOMER_VIEW ON
PUB_InvcHead.CustNum = ODBCUSER_CUSTOMER_VIEW.CustNum
WHERE (((PUB_InvcDtl.PartNum)<>'') AND ((PUB_InvcHead.Posted)=True) AND
((PUB_InvcHead.InvoiceType)="Shp"))
ORDER BY PUB_ShipDtl.JobNum, PUB_InvcDtl.InvoiceNum,
PUB_InvcHead.InvoiceDate;
Spaghetti for sure but you can C&P in an Access query and look at the
QBD window. I pulled the part description from the Part table instead
of the Order Line just because the folks in Contracts don't always match
the part description in the order. But everything else should be
straight forward. Also, there is a support document on this, 253MPS.
All that said, Todd et al. bring up some good points as to the accuracy
and timing of this report. As mentioned, it's just a snapshot at the
time of invoice and should be used accordingly.
________________________________
From:
vantage@yahoogroups.com [mailto:
vantage@yahoogroups.com] On Behalf
Of Bruce Butler
Sent: Monday, March 05, 2007 3:00 PM
To:
vantage@yahoogroups.com
Subject: RE: [Vantage] Sales Gross Margin report
I developed an SGM report I from scratch due to the very simplistic and
inaccurate approach within system canned report. We are pretty happy
with the numbers we get from the PDRs so we modeled the report after the
PDR, and tested against it. If you contact me offline, I will send a
screen shot of the table relationships I setup.
Bruce Butler
IT Manager
Knappe & Koester, Inc.
_____
From:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:
vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Jasper Recto
Sent: Friday, March 02, 2007 2:06 PM
To: Vantage Groups (E-mail)
Subject: [Vantage] Sales Gross Margin report
Does anybody have a report builder sales gross margin report they would
be willing to share. If not, can somebody give me some ideas on what
table they are pulling the information from.
Thanks,
Jasper
Jasper
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
This is an e-mail from Saab Barracuda LLC. It is for the intended recipient only and may contain confidential and privileged information. No one else may read, print, store, copy, forward or act in reliance on it or its attachments. If you are not the intended recipient, please return this message to the sender and delete the message and any attachments from your computer. Your cooperation is appreciated.
###########################################
This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange.
For more information, connect to
http://www.f-secure.com/
[Non-text portions of this message have been removed]