Indented BOM Cost report

system · November 18, 2008, 9:10am

I have created a dashboard to group by Key1 to make it easy for user to get
to there data. I made a couple of tweaks to the code just to remove the
Corvu name in the status.

Thanks again!

Steve

-----Original Message-----
From: bw2868bond [mailto:bwalker@...]
Sent: November 18, 2008 8:08 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Indented BOM Cost report

You are welcome. We use the data with CorVu so not many fields are
used in UD20, but you can populate as many fields with whatever you
need for use with a BAQ pretty easily.

We have multiple users using the 'tool' that is why Key1 is the
userid. - to keep each persons data separate.

--- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com,
"vantage" <vantage@...> wrote:

>
> BW,
>
>
>
> Thanks for the upload!! I can find many uses for the utility.
>
>
>
> Steve
>
>
>
> -----Original Message-----
> From: bw2868bond [mailto:bwalker@...]
> Sent: November 15, 2008 2:25 PM
> To: vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com
> Subject: [Vantage] Re: Indented BOM Cost report
>
>
>
> I created a customization to UD20Entry that populates UD20 with a

BOM

> and a summarized BOM - we use it with CorVu for quoting materials,
> costs etc.
>
> I think it should be possible for the data to be used in a BAQ,
> joined to what ever tables needed and then sent to Crystal.
>
> I can upload to files section if anyone is interested.
>
> bw
>
> --- In vantage@yahoogroups <mailto:vantage%

40yahoogroups.com> .com, "Brian

> W, Spolarich" <bspolarich@>
> wrote:
> >
> >
> > This is on my to-do list right now: develop a custom BOM report
> that shows
> > costs. It is quite possible to do this in Crystal if you have
> MSSQL 2005 on
> > the backend.
> >
> > -brian
> >
> > On 11/12/08 10:06 AM, "Jasper Recto" <jrecto@> wrote:
> >
> > >
> > >
> > >
> > > Has anybody recreated the Indented BOM Cost report in report
> builder, crystal
> > > reports or Microsoft access?
> > >
> > > I need to be able to run that report and filter out certain

part

> classes.
> > >
> > > Any suggestions?
> > >
> > >
> > >
> > >
> > > .
> > >
> > >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

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

system · November 12, 2008, 10:06am

Has anybody recreated the Indented BOM Cost report in report builder, crystal reports or Microsoft access?

I need to be able to run that report and filter out certain part classes.

Any suggestions?

Thanks,

Jasper

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

system · November 15, 2008, 2:31pm

This is on my to-do list right now: develop a custom BOM report that shows
costs. It is quite possible to do this in Crystal if you have MSSQL 2005 on
the backend.

-brian

On 11/12/08 10:06 AM, "Jasper Recto" <jrecto@...> wrote:

>
>
>
> Has anybody recreated the Indented BOM Cost report in report builder, crystal
> reports or Microsoft access?
>
> I need to be able to run that report and filter out certain part classes.
>
> Any suggestions?
>
>
>
>
> .
>
>

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

system · November 15, 2008, 3:25pm

I created a customization to UD20Entry that populates UD20 with a BOM
and a summarized BOM - we use it with CorVu for quoting materials,
costs etc.

I think it should be possible for the data to be used in a BAQ,
joined to what ever tables needed and then sent to Crystal.

I can upload to files section if anyone is interested.

bw

--- In vantage@yahoogroups.com, "Brian W, Spolarich" <bspolarich@...>
wrote:

>
>
> This is on my to-do list right now: develop a custom BOM report

that shows

> costs. It is quite possible to do this in Crystal if you have

MSSQL 2005 on

> the backend.
>
> -brian
>
> On 11/12/08 10:06 AM, "Jasper Recto" <jrecto@...> wrote:
>
> >
> >
> >
> > Has anybody recreated the Indented BOM Cost report in report

builder, crystal

> > reports or Microsoft access?
> >
> > I need to be able to run that report and filter out certain part

classes.

> >
> > Any suggestions?
> >
> >
> >
> >
> > .
> >
> >
>
>
>
> [Non-text portions of this message have been removed]
>

system · November 15, 2008, 3:35pm

I am interested. Thanks.

Ross

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
bw2868bond
Sent: Saturday, November 15, 2008 2:25 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Indented BOM Cost report

