Exportable Sales GM Report

Here is my SGM query from Access:

SELECT DISTINCT PUB_ShipDtl.JobNum, PUB_InvcDtl.InvoiceNum, PUB_InvcDtl.InvoiceLine, 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_OrderDtl.ProdCode, PUB_Part.ProdCode AS ProdCode2, 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, PUB_ShipDtl.OrderNum, PUB_ShipDtl.OrderLine, PUB_ShipDtl.OrderRelNum
FROM (((PUB_InvcHead INNER JOIN (PUB_InvcDtl LEFT JOIN PUB_ShipDtl ON (PUB_InvcDtl.PackLine = PUB_ShipDtl.PackLine) AND (PUB_InvcDtl.PackNum = PUB_ShipDtl.PackNum) 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.Company = PUB_Part.Company) AND (PUB_InvcDtl.PartNum = PUB_Part.PartNum)) INNER JOIN ODBCUSER_CUSTOMER_VIEW ON (PUB_InvcHead.Company = ODBCUSER_CUSTOMER_VIEW.Company) AND (PUB_InvcHead.CustNum = ODBCUSER_CUSTOMER_VIEW.CustNum)) LEFT JOIN PUB_OrderDtl ON (PUB_ShipDtl.OrderLine = PUB_OrderDtl.OrderLine) AND (PUB_ShipDtl.Company = PUB_OrderDtl.Company) AND (PUB_ShipDtl.OrderNum = PUB_OrderDtl.OrderNum)
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;


--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...> wrote:
>
> I created an exportable-to-Excel version of the Sales Gross Margin
> report in 405 that made one of my users happy.
>
>
>
> This report has changed considerably in 408B, and I'm struggling to
> re-create the modifications I did to the report previously. One thing I
> don't understand is that the report seems to work primarily off of
> InvcDtl, which results in duplicate entries in the details section of
> the report when I remove the groupings.
>
>
>
> Does anyone have a BAQ, dashboard, or modified report that duplicates
> what this report does in a way that's exportable to Excel and otherwise
> easier to work with the data than the standard report?
>
>
>
> -bws
>
>
>
> --
>
> Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
> Picometrix
>
> bspolarich@...
> <mailto:bspolarich@...> ~ 734-864-5618 ~
> www.advancedphotonix.com <http://www.advancedphotonix.com>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
I created an exportable-to-Excel version of the Sales Gross Margin
report in 405 that made one of my users happy.



This report has changed considerably in 408B, and I'm struggling to
re-create the modifications I did to the report previously. One thing I
don't understand is that the report seems to work primarily off of
InvcDtl, which results in duplicate entries in the details section of
the report when I remove the groupings.



Does anyone have a BAQ, dashboard, or modified report that duplicates
what this report does in a way that's exportable to Excel and otherwise
easier to work with the data than the standard report?



-bws



--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich@...> ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>





[Non-text portions of this message have been removed]
Brian,



Does this format work for you?

Part Num

Tot qty

Customer

Total Cost PN

Mat'l Cost

Labor

Burden

SubCont

Mtl Burden

$Invoiced

$ Margin

% Margin

0206-0008

2

TRANE

131.97

121.18

4.32

6.47

0.00

0.00

172.50

40.53

23.50

0206-0009

2

TRANE

177.64

151.83

10.35

15.46

0.00

0.00

193.08

15.44

8.00

0206-0011

1

TRANE

48.90

48.90

0.00

0.00

0.00

0.00

56.07

7.17

12.79

0206-0012

1

TRANE

97.80

97.80

0.00

0.00

0.00

0.00

119.29

21.49

18.01

125120C

34

OSH

205.71

47.39

41.06

117.26

0.00

0.00

663.00

457.29

68.97

1264445

10

PRC

764.93

60.15

208.39

496.39

0.00

0.00

907.10

142.17

15.67

1310740W

7

OSH

613.26

143.90

146.27

323.09

0.00

0.00

730.10

116.84

16.00





Rob Bucek

Manufacturing Engineer

PH: (715) 284-5376 ext 3111

Mobile: (715)896-0590

FAX: (715)284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site) <http://www.dsmfg.com/>





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Brian W. Spolarich
Sent: Thursday, June 03, 2010 2:51 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Exportable Sales GM Report





I created an exportable-to-Excel version of the Sales Gross Margin
report in 405 that made one of my users happy.

This report has changed considerably in 408B, and I'm struggling to
re-create the modifications I did to the report previously. One thing I
don't understand is that the report seems to work primarily off of
InvcDtl, which results in duplicate entries in the details section of
the report when I remove the groupings.

Does anyone have a BAQ, dashboard, or modified report that duplicates
what this report does in a way that's exportable to Excel and otherwise
easier to work with the data than the standard report?

-bws

--

Brian W. Spolarich ~ Manager, Information Services ~ Advanced Photonix /
Picometrix

bspolarich@...
<mailto:bspolarich%40advancedphotonix.com>
<mailto:bspolarich@...
<mailto:bspolarich%40advancedphotonix.com> > ~ 734-864-5618 ~
www.advancedphotonix.com <http://www.advancedphotonix.com>

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]