Not sure if I completely understand the question but I'll take a stab at it. The Part.StdMaterialCost field will reflect the current standard at a given point in time (right?). So it carries no history of what it was earlier in time. I have a report I must do for our auditors on raw material costs that has the similar requirement of needing to compare the cost at the end of the year with the cost at the beginning. Two ways I have approached this are:
1. Restore database from last year's end of year backup to our "Test" database, extract Part Num and Cost to Access DB table, extract current cost to another table, link and report change (for some sort of LIFO factor our auditors user). Could be difficult if you have upgraded during the year and restored DB needs to be converted (schema changes, etc...).
2. A Report Builder report that exams all the part's purchases and takes earliest one in the year and compares to current raw material cost. Not 100% accurate if first purchase in year was, say, in June. But good enough to compare cost change over the year.
Since you are looking for material costs going into a Part you might be able to reconstruct from all the jobs for the part in each year. Perhaps from the JobMtl table? You could then add up 2003 material costs (based on Jobs Closed in 2003?) and divide by Qty and compare to the current standard cost in the Part table. Might this work? Like #2 above not 100% but probably pretty close.
-Todd C.
-----Original Message-----
From: Cliff Drumeller [mailto:cliff@...]
Sent: Wednesday, April 07, 2004 9:04 AM
To: VantageGroup (E-mail)
Subject: [Vantage] Report Builder question
Hey All
Is it possible to write a report that would compare last years
Part.StdMaterialCost with the current Part.StdMaterialCost. I'm drawing a
blank. If you give a date range for 2003 to pick up last years cost how do
you get the current cost ?? I'm sure someone has figured it out.
Cliff Drumeller
IT Manager
Mass Precision Sheetmetal
408 954 0200 x 217
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Sponsor
ADVERTISEMENT
<http://rd.yahoo.com/SIG=12c7mbktq/M=291630.4786521.5933964.1261774/D=egroupweb/S=1705007183:HM/EXP=1081433185/A=2072415/R=0/SIG=11thh7ako/*http://www.netflix.com/Default?mqso=60178432&partid=4786521> click here
<http://us.adserver.yahoo.com/l?M=291630.4786521.5933964.1261774/D=egroupweb/S=:HM/A=2072415/rand=482719576>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com <mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .
[Non-text portions of this message have been removed]
1. Restore database from last year's end of year backup to our "Test" database, extract Part Num and Cost to Access DB table, extract current cost to another table, link and report change (for some sort of LIFO factor our auditors user). Could be difficult if you have upgraded during the year and restored DB needs to be converted (schema changes, etc...).
2. A Report Builder report that exams all the part's purchases and takes earliest one in the year and compares to current raw material cost. Not 100% accurate if first purchase in year was, say, in June. But good enough to compare cost change over the year.
Since you are looking for material costs going into a Part you might be able to reconstruct from all the jobs for the part in each year. Perhaps from the JobMtl table? You could then add up 2003 material costs (based on Jobs Closed in 2003?) and divide by Qty and compare to the current standard cost in the Part table. Might this work? Like #2 above not 100% but probably pretty close.
-Todd C.
-----Original Message-----
From: Cliff Drumeller [mailto:cliff@...]
Sent: Wednesday, April 07, 2004 9:04 AM
To: VantageGroup (E-mail)
Subject: [Vantage] Report Builder question
Hey All
Is it possible to write a report that would compare last years
Part.StdMaterialCost with the current Part.StdMaterialCost. I'm drawing a
blank. If you give a date range for 2003 to pick up last years cost how do
you get the current cost ?? I'm sure someone has figured it out.
Cliff Drumeller
IT Manager
Mass Precision Sheetmetal
408 954 0200 x 217
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links
Yahoo! Groups Sponsor
ADVERTISEMENT
<http://rd.yahoo.com/SIG=12c7mbktq/M=291630.4786521.5933964.1261774/D=egroupweb/S=1705007183:HM/EXP=1081433185/A=2072415/R=0/SIG=11thh7ako/*http://www.netflix.com/Default?mqso=60178432&partid=4786521> click here
<http://us.adserver.yahoo.com/l?M=291630.4786521.5933964.1261774/D=egroupweb/S=:HM/A=2072415/rand=482719576>
_____
Yahoo! Groups Links
* To visit your group on the web, go to:
http://groups.yahoo.com/group/vantage/
* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com <mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>
* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .
[Non-text portions of this message have been removed]