I created a customization to UD20Entry that populates UD20 with a BOM
and a summarized BOM - we use it with CorVu for quoting materials,
costs etc.

I think it should be possible for the data to be used in a BAQ,
joined to what ever tables needed and then sent to Crystal.

I can upload to files section if anyone is interested.

bw

--- In vantage@yahoogroups.com, "Brian W, Spolarich" <bspolarich@...>
wrote:

>
>
> This is on my to-do list right now: develop a custom BOM report

that shows

> costs. It is quite possible to do this in Crystal if you have

MSSQL 2005 on

> the backend.
>
> -brian
>
> On 11/12/08 10:06 AM, "Jasper Recto" <jrecto@...> wrote:
>
> >
> >
> >
> > Has anybody recreated the Indented BOM Cost report in report

builder, crystal

> > reports or Microsoft access?
> >
> > I need to be able to run that report and filter out certain part

classes.

> >
> > Any suggestions?
> >
> >
> >
> >
> > .
> >
> >
>
>
>
> [Non-text portions of this message have been removed]
>

------------------------------------

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/linksYahoo! Groups Links

system · November 17, 2008, 6:31pm

Here are some routines to create a BOM in Access for Vantage 6. This is
slower because of all the calls to the database but no limitations to
the depth of the BOM. Once the BOM table is populated, you can use it
in queries for costed BOM's, etc. The routine you'll call is
BomByPartNumber and pass it the parameters. Haven't looked at this in a
long time so it probably could use a little clean-up but should get you
started.

Butch

************************************************************************
*************

Create a query called qryPartMaxRevDate

SELECT PUB_PartRev.PartNum, PUB_PartRev.RevisionNum,
Max(PUB_PartRev.EffectiveDate) AS MaxOfEffectiveDate,
PUB_PartRev.Approved, PUB_PartRev.Method

FROM PUB_PartRev

GROUP BY PUB_PartRev.PartNum, PUB_PartRev.RevisionNum,
PUB_PartRev.Approved, PUB_PartRev.Method

HAVING (((PUB_PartRev.Approved)=True));

Create a query called qryPartLatestRevDate

SELECT PUB_PartRev.PartNum, Max(PUB_PartRev.EffectiveDate) AS
MaxOfEffectiveDate

FROM PUB_PartRev

WHERE (((PUB_PartRev.Approved)=True))

GROUP BY PUB_PartRev.PartNum;

************************************************************************
*************

Save these in a module:

Function IsTableQuery(DbName As String, TName As String) As Integer

Dim db As Database, Found As Integer, Test As String

Const NAME_NOT_IN_COLLECTION = 3265

' Assume the table or query does not exist.

Found = False

' Trap for any errors.

On Error Resume Next

' If the database name is empty...

If Trim$(DbName) = "" Then

' Set Db to the current Db.

Set db = CurrentDb()

Else

' Otherwise, set Db to the specified open database.

Set db = DBEngine.Workspaces(0).OpenDatabase(DbName)

' See if an error occurred.

If Err Then

MsgBox "Could not find database to open: " & DbName

IsTableQuery = False

Exit Function

End If

End If

' See if the name is in the Tables collection.

Test = db.TableDefs(TName).Name

If Err <> NAME_NOT_IN_COLLECTION Then Found = True

' Reset the error variable.

Err = 0

' See if the name is in the Queries collection.

Test = db.QueryDefs(TName$).Name

If Err <> NAME_NOT_IN_COLLECTION Then Found = True

db.Close

IsTableQuery = Found

End Function

***********************************************************

Public Sub ResetBomTable(InTablename As String)

On Error GoTo ErrProc

Dim db As Database

Set db = CurrentDb()

' Check to see if the table exists

If IsTableQuery("", InTablename) Then

db.Execute "Delete * from " & InTablename & ";"

Else

db.Execute "CREATE TABLE " & InTablename & _

" (idsBomID Long, strTopLevelPart Text(20), lngLevel Long,
strParentPartNum Text(20), " & _

"strParentRev Text (10), strMtlPartNum Text(20), strMtlRev
Text (10), dblQuantity Double, " & _

"dblParentQuantity Double, dblTotal Double)"

End If

db.Close

ExitProc:

Exit Sub

ErrProc:

MsgBox Err.Description

Resume ExitProc

End Sub

***********************************************************

Public Sub FindBom(TopLevelPartNum As String, Level As Long,
ParentPartNum As String, _

ParentRev As String, ParentQuantity As Double, InTablename As
String)

On Error GoTo ErrProc

Dim db As Database

Dim rs As Recordset

Dim rsLog As Recordset

Set db = CurrentDb()

Set rsLog = db.OpenRecordset(InTablename)

UpdateStatus ("Expanding " & ParentPartNum)

If Level = 0 Then

rsLog.AddNew

rsLog![idsBomID] = GetNextID(InTablename, "idsBomId")

rsLog!strTopLevelPart = TopLevelPartNum

rsLog![lngLevel] = Level

rsLog![strParentPartNum] = Null

rsLog![strParentRev] = Null

rsLog![strMtlPartNum] = ParentPartNum

rsLog![strMtlRev] = ParentRev

rsLog![dblQuantity] = 1

rsLog![dblParentQuantity] = ParentQuantity

rsLog![dblTotal] = ParentQuantity

rsLog.Update

FindBom ParentPartNum, 1, ParentPartNum, ParentRev,
ParentQuantity, InTablename

Else

Set rs = db.OpenRecordset("SELECT qryPartMaxRevDate.PartNum,
qryPartMaxRevDate.RevisionNum AS ParentRev, " & _

"qryPartMaxRevDate.Method, PUB_PartMtl.MtlSeq,
PUB_PartMtl.QtyPer, PUB_PartMtl.MtlPartNum, " & _

"qryPartMaxRevDate_1.RevisionNum AS MtlRev,
qryPartMaxRevDate_1.Method AS MtlMethod " & _

"FROM ((qryPartMaxRevDate INNER JOIN PUB_PartMtl ON
(qryPartMaxRevDate.RevisionNum = " & _

"PUB_PartMtl.RevisionNum) AND (qryPartMaxRevDate.PartNum =
PUB_PartMtl.PartNum)) INNER JOIN " & _

"qryPartMaxRevDate AS qryPartMaxRevDate_1 ON
PUB_PartMtl.MtlPartNum = qryPartMaxRevDate_1.PartNum) " & _

"INNER JOIN qryPartLatestRevDate ON
(qryPartMaxRevDate_1.MaxOfEffectiveDate = " & _

"qryPartLatestRevDate.MaxOfEffectiveDate) AND
(qryPartMaxRevDate_1.PartNum = qryPartLatestRevDate.PartNum) " & _

"WHERE (qryPartMaxRevDate.PartNum = '" & ParentPartNum & "')
And " & _

"(qryPartMaxRevDate.RevisionNum = '" & ParentRev & "') " & _

"ORDER BY PUB_PartMtl.MtlSeq;")

While Not rs.EOF

rsLog.AddNew

rsLog![idsBomID] = GetNextID(InTablename, "idsBomId")

rsLog![lngLevel] = Level

rsLog![strTopLevelPart] = TopLevelPartNum

rsLog![strParentPartNum] = ParentPartNum

rsLog![strParentRev] = ParentRev

rsLog![strMtlPartNum] = rs![MtlPartNum]

rsLog![strMtlRev] = rs![MtlRev]

rsLog![dblQuantity] = rs![QtyPer]

rsLog![dblParentQuantity] = ParentQuantity

rsLog![dblTotal] = ParentQuantity * rs![QtyPer]

rsLog.Update

If rs!MtlMethod Then FindBom TopLevelPartNum, Level + 1,
rs!MtlPartNum, rs!MtlRev, ParentQuantity * rs!QtyPer, InTablename

rs.MoveNext

Wend

rs.Close

End If

rsLog.Close

db.Close

ExitProc:

Exit Sub

ErrProc:

MsgBox Err.Description

Resume ExitProc

End Sub

***********************************************************

Public Sub BomByPartNumber(InPartNumber As String, InRev As String,
InQty As Double, _

InTablename As String, InResetTable As Boolean)

On Error GoTo ErrProc

Dim db As Database

Dim rs As Recordset

If InResetTable Then ResetBomTable InTablename

Set db = CurrentDb()

'Verify the part number

Set rs = db.OpenRecordset("SELECT PartNum FROM Pub_PartRev " & _

"WHERE (PartNum ='" & InPartNumber & "') and (RevisionNum = '" &
InRev & "') and (Approved = True)")

If Not rs.EOF Then

FindBom rs![PartNum], 0, rs![PartNum], InRev, InQty, InTablename

Else

' MsgBox "Invalid part number, invalid revision or unapproved
revision."

Debug.Print InPartNumber & " - Invalid part"

End If

UpdateStatus ("")

ExitProc:

Set rs = Nothing

Set db = Nothing

Exit Sub

ErrProc:

MsgBox Err.Description

Resume ExitProc

End Sub

Public Sub UpdateStatus(InString)

On Error GoTo ErrProc

Dim varReturn As Variant

If Len(InString) = 0 Then

varReturn = SysCmd(acSysCmdClearStatus)

Else

varReturn = SysCmd(acSysCmdSetStatus, InString)

End If

ExitProc:

Exit Sub

ErrProc:

MsgBox "UpdateStatus" & Err.Number & " - " & Err.Description

Resume ExitProc

End Sub

***********************************************************

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jasper Recto
Sent: Wednesday, November 12, 2008 7:07 AM
To: Vantage Groups (vantage@yahoogroups.com)
Subject: [Vantage] Indented BOM Cost report

Has anybody recreated the Indented BOM Cost report in report builder,
crystal reports or Microsoft access?

I need to be able to run that report and filter out certain part
classes.

Any suggestions?

Thanks,

Jasper

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

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

system · November 18, 2008, 8:55am

BW,

Thanks for the upload!! I can find many uses for the utility.

Steve

-----Original Message-----
From: bw2868bond [mailto:bwalker@...]
Sent: November 15, 2008 2:25 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Indented BOM Cost report

I created a customization to UD20Entry that populates UD20 with a BOM
and a summarized BOM - we use it with CorVu for quoting materials,
costs etc.

I think it should be possible for the data to be used in a BAQ,
joined to what ever tables needed and then sent to Crystal.

I can upload to files section if anyone is interested.

bw

--- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com, "Brian
W, Spolarich" <bspolarich@...>
wrote:

>
>
> This is on my to-do list right now: develop a custom BOM report

that shows

> costs. It is quite possible to do this in Crystal if you have

MSSQL 2005 on

> the backend.
>
> -brian
>
> On 11/12/08 10:06 AM, "Jasper Recto" <jrecto@...> wrote:
>
> >
> >
> >
> > Has anybody recreated the Indented BOM Cost report in report

builder, crystal

> > reports or Microsoft access?
> >
> > I need to be able to run that report and filter out certain part

classes.

> >
> > Any suggestions?
> >
> >
> >
> >
> > .
> >
> >
>
>
>
> [Non-text portions of this message have been removed]
>

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

system · November 18, 2008, 9:08am

You are welcome. We use the data with CorVu so not many fields are
used in UD20, but you can populate as many fields with whatever you
need for use with a BAQ pretty easily.

We have multiple users using the 'tool' that is why Key1 is the
userid. - to keep each persons data separate.

--- In vantage@yahoogroups.com, "vantage" <vantage@...> wrote:
>
> BW,
>
>
>
> Thanks for the upload!! I can find many uses for the utility.
>
>
>
> Steve
>
>
>
> -----Original Message-----
> From: bw2868bond [mailto:bwalker@...]
> Sent: November 15, 2008 2:25 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Indented BOM Cost report
>
>
>
> I created a customization to UD20Entry that populates UD20 with a
BOM
> and a summarized BOM - we use it with CorVu for quoting materials,
> costs etc.
>
> I think it should be possible for the data to be used in a BAQ,
> joined to what ever tables needed and then sent to Crystal.
>
> I can upload to files section if anyone is interested.
>
> bw
>
> --- In vantage@yahoogroups <mailto:vantage%
40yahoogroups.com> .com, "Brian
> W, Spolarich" <bspolarich@>
> wrote:
> >
> >
> > This is on my to-do list right now: develop a custom BOM report
> that shows
> > costs. It is quite possible to do this in Crystal if you have
> MSSQL 2005 on
> > the backend.
> >
> > -brian
> >
> > On 11/12/08 10:06 AM, "Jasper Recto" <jrecto@> wrote:
> >
> > >
> > >
> > >
> > > Has anybody recreated the Indented BOM Cost report in report
> builder, crystal
> > > reports or Microsoft access?
> > >
> > > I need to be able to run that report and filter out certain
part
> classes.
> > >
> > > Any suggestions?
> > >
> > >
> > >
> > >
> > > .
> > >
> > >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>