Time Phase Report To Excel In 8.03.305

[system]

I have a mimic of the Vantage Time Phase report I wrote in crystal for
6.1
I would be curious to have a look at yours to do some comparisons and
see if there is extra value I could get out of it. I would be willing to
swap reports with you. You should easily enough be able to update mine
to V8 with minor changes to fields based on V8 schema/table structure
change. Also my report has a formula for suggestions that may be the
solution to your question below.

________________________________

From: Charles Carden [mailto:shadowcar1449@...]
Sent: Saturday, October 25, 2008 6:14 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Re: Time Phase Report To Excel In 8.03.305



The part detail table is updated immediately and does not require MRP to
run.

It is easy enough to write a time phase in Crystal. I created a BAQ
using the following tables:

Part - filtered for only Quantity Bearing Purchased Parts
PartPlant - Buyer Id and Default Supplier and MRP Parameters
PurAgent - Buyer Name
Vendor - Default Supplier ID and Name
PartDtl - Demand and Purchases
JobHead - Part Revision
SugPOChg - Purchase Order Info
PartWhse - On Hand Quantity
JobProd - Order Number if Demand comes from an Order

I provided filters by Buyer ID and Part Number. This is what the buyers
are always looking for. The report can still be lengthy for a single
buyer but it is much better than the thousands of pages that are printed
with the standard MRP report. I was unable to show the new PO
suggestions on the report because of table linkage issues. If anyone has
figured out how to include the new suggestions I would be very
interested in how it was done. The change suggestions table is the only
one that does require MRP.

I also allowed a cutoff date option for the report since we run MRP out
9 months but the buyers are not always wanting to look that far in the
future. I would liked to have provided an option to only include parts
with exceptions but I couldn't figure that one out either. I did
calculate an order by date for the parts that dropped below zero on the
report. This was tricky because I had to look at the receive time and
lead times and then had to ensure that I counted back only working days
and then come up with an order date that was not on the weekend. I
probably should have used the calendar file for the Vendor to come up
with working days but since we only have one calendar I decided against
it.

The report was sorted by Buyer ID/Part Number.

Charles Carden
IT Manager
Manitex, Inc.

----- Original Message -----
From: nmtaylor1969
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Sent: Saturday, October 25, 2008 1:23 AM
Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305

Thanks for that Gary, I had not spotted that, and I must say the data
in that table looks quite interesting. Its definitely the base data
for the timephase alright... :o)

Do you know if this data is updated in real-time, or does it get
updated when MRP is run...?

I had gone with my original plan and extracted the data via service
connect, and this works fine, however the data in the partdtl table
may well have lots of other uses for BAQ's and dashboards etc...

Nice one...

Many thanks,

Nick

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"Gary Parfrey" <garyp@...> wrote:
>
> Nick, Rob
>
>
>
> Hope I got the whole thread. The timephase core dataset is in the
> partdtl table. We have rewritten the timephase report for
customers in
> the past in crystal.
>
>
>
> Gary Parfrey
>
>
>
> From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
> Of nmtaylor1969
> Sent: 23 October 2008 16:01
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
>
>
>
>
> Sorry, I'm on one now Rob...!
>
> If I have my timephase dataset in a format I can crunch/manipulate,
I
> can then produce the horizontal time phase ( i.e. a report that
> groups demand into weekly or monthly buckets ), dump this back into
> Excel, and use this as the main scheduling document with all my
> vendors...
>
> 8.03.305 does not appear to produce this report out of the box
which
> is most frustrating...
>
> If we have this, then we can run just about everything via KanBan,
> and practically turn the suggestions of all together... ;o)
>
> Nick
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ,
> Robert Brown <robertb_versa@>
> wrote:
> >
> > Nick,
> >
> > Unfortunately - No. Time Phase not being a Crystal based report
> means their is no intermediate xml source you can read into excel
as
> a datasource.
> >
> > Since the data tables themselves are non-presistent temp tables,
> you can't even do a simple odbc poke query to bring the data into
> excel.
> >
> > I think you are on to something re: Service Connect's exposure of
> the timephase BOs & methods. That would at least trigger part by
part
> population of the temp data they generate. How you would then read
> them back out into a massive single data source (xml, csv) excel
can
> leverage is beyond my knowledge level at this time.
> >
> > Is it possible to invoke Service connect processing from within
> excel (via vbScript code)? Being excel/vbScript biased, that would
be
> my first attack plan attempt.
> >
> > You are 'one clever dude' Nick Taylor... Your posts consistently
> challenge & expand my thinking on ways to make this monstrousity
work
> for us instead of the other way around.
> >
> > Thanks
> >
> > Rob
> >
> > --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@> wrote:
> >
> > From: nmtaylor1969 <n.taylor@>
> > Subject: [Vantage] Time Phase Report To Excel In 8.03.305
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
> > Date: Wednesday, October 22, 2008, 3:56 PM
> >
> >
> >
> >
> >
> >
> > Has anybody found a way of getting the time phase report out to
> Excel ??
> >
> > We are struggling with managing purchasing suggestions, and this
> report
> > is useful, however Vantage produces this as a hardcopy report
> only... :o
> > (
> >
> > I know I can do this in ServiceConnect as the TimePhase
calculation
> is
> > available as a webservice, but before I run off and do that, I
> wondered
> > if anybody had found a quick way of getting it into Excel ??
> >
> > Thanks,
> >
> > Nick
> >
>
>
>
>
>
> [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]

Has anybody found a way of getting the time phase report out to Excel ??

We are struggling with managing purchasing suggestions, and this report
is useful, however Vantage produces this as a hardcopy report only... :o
(

I know I can do this in ServiceConnect as the TimePhase calculation is
available as a webservice, but before I run off and do that, I wondered
if anybody had found a quick way of getting it into Excel ??

Thanks,

Nick

[system]

Nick,

Unfortunately - No. Time Phase not being a Crystal based report means their is no intermediate xml source you can read into excel as a datasource.

Since the data tables themselves are non-presistent temp tables, you can't even do a simple odbc poke query to bring the data into excel.

I think you are on to something re: Service Connect's exposure of the timephase BOs & methods. That would at least trigger part by part population of the temp data they generate. How you would then read them back out into a massive single data source (xml, csv) excel can leverage is beyond my knowledge level at this time.

Is it possible to invoke Service connect processing from within excel (via vbScript code)? Being excel/vbScript biased, that would be my first attack plan attempt.

You are 'one clever dude' Nick Taylor... Your posts consistently challenge & expand my thinking on ways to make this monstrousity work for us instead of the other way around.

Thanks

Rob

--- On Wed, 10/22/08, nmtaylor1969 <n.taylor@...> wrote:

From: nmtaylor1969 <n.taylor@...>
Subject: [Vantage] Time Phase Report To Excel In 8.03.305
To: vantage@yahoogroups.com
Date: Wednesday, October 22, 2008, 3:56 PM






Has anybody found a way of getting the time phase report out to Excel ??

We are struggling with managing purchasing suggestions, and this report
is useful, however Vantage produces this as a hardcopy report only... :o
(

I know I can do this in ServiceConnect as the TimePhase calculation is
available as a webservice, but before I run off and do that, I wondered
if anybody had found a quick way of getting it into Excel ??

Thanks,

Nick

[system]

Hmmm, I expected that this would be calculated on the fly Rob...

ServiceConnect CAN do it, the only tricky bit is how you pull all the
individual part time phase data into one spreadsheet or dataset. If I
really run out of steam with S/C I could use dear old FoxPro to pull
all the individual XML bits together and produce one big dataset (
lets say we run this overnight, every night ). Then I really hold the
keys to power because I can do what ever I like with the timephase
data and write loads of really useful reports on it... :o)

What I am trying to do is to see the global picture, and show the
exceptions where we go negative or where we about to bust the company
because we are way too stock positive. I can then shut all the other
crap out because its of no interest...

So we could kind of turn off the suggestions for all but the most
critical of "A" class parts and use my new exception report to see
the really important bits... The buyers will love me, and we wont
spend all our time playing "chase the suggestion..."

Then I will be running MRP and not the other way around...! ;o)

I dont know if you can call S/C from within Excel, but I do know you
can access the underlying web methods from a dotnet environment.

Me? Clever? Naah Rob, I still have a long way to go yet, but I will
take the compliment from one so esteemed... ;o)

Nick


--- In vantage@yahoogroups.com, Robert Brown <robertb_versa@...>
wrote:

>
> Nick,
>
> Unfortunately - No. Time Phase not being a Crystal based report

means their is no intermediate xml source you can read into excel as
a datasource.

>
> Since the data tables themselves are non-presistent temp tables,

you can't even do a simple odbc poke query to bring the data into
excel.

>
> I think you are on to something re: Service Connect's exposure of

the timephase BOs & methods. That would at least trigger part by part
population of the temp data they generate. How you would then read
them back out into a massive single data source (xml, csv) excel can
leverage is beyond my knowledge level at this time.

>
> Is it possible to invoke Service connect processing from within

excel (via vbScript code)? Being excel/vbScript biased, that would be
my first attack plan attempt.

>
> You are 'one clever dude' Nick Taylor... Your posts consistently

challenge & expand my thinking on ways to make this monstrousity work
for us instead of the other way around.

>
> Thanks
>
> Rob
>
> --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@...> wrote:
>
> From: nmtaylor1969 <n.taylor@...>
> Subject: [Vantage] Time Phase Report To Excel In 8.03.305
> To: vantage@yahoogroups.com
> Date: Wednesday, October 22, 2008, 3:56 PM
>
>
>
>
>
>
> Has anybody found a way of getting the time phase report out to

Excel ??

>
> We are struggling with managing purchasing suggestions, and this

report

> is useful, however Vantage produces this as a hardcopy report

only... :o

> (
>
> I know I can do this in ServiceConnect as the TimePhase calculation

is

> available as a webservice, but before I run off and do that, I

wondered

> if anybody had found a quick way of getting it into Excel ??
>
> Thanks,
>
> Nick
>

[system]

Sorry, I'm on one now Rob...!

If I have my timephase dataset in a format I can crunch/manipulate, I
can then produce the horizontal time phase ( i.e. a report that
groups demand into weekly or monthly buckets ), dump this back into
Excel, and use this as the main scheduling document with all my
vendors...

8.03.305 does not appear to produce this report out of the box which
is most frustrating...

If we have this, then we can run just about everything via KanBan,
and practically turn the suggestions of all together... ;o)

Nick


--- In vantage@yahoogroups.com, Robert Brown <robertb_versa@...>
wrote:

>
> Nick,
>
> Unfortunately - No. Time Phase not being a Crystal based report

means their is no intermediate xml source you can read into excel as
a datasource.

>
> Since the data tables themselves are non-presistent temp tables,

you can't even do a simple odbc poke query to bring the data into
excel.

>
> I think you are on to something re: Service Connect's exposure of

the timephase BOs & methods. That would at least trigger part by part
population of the temp data they generate. How you would then read
them back out into a massive single data source (xml, csv) excel can
leverage is beyond my knowledge level at this time.

>
> Is it possible to invoke Service connect processing from within

excel (via vbScript code)? Being excel/vbScript biased, that would be
my first attack plan attempt.

>
> You are 'one clever dude' Nick Taylor... Your posts consistently

challenge & expand my thinking on ways to make this monstrousity work
for us instead of the other way around.

>
> Thanks
>
> Rob
>
> --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@...> wrote:
>
> From: nmtaylor1969 <n.taylor@...>
> Subject: [Vantage] Time Phase Report To Excel In 8.03.305
> To: vantage@yahoogroups.com
> Date: Wednesday, October 22, 2008, 3:56 PM
>
>
>
>
>
>
> Has anybody found a way of getting the time phase report out to

Excel ??

>
> We are struggling with managing purchasing suggestions, and this

report

> is useful, however Vantage produces this as a hardcopy report

only... :o

> (
>
> I know I can do this in ServiceConnect as the TimePhase calculation

is

> available as a webservice, but before I run off and do that, I

wondered

> if anybody had found a quick way of getting it into Excel ??
>
> Thanks,
>
> Nick
>

[system]

That's the ticket Nick!

Time Phase stinks because it is awash with too much detail (versus summary by day as every other netting screen I ever used is - with detail available by drill down or pegging). That said, because it is temp data tables with no ud fields, I can't even add pertinent (to us) info that might overcome the need to right click launch other apps to find (really slowing the process).

If you can pull that data out via service connect (and deposit in whatever db you like best to then bring into excel) you'll have it made in the shade.

...Now I have to go lobby to get Service Connect sooner rather than later. };0

Thanks

Rob

--- On Thu, 10/23/08, nmtaylor1969 <n.taylor@...> wrote:

From: nmtaylor1969 <n.taylor@...>
Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
To: vantage@yahoogroups.com
Date: Thursday, October 23, 2008, 11:00 AM







Sorry, I'm on one now Rob...!

If I have my timephase dataset in a format I can crunch/manipulate, I
can then produce the horizontal time phase ( i.e. a report that
groups demand into weekly or monthly buckets ), dump this back into
Excel, and use this as the main scheduling document with all my
vendors...

8.03.305 does not appear to produce this report out of the box which
is most frustrating. ..

If we have this, then we can run just about everything via KanBan,
and practically turn the suggestions of all together... ;o)

Nick

--- In vantage@yahoogroups .com, Robert Brown <robertb_versa@ ...>
wrote:
>
> Nick,
>
> Unfortunately - No. Time Phase not being a Crystal based report
means their is no intermediate xml source you can read into excel as
a datasource.
>
> Since the data tables themselves are non-presistent temp tables,
you can't even do a simple odbc poke query to bring the data into
excel.
>
> I think you are on to something re: Service Connect's exposure of
the timephase BOs & methods. That would at least trigger part by part
population of the temp data they generate. How you would then read
them back out into a massive single data source (xml, csv) excel can
leverage is beyond my knowledge level at this time.
>
> Is it possible to invoke Service connect processing from within
excel (via vbScript code)? Being excel/vbScript biased, that would be
my first attack plan attempt.
>
> You are 'one clever dude' Nick Taylor... Your posts consistently
challenge & expand my thinking on ways to make this monstrousity work
for us instead of the other way around.
>
> Thanks
>
> Rob
>
> --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@.. .> wrote:
>
> From: nmtaylor1969 <n.taylor@.. .>
> Subject: [Vantage] Time Phase Report To Excel In 8.03.305
> To: vantage@yahoogroups .com
> Date: Wednesday, October 22, 2008, 3:56 PM
>
>
>
>
>
>
> Has anybody found a way of getting the time phase report out to
Excel ??
>
> We are struggling with managing purchasing suggestions, and this
report
> is useful, however Vantage produces this as a hardcopy report
only... :o
> (
>
> I know I can do this in ServiceConnect as the TimePhase calculation
is
> available as a webservice, but before I run off and do that, I
wondered
> if anybody had found a quick way of getting it into Excel ??
>
> Thanks,
>
> Nick
>

[system]

Nick, Rob



Hope I got the whole thread. The timephase core dataset is in the
partdtl table. We have rewritten the timephase report for customers in
the past in crystal.



Gary Parfrey



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of nmtaylor1969
Sent: 23 October 2008 16:01
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305




Sorry, I'm on one now Rob...!

If I have my timephase dataset in a format I can crunch/manipulate, I
can then produce the horizontal time phase ( i.e. a report that
groups demand into weekly or monthly buckets ), dump this back into
Excel, and use this as the main scheduling document with all my
vendors...

8.03.305 does not appear to produce this report out of the box which
is most frustrating...

If we have this, then we can run just about everything via KanBan,
and practically turn the suggestions of all together... ;o)

Nick

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
Robert Brown <robertb_versa@...>
wrote:

>
> Nick,
>
> Unfortunately - No. Time Phase not being a Crystal based report

means their is no intermediate xml source you can read into excel as
a datasource.

>
> Since the data tables themselves are non-presistent temp tables,

you can't even do a simple odbc poke query to bring the data into
excel.

>
> I think you are on to something re: Service Connect's exposure of

the timephase BOs & methods. That would at least trigger part by part
population of the temp data they generate. How you would then read
them back out into a massive single data source (xml, csv) excel can
leverage is beyond my knowledge level at this time.

>
> Is it possible to invoke Service connect processing from within

excel (via vbScript code)? Being excel/vbScript biased, that would be
my first attack plan attempt.

>
> You are 'one clever dude' Nick Taylor... Your posts consistently

challenge & expand my thinking on ways to make this monstrousity work
for us instead of the other way around.

>
> Thanks
>
> Rob
>
> --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@...> wrote:
>
> From: nmtaylor1969 <n.taylor@...>
> Subject: [Vantage] Time Phase Report To Excel In 8.03.305
> To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> Date: Wednesday, October 22, 2008, 3:56 PM
>
>
>
>
>
>
> Has anybody found a way of getting the time phase report out to

Excel ??

>
> We are struggling with managing purchasing suggestions, and this

report

> is useful, however Vantage produces this as a hardcopy report

only... :o

> (
>
> I know I can do this in ServiceConnect as the TimePhase calculation

is

> available as a webservice, but before I run off and do that, I

wondered

> if anybody had found a quick way of getting it into Excel ??
>
> Thanks,
>
> Nick
>

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

[system]

Thanks for that Gary, I had not spotted that, and I must say the data
in that table looks quite interesting. Its definitely the base data
for the timephase alright... :o)

Do you know if this data is updated in real-time, or does it get
updated when MRP is run...?

I had gone with my original plan and extracted the data via service
connect, and this works fine, however the data in the partdtl table
may well have lots of other uses for BAQ's and dashboards etc...

Nice one...

Many thanks,

Nick

--- In vantage@yahoogroups.com, "Gary Parfrey" <garyp@...> wrote:
>
> Nick, Rob
>
>
>
> Hope I got the whole thread. The timephase core dataset is in the
> partdtl table. We have rewritten the timephase report for
customers in
> the past in crystal.
>
>
>
> Gary Parfrey
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of nmtaylor1969
> Sent: 23 October 2008 16:01
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
>
>
>
>
> Sorry, I'm on one now Rob...!
>
> If I have my timephase dataset in a format I can crunch/manipulate,
I
> can then produce the horizontal time phase ( i.e. a report that
> groups demand into weekly or monthly buckets ), dump this back into
> Excel, and use this as the main scheduling document with all my
> vendors...
>
> 8.03.305 does not appear to produce this report out of the box
which
> is most frustrating...
>
> If we have this, then we can run just about everything via KanBan,
> and practically turn the suggestions of all together... ;o)
>
> Nick
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> Robert Brown <robertb_versa@>
> wrote:
> >
> > Nick,
> >
> > Unfortunately - No. Time Phase not being a Crystal based report
> means their is no intermediate xml source you can read into excel
as
> a datasource.
> >
> > Since the data tables themselves are non-presistent temp tables,
> you can't even do a simple odbc poke query to bring the data into
> excel.
> >
> > I think you are on to something re: Service Connect's exposure of
> the timephase BOs & methods. That would at least trigger part by
part
> population of the temp data they generate. How you would then read
> them back out into a massive single data source (xml, csv) excel
can
> leverage is beyond my knowledge level at this time.
> >
> > Is it possible to invoke Service connect processing from within
> excel (via vbScript code)? Being excel/vbScript biased, that would
be
> my first attack plan attempt.
> >
> > You are 'one clever dude' Nick Taylor... Your posts consistently
> challenge & expand my thinking on ways to make this monstrousity
work
> for us instead of the other way around.
> >
> > Thanks
> >
> > Rob
> >
> > --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@> wrote:
> >
> > From: nmtaylor1969 <n.taylor@>
> > Subject: [Vantage] Time Phase Report To Excel In 8.03.305
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Date: Wednesday, October 22, 2008, 3:56 PM
> >
> >
> >
> >
> >
> >
> > Has anybody found a way of getting the time phase report out to
> Excel ??
> >
> > We are struggling with managing purchasing suggestions, and this
> report
> > is useful, however Vantage produces this as a hardcopy report
> only... :o
> > (
> >
> > I know I can do this in ServiceConnect as the TimePhase
calculation
> is
> > available as a webservice, but before I run off and do that, I
> wondered
> > if anybody had found a quick way of getting it into Excel ??
> >
> > Thanks,
> >
> > Nick
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

[system]

The part detail table is updated immediately and does not require MRP to run.

It is easy enough to write a time phase in Crystal. I created a BAQ using the following tables:

Part - filtered for only Quantity Bearing Purchased Parts
PartPlant - Buyer Id and Default Supplier and MRP Parameters
PurAgent - Buyer Name
Vendor - Default Supplier ID and Name
PartDtl - Demand and Purchases
JobHead - Part Revision
SugPOChg - Purchase Order Info
PartWhse - On Hand Quantity
JobProd - Order Number if Demand comes from an Order

I provided filters by Buyer ID and Part Number. This is what the buyers are always looking for. The report can still be lengthy for a single buyer but it is much better than the thousands of pages that are printed with the standard MRP report. I was unable to show the new PO suggestions on the report because of table linkage issues. If anyone has figured out how to include the new suggestions I would be very interested in how it was done. The change suggestions table is the only one that does require MRP.

I also allowed a cutoff date option for the report since we run MRP out 9 months but the buyers are not always wanting to look that far in the future. I would liked to have provided an option to only include parts with exceptions but I couldn't figure that one out either. I did calculate an order by date for the parts that dropped below zero on the report. This was tricky because I had to look at the receive time and lead times and then had to ensure that I counted back only working days and then come up with an order date that was not on the weekend. I probably should have used the calendar file for the Vendor to come up with working days but since we only have one calendar I decided against it.

The report was sorted by Buyer ID/Part Number.


Charles Carden
IT Manager
Manitex, Inc.

----- Original Message -----
From: nmtaylor1969
To: vantage@yahoogroups.com
Sent: Saturday, October 25, 2008 1:23 AM
Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305


Thanks for that Gary, I had not spotted that, and I must say the data
in that table looks quite interesting. Its definitely the base data
for the timephase alright... :o)

Do you know if this data is updated in real-time, or does it get
updated when MRP is run...?

I had gone with my original plan and extracted the data via service
connect, and this works fine, however the data in the partdtl table
may well have lots of other uses for BAQ's and dashboards etc...

Nice one...

Many thanks,

Nick

--- In vantage@yahoogroups.com, "Gary Parfrey" <garyp@...> wrote:
>
> Nick, Rob
>
>
>
> Hope I got the whole thread. The timephase core dataset is in the
> partdtl table. We have rewritten the timephase report for
customers in
> the past in crystal.
>
>
>
> Gary Parfrey
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of nmtaylor1969
> Sent: 23 October 2008 16:01
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
>
>
>
>
> Sorry, I'm on one now Rob...!
>
> If I have my timephase dataset in a format I can crunch/manipulate,
I
> can then produce the horizontal time phase ( i.e. a report that
> groups demand into weekly or monthly buckets ), dump this back into
> Excel, and use this as the main scheduling document with all my
> vendors...
>
> 8.03.305 does not appear to produce this report out of the box
which
> is most frustrating...
>
> If we have this, then we can run just about everything via KanBan,
> and practically turn the suggestions of all together... ;o)
>
> Nick
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> Robert Brown <robertb_versa@>
> wrote:
> >
> > Nick,
> >
> > Unfortunately - No. Time Phase not being a Crystal based report
> means their is no intermediate xml source you can read into excel
as
> a datasource.
> >
> > Since the data tables themselves are non-presistent temp tables,
> you can't even do a simple odbc poke query to bring the data into
> excel.
> >
> > I think you are on to something re: Service Connect's exposure of
> the timephase BOs & methods. That would at least trigger part by
part
> population of the temp data they generate. How you would then read
> them back out into a massive single data source (xml, csv) excel
can
> leverage is beyond my knowledge level at this time.
> >
> > Is it possible to invoke Service connect processing from within
> excel (via vbScript code)? Being excel/vbScript biased, that would
be
> my first attack plan attempt.
> >
> > You are 'one clever dude' Nick Taylor... Your posts consistently
> challenge & expand my thinking on ways to make this monstrousity
work
> for us instead of the other way around.
> >
> > Thanks
> >
> > Rob
> >
> > --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@> wrote:
> >
> > From: nmtaylor1969 <n.taylor@>
> > Subject: [Vantage] Time Phase Report To Excel In 8.03.305
> > To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
> > Date: Wednesday, October 22, 2008, 3:56 PM
> >
> >
> >
> >
> >
> >
> > Has anybody found a way of getting the time phase report out to
> Excel ??
> >
> > We are struggling with managing purchasing suggestions, and this
> report
> > is useful, however Vantage produces this as a hardcopy report
> only... :o
> > (
> >
> > I know I can do this in ServiceConnect as the TimePhase
calculation
> is
> > available as a webservice, but before I run off and do that, I
> wondered
> > if anybody had found a quick way of getting it into Excel ??
> >
> > Thanks,
> >
> > Nick
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>





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

[system]

That's good stuff Charles. Thanks.

I tried writing a PartDtl based time phase when we 1st went live but found the data didn't accurately reflect reality.

Concurrently at that time, we also were experiencing a job detail generation problem that was resulting in incomplete (and unnecessary) Assembly levels in jobs that was screwing up our Allocations. This was finally fixed on 405.

It never occurred to me to go back and look at PartDtl again but I'll definitely do so.

Thanks!

Rob

--- On Sat, 10/25/08, Charles Carden <shadowcar1449@...> wrote:
From: Charles Carden <shadowcar1449@...>
Subject: Re: [Vantage] Re: Time Phase Report To Excel In 8.03.305
To: vantage@yahoogroups.com
Date: Saturday, October 25, 2008, 7:13 AM











The part detail table is updated immediately and does not require MRP to run.



It is easy enough to write a time phase in Crystal. I created a BAQ using the following tables:



Part - filtered for only Quantity Bearing Purchased Parts

PartPlant - Buyer Id and Default Supplier and MRP Parameters

PurAgent - Buyer Name

Vendor - Default Supplier ID and Name

PartDtl - Demand and Purchases

JobHead - Part Revision

SugPOChg - Purchase Order Info

PartWhse - On Hand Quantity

JobProd - Order Number if Demand comes from an Order



I provided filters by Buyer ID and Part Number. This is what the buyers are always looking for. The report can still be lengthy for a single buyer but it is much better than the thousands of pages that are printed with the standard MRP report. I was unable to show the new PO suggestions on the report because of table linkage issues. If anyone has figured out how to include the new suggestions I would be very interested in how it was done. The change suggestions table is the only one that does require MRP.



I also allowed a cutoff date option for the report since we run MRP out 9 months but the buyers are not always wanting to look that far in the future. I would liked to have provided an option to only include parts with exceptions but I couldn't figure that one out either. I did calculate an order by date for the parts that dropped below zero on the report. This was tricky because I had to look at the receive time and lead times and then had to ensure that I counted back only working days and then come up with an order date that was not on the weekend. I probably should have used the calendar file for the Vendor to come up with working days but since we only have one calendar I decided against it.



The report was sorted by Buyer ID/Part Number.



Charles Carden

IT Manager

Manitex, Inc.



----- Original Message -----

From: nmtaylor1969

To: vantage@yahoogroups .com

Sent: Saturday, October 25, 2008 1:23 AM

Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305



Thanks for that Gary, I had not spotted that, and I must say the data

in that table looks quite interesting. Its definitely the base data

for the timephase alright... :o)



Do you know if this data is updated in real-time, or does it get

updated when MRP is run...?



I had gone with my original plan and extracted the data via service

connect, and this works fine, however the data in the partdtl table

may well have lots of other uses for BAQ's and dashboards etc...



Nice one...



Many thanks,



Nick



--- In vantage@yahoogroups .com, "Gary Parfrey" <garyp@...> wrote:

>

> Nick, Rob

>

>

>

> Hope I got the whole thread. The timephase core dataset is in the

> partdtl table. We have rewritten the timephase report for

customers in

> the past in crystal.

>

>

>

> Gary Parfrey

>

>

>

> From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On

Behalf

> Of nmtaylor1969

> Sent: 23 October :01

> To: vantage@yahoogroups .com

> Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305

>

>

>

>

> Sorry, I'm on one now Rob...!

>

> If I have my timephase dataset in a format I can crunch/manipulate,

I

> can then produce the horizontal time phase ( i.e. a report that

> groups demand into weekly or monthly buckets ), dump this back into

> Excel, and use this as the main scheduling document with all my

> vendors...

>

> 8.03.305 does not appear to produce this report out of the box

which

> is most frustrating. ..

>

> If we have this, then we can run just about everything via KanBan,

> and practically turn the suggestions of all together... ;o)

>

> Nick

>

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

> Robert Brown <robertb_versa@ >

> wrote:

> >

> > Nick,

> >

> > Unfortunately - No. Time Phase not being a Crystal based report

> means their is no intermediate xml source you can read into excel

as

> a datasource.

> >

> > Since the data tables themselves are non-presistent temp tables,

> you can't even do a simple odbc poke query to bring the data into

> excel.

> >

> > I think you are on to something re: Service Connect's exposure of

> the timephase BOs & methods. That would at least trigger part by

part

> population of the temp data they generate. How you would then read

> them back out into a massive single data source (xml, csv) excel

can

> leverage is beyond my knowledge level at this time.

> >

> > Is it possible to invoke Service connect processing from within

> excel (via vbScript code)? Being excel/vbScript biased, that would

be

> my first attack plan attempt.

> >

> > You are 'one clever dude' Nick Taylor... Your posts consistently

> challenge & expand my thinking on ways to make this monstrousity

work

> for us instead of the other way around.

> >

> > Thanks

> >

> > Rob

> >

> > --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@> wrote:

> >

> > From: nmtaylor1969 <n.taylor@>

> > Subject: [Vantage] Time Phase Report To Excel In 8.03.305

> > To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups. com>

> > Date: Wednesday, October 22, 2008, 3:56 PM

> >

> >

> >

> >

> >

> >

> > Has anybody found a way of getting the time phase report out to

> Excel ??

> >

> > We are struggling with managing purchasing suggestions, and this

> report

> > is useful, however Vantage produces this as a hardcopy report

> only... :o

> > (

> >

> > I know I can do this in ServiceConnect as the TimePhase

calculation

> is

> > available as a webservice, but before I run off and do that, I

> wondered

> > if anybody had found a quick way of getting it into Excel ??

> >

> > Thanks,

> >

> > Nick

> >

>

>

>

>

>

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

>



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

[system]

There are a couple of other tables which are worth a look...

PegDmdMst ( Multi level pegging demands )
PegSupMst ( Multi level pegging supply )
PegLink ( The intermediary table that ties the above two together )

Combined, these tables permit you to interrogate the multi-level
pegging data and is useful for writing BAQ's etc. This is much easier
that having to look at things one job at a time via the multi-level
pegging display form.

I still have some more work to do on these tables so I fully
understand them, but they are certainly worth investigating...

Nick

--- In vantage@yahoogroups.com, Robert Brown <robertb_versa@...>
wrote:

>
> That's good stuff Charles. Thanks.
>
> I tried writing a PartDtl based time phase when we 1st went live

but found the data didn't accurately reflect reality.

>
> Concurrently at that time, we also were experiencing a job detail

generation problem that was resulting in incomplete (and unnecessary)
Assembly levels in jobs that was screwing up our Allocations. This
was finally fixed on 405.

>
> It never occurred to me to go back and look at PartDtl again but

I'll definitely do so.

>
> Thanks!
>
> Rob
>
> --- On Sat, 10/25/08, Charles Carden <shadowcar1449@...> wrote:
> From: Charles Carden <shadowcar1449@...>
> Subject: Re: [Vantage] Re: Time Phase Report To Excel In 8.03.305
> To: vantage@yahoogroups.com
> Date: Saturday, October 25, 2008, 7:13 AM
>
>
>
>
>
>
>
>
>
>
>
> The part detail table is updated immediately and does

not require MRP to run.

>
>
>
> It is easy enough to write a time phase in Crystal. I created a

BAQ using the following tables:

>
>
>
> Part - filtered for only Quantity Bearing Purchased Parts
>
> PartPlant - Buyer Id and Default Supplier and MRP Parameters
>
> PurAgent - Buyer Name
>
> Vendor - Default Supplier ID and Name
>
> PartDtl - Demand and Purchases
>
> JobHead - Part Revision
>
> SugPOChg - Purchase Order Info
>
> PartWhse - On Hand Quantity
>
> JobProd - Order Number if Demand comes from an Order
>
>
>
> I provided filters by Buyer ID and Part Number. This is what the

buyers are always looking for. The report can still be lengthy for a
single buyer but it is much better than the thousands of pages that
are printed with the standard MRP report. I was unable to show the
new PO suggestions on the report because of table linkage issues. If
anyone has figured out how to include the new suggestions I would be
very interested in how it was done. The change suggestions table is
the only one that does require MRP.

>
>
>
> I also allowed a cutoff date option for the report since we run MRP

out 9 months but the buyers are not always wanting to look that far
in the future. I would liked to have provided an option to only
include parts with exceptions but I couldn't figure that one out
either. I did calculate an order by date for the parts that dropped
below zero on the report. This was tricky because I had to look at
the receive time and lead times and then had to ensure that I counted
back only working days and then come up with an order date that was
not on the weekend. I probably should have used the calendar file
for the Vendor to come up with working days but since we only have
one calendar I decided against it.

>
>
>
> The report was sorted by Buyer ID/Part Number.
>
>
>
> Charles Carden
>
> IT Manager
>
> Manitex, Inc.
>
>
>
> ----- Original Message -----
>
> From: nmtaylor1969
>
> To: vantage@yahoogroups .com
>
> Sent: Saturday, October 25, 2008 1:23 AM
>
> Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
>
>
>
> Thanks for that Gary, I had not spotted that, and I must say the

data

>
> in that table looks quite interesting. Its definitely the base

data

>
> for the timephase alright... :o)
>
>
>
> Do you know if this data is updated in real-time, or does it get
>
> updated when MRP is run...?
>
>
>
> I had gone with my original plan and extracted the data via service
>
> connect, and this works fine, however the data in the partdtl

table

>
> may well have lots of other uses for BAQ's and dashboards etc...
>
>
>
> Nice one...
>
>
>
> Many thanks,
>
>
>
> Nick
>
>
>
> --- In vantage@yahoogroups .com, "Gary Parfrey" <garyp@> wrote:
>
> >
>
> > Nick, Rob
>
> >
>
> >
>
> >
>
> > Hope I got the whole thread. The timephase core dataset is in

the

>
> > partdtl table. We have rewritten the timephase report for
>
> customers in
>
> > the past in crystal.
>
> >
>
> >
>
> >
>
> > Gary Parfrey
>
> >
>
> >
>
> >
>
> > From: vantage@yahoogroups .com

[mailto:vantage@yahoogroups .com] On

>
> Behalf
>
> > Of nmtaylor1969
>
> > Sent: 23 October :01
>
> > To: vantage@yahoogroups .com
>
> > Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
>
> >
>
> >
>
> >
>
> >
>
> > Sorry, I'm on one now Rob...!
>
> >
>
> > If I have my timephase dataset in a format I can

crunch/manipulate,

>
> I
>
> > can then produce the horizontal time phase ( i.e. a report that
>
> > groups demand into weekly or monthly buckets ), dump this back

into

>
> > Excel, and use this as the main scheduling document with all my
>
> > vendors...
>
> >
>
> > 8.03.305 does not appear to produce this report out of the box
>
> which
>
> > is most frustrating. ..
>
> >
>
> > If we have this, then we can run just about everything via

KanBan,

>
> > and practically turn the suggestions of all together... ;o)
>
> >
>
> > Nick
>
> >
>
> > --- In vantage@yahoogroups .com <mailto:vantage% 40yahoogroups.

com> ,

>
> > Robert Brown <robertb_versa@ >
>
> > wrote:
>
> > >
>
> > > Nick,
>
> > >
>
> > > Unfortunately - No. Time Phase not being a Crystal based

report

>
> > means their is no intermediate xml source you can read into

excel

>
> as
>
> > a datasource.
>
> > >
>
> > > Since the data tables themselves are non-presistent temp

tables,

>
> > you can't even do a simple odbc poke query to bring the data

into

>
> > excel.
>
> > >
>
> > > I think you are on to something re: Service Connect's

exposure of

>
> > the timephase BOs & methods. That would at least trigger part

by

>
> part
>
> > population of the temp data they generate. How you would then

read

>
> > them back out into a massive single data source (xml, csv)

excel

>
> can
>
> > leverage is beyond my knowledge level at this time.
>
> > >
>
> > > Is it possible to invoke Service connect processing from

within

>
> > excel (via vbScript code)? Being excel/vbScript biased, that

would

>
> be
>
> > my first attack plan attempt.
>
> > >
>
> > > You are 'one clever dude' Nick Taylor... Your posts

consistently

>
> > challenge & expand my thinking on ways to make this

monstrousity

>
> work
>
> > for us instead of the other way around.
>
> > >
>
> > > Thanks
>
> > >
>
> > > Rob
>
> > >
>
> > > --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@> wrote:
>
> > >
>
> > > From: nmtaylor1969 <n.taylor@>
>
> > > Subject: [Vantage] Time Phase Report To Excel In 8.03.305
>
> > > To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups.

com>

>
> > > Date: Wednesday, October 22, 2008, 3:56 PM
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > >
>
> > > Has anybody found a way of getting the time phase report out

to

>
> > Excel ??
>
> > >
>
> > > We are struggling with managing purchasing suggestions, and

this

>
> > report
>
> > > is useful, however Vantage produces this as a hardcopy report
>
> > only... :o
>
> > > (
>
> > >
>
> > > I know I can do this in ServiceConnect as the TimePhase
>
> calculation
>
> > is
>
> > > available as a webservice, but before I run off and do that,

I

>
> > wondered
>
> > > if anybody had found a quick way of getting it into Excel ??
>
> > >
>
> > > Thanks,
>
> > >
>
> > > Nick
>
> > >
>
> >
>
> >
>
> >
>
> >
>
> >
>
> > [Non-text portions of this message have been removed]
>
> >
>
>
>
> [Non-text portions of this message have been removed]
>

[system]

Nick,

Be careful with the Peg tables. Last I looked, they are static (MRP populated).

They also show only demand/supply that is dependent upon one another. (Every order & Job requirement is not necessarily listed. Nor is every supply PO or Job).

Unfortunately, it is not a 'pure' dependency as it treats your Min O/H and/or Safety as an immediate requirement today (and thus shows a lot more dependent supply/demand than really exists from the perspective of being to release 'clear to build' jobs or direct sales order requirements).

With S/C, (after your normal Net or full regen MRP) you could capture all current part Min O/H & Safeties, temporarily store them, punch back in zeroes for everything, run Pegging (as an individual process) and then restore the Mins/Safeties with S/C.

That would give you valuable Peg data (that is untainted by Mins/Safeties).

...Food for thought. (Go man Go!)


Rob

--- On Sun, 10/26/08, nmtaylor1969 <n.taylor@...> wrote:
From: nmtaylor1969 <n.taylor@...>
Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
To: vantage@yahoogroups.com
Date: Sunday, October 26, 2008, 4:49 AM











There are a couple of other tables which are worth a look...



PegDmdMst ( Multi level pegging demands )

PegSupMst ( Multi level pegging supply )

PegLink ( The intermediary table that ties the above two together )



Combined, these tables permit you to interrogate the multi-level

pegging data and is useful for writing BAQ's etc. This is much easier

that having to look at things one job at a time via the multi-level

pegging display form.



I still have some more work to do on these tables so I fully

understand them, but they are certainly worth investigating. ..



Nick



--- In vantage@yahoogroups .com, Robert Brown <robertb_versa@ ...>

wrote:

>

> That's good stuff Charles. Thanks.

>

> I tried writing a PartDtl based time phase when we 1st went live

but found the data didn't accurately reflect reality.

>

> Concurrently at that time, we also were experiencing a job detail

generation problem that was resulting in incomplete (and unnecessary)

Assembly levels in jobs that was screwing up our Allocations. This

was finally fixed on 405.

>

> It never occurred to me to go back and look at PartDtl again but

I'll definitely do so.

>

> Thanks!

>

> Rob

>

> --- On Sat, 10/25/08, Charles Carden <shadowcar1449@ ...> wrote:

> From: Charles Carden <shadowcar1449@ ...>

> Subject: Re: [Vantage] Re: Time Phase Report To Excel In 8.03.305

> To: vantage@yahoogroups .com

> Date: Saturday, October 25, 2008, 7:13 AM

>

>

>

>

>

>

>

>

>

>

>

> The part detail table is updated immediately and does

not require MRP to run.

>

>

>

> It is easy enough to write a time phase in Crystal. I created a

BAQ using the following tables:

>

>

>

> Part - filtered for only Quantity Bearing Purchased Parts

>

> PartPlant - Buyer Id and Default Supplier and MRP Parameters

>

> PurAgent - Buyer Name

>

> Vendor - Default Supplier ID and Name

>

> PartDtl - Demand and Purchases

>

> JobHead - Part Revision

>

> SugPOChg - Purchase Order Info

>

> PartWhse - On Hand Quantity

>

> JobProd - Order Number if Demand comes from an Order

>

>

>

> I provided filters by Buyer ID and Part Number. This is what the

buyers are always looking for. The report can still be lengthy for a

single buyer but it is much better than the thousands of pages that

are printed with the standard MRP report. I was unable to show the

new PO suggestions on the report because of table linkage issues. If

anyone has figured out how to include the new suggestions I would be

very interested in how it was done. The change suggestions table is

the only one that does require MRP.

>

>

>

> I also allowed a cutoff date option for the report since we run MRP

out 9 months but the buyers are not always wanting to look that far

in the future. I would liked to have provided an option to only

include parts with exceptions but I couldn't figure that one out

either. I did calculate an order by date for the parts that dropped

below zero on the report. This was tricky because I had to look at

the receive time and lead times and then had to ensure that I counted

back only working days and then come up with an order date that was

not on the weekend. I probably should have used the calendar file

for the Vendor to come up with working days but since we only have

one calendar I decided against it.

>

>

>

> The report was sorted by Buyer ID/Part Number.

>

>

>

> Charles Carden

>

> IT Manager

>

> Manitex, Inc.

>

>

>

> ----- Original Message -----

>

> From: nmtaylor1969

>

> To: vantage@yahoogroups .com

>

> Sent: Saturday, October 25, 2008 1:23 AM

>

> Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305

>

>

>

> Thanks for that Gary, I had not spotted that, and I must say the

data

>

> in that table looks quite interesting. Its definitely the base

data

>

> for the timephase alright... :o)

>

>

>

> Do you know if this data is updated in real-time, or does it get

>

> updated when MRP is run...?

>

>

>

> I had gone with my original plan and extracted the data via service

>

> connect, and this works fine, however the data in the partdtl

table

>

> may well have lots of other uses for BAQ's and dashboards etc...

>

>

>

> Nice one...

>

>

>

> Many thanks,

>

>

>

> Nick

>

>

>

> --- In vantage@yahoogroups .com, "Gary Parfrey" <garyp@> wrote:

>

> >

>

> > Nick, Rob

>

> >

>

> >

>

> >

>

> > Hope I got the whole thread. The timephase core dataset is in

the

>

> > partdtl table. We have rewritten the timephase report for

>

> customers in

>

> > the past in crystal.

>

> >

>

> >

>

> >

>

> > Gary Parfrey

>

> >

>

> >

>

> >

>

> > From: vantage@yahoogroups .com

[mailto:vantage@ yahoogroups .com] On

>

> Behalf

>

> > Of nmtaylor1969

>

> > Sent: 23 October :01

>

> > To: vantage@yahoogroups .com

>

> > Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305

>

> >

>

> >

>

> >

>

> >

>

> > Sorry, I'm on one now Rob...!

>

> >

>

> > If I have my timephase dataset in a format I can

crunch/manipulate,

>

> I

>

> > can then produce the horizontal time phase ( i.e. a report that

>

> > groups demand into weekly or monthly buckets ), dump this back

into

>

> > Excel, and use this as the main scheduling document with all my

>

> > vendors...

>

> >

>

> > 8.03.305 does not appear to produce this report out of the box

>

> which

>

> > is most frustrating. ..

>

> >

>

> > If we have this, then we can run just about everything via

KanBan,

>

> > and practically turn the suggestions of all together... ;o)

>

> >

>

> > Nick

>

> >

>

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

com> ,

>

> > Robert Brown <robertb_versa@ >

>

> > wrote:

>

> > >

>

> > > Nick,

>

> > >

>

> > > Unfortunately - No. Time Phase not being a Crystal based

report

>

> > means their is no intermediate xml source you can read into

excel

>

> as

>

> > a datasource.

>

> > >

>

> > > Since the data tables themselves are non-presistent temp

tables,

>

> > you can't even do a simple odbc poke query to bring the data

into

>

> > excel.

>

> > >

>

> > > I think you are on to something re: Service Connect's

exposure of

>

> > the timephase BOs & methods. That would at least trigger part

by

>

> part

>

> > population of the temp data they generate. How you would then

read

>

> > them back out into a massive single data source (xml, csv)

excel

>

> can

>

> > leverage is beyond my knowledge level at this time.

>

> > >

>

> > > Is it possible to invoke Service connect processing from

within

>

> > excel (via vbScript code)? Being excel/vbScript biased, that

would

>

> be

>

> > my first attack plan attempt.

>

> > >

>

> > > You are 'one clever dude' Nick Taylor... Your posts

consistently

>

> > challenge & expand my thinking on ways to make this

monstrousity

>

> work

>

> > for us instead of the other way around.

>

> > >

>

> > > Thanks

>

> > >

>

> > > Rob

>

> > >

>

> > > --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@> wrote:

>

> > >

>

> > > From: nmtaylor1969 <n.taylor@>

>

> > > Subject: [Vantage] Time Phase Report To Excel In 8.03.305

>

> > > To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups.

com>

>

> > > Date: Wednesday, October 22, 2008, 3:56 PM

>

> > >

>


> > >

>

> > >

>

> > >

>

> > >

>

> > >

>

> > > Has anybody found a way of getting the time phase report out

to

>

> > Excel ??

>

> > >

>

> > > We are struggling with managing purchasing suggestions, and

this

>

> > report

>

> > > is useful, however Vantage produces this as a hardcopy report

>

> > only... :o

>

> > > (

>

> > >

>

> > > I know I can do this in ServiceConnect as the TimePhase

>

> calculation

>

> > is

>

> > > available as a webservice, but before I run off and do that,

I

>

> > wondered

>

> > > if anybody had found a quick way of getting it into Excel ??

>

> > >

>

> > > Thanks,

>

> > >

>

> > > Nick

>

> > >

>

> >

>

> >

>

> >

>

> >

>

> >

>

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

>

> >

>

>

>

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

>

[system]

Oh yeah: Charles is right: I recall when I initially looked at all these tables and prewrote BAQs I found significant limitations to being able to integrate all the desired data tables into a single massive BAQ.

The current BAQ limit of only being able to add a table once to the query (rather than several times linked to specific branches of the table relationships you are trying to pull in) is a significant issue. Part, PartPlant & PartWhse are the tables you'd probably want to have in the query in multiple instances (to pull in O/H, allocation & planning field data) joined at the 'end' of each query join branch.

FYI: My V9 beta 'spy' tells me this limit is gone in v9. (If done right, BAQs then will be on an equal footing with std SQL as a query tool. The only thing lacking will be the ability to do recursive "Do While" subqueries within a single query.)

Also: Even if you sacrifice the Part related tables, the fields in Peg & PartDtl that you need to Join through are not all indexed fields - so the query can run lumberingly slow.

My solution was to break up the single query into individual queries for Part OH & planning field data, PO supply, Job supply, Job demand & Order demand.

These (still are) set up in our system for recurring export to csv (much smaller files than xml) at 4am and then an Auto_open vbscripted excel file is task scheduled to open and automatically pull everything from the csv files in and integrate it via vlookup() formulas, etc.,.

The plan was to then create my own messaging (based on rules pertinent to us) and also use it as an aid to releasing jobs.

With Charles' reminder, I'll be looking at this again this coming week.

I'll let you know what I find.

Rob

--- On Sun, 10/26/08, nmtaylor1969 <n.taylor@...> wrote:
From: nmtaylor1969 <n.taylor@...>
Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305
To: vantage@yahoogroups.com
Date: Sunday, October 26, 2008, 4:49 AM











There are a couple of other tables which are worth a look...



PegDmdMst ( Multi level pegging demands )

PegSupMst ( Multi level pegging supply )

PegLink ( The intermediary table that ties the above two together )



Combined, these tables permit you to interrogate the multi-level

pegging data and is useful for writing BAQ's etc. This is much easier

that having to look at things one job at a time via the multi-level

pegging display form.



I still have some more work to do on these tables so I fully

understand them, but they are certainly worth investigating. ..



Nick



--- In vantage@yahoogroups .com, Robert Brown <robertb_versa@ ...>

wrote:

>

> That's good stuff Charles. Thanks.

>

> I tried writing a PartDtl based time phase when we 1st went live

but found the data didn't accurately reflect reality.

>

> Concurrently at that time, we also were experiencing a job detail

generation problem that was resulting in incomplete (and unnecessary)

Assembly levels in jobs that was screwing up our Allocations. This

was finally fixed on 405.

>

> It never occurred to me to go back and look at PartDtl again but

I'll definitely do so.

>

> Thanks!

>

> Rob

>

> --- On Sat, 10/25/08, Charles Carden <shadowcar1449@ ...> wrote:

> From: Charles Carden <shadowcar1449@ ...>

> Subject: Re: [Vantage] Re: Time Phase Report To Excel In 8.03.305

> To: vantage@yahoogroups .com

> Date: Saturday, October 25, 2008, 7:13 AM

>

>

>

>

>

>

>

>

>

>

>

> The part detail table is updated immediately and does

not require MRP to run.

>

>

>

> It is easy enough to write a time phase in Crystal. I created a

BAQ using the following tables:

>

>

>

> Part - filtered for only Quantity Bearing Purchased Parts

>

> PartPlant - Buyer Id and Default Supplier and MRP Parameters

>

> PurAgent - Buyer Name

>

> Vendor - Default Supplier ID and Name

>

> PartDtl - Demand and Purchases

>

> JobHead - Part Revision

>

> SugPOChg - Purchase Order Info

>

> PartWhse - On Hand Quantity

>

> JobProd - Order Number if Demand comes from an Order

>

>

>

> I provided filters by Buyer ID and Part Number. This is what the

buyers are always looking for. The report can still be lengthy for a

single buyer but it is much better than the thousands of pages that

are printed with the standard MRP report. I was unable to show the

new PO suggestions on the report because of table linkage issues. If

anyone has figured out how to include the new suggestions I would be

very interested in how it was done. The change suggestions table is

the only one that does require MRP.

>

>

>

> I also allowed a cutoff date option for the report since we run MRP

out 9 months but the buyers are not always wanting to look that far

in the future. I would liked to have provided an option to only

include parts with exceptions but I couldn't figure that one out

either. I did calculate an order by date for the parts that dropped

below zero on the report. This was tricky because I had to look at

the receive time and lead times and then had to ensure that I counted

back only working days and then come up with an order date that was

not on the weekend. I probably should have used the calendar file

for the Vendor to come up with working days but since we only have

one calendar I decided against it.

>

>

>

> The report was sorted by Buyer ID/Part Number.

>

>

>

> Charles Carden

>

> IT Manager

>

> Manitex, Inc.

>

>

>

> ----- Original Message -----

>

> From: nmtaylor1969

>

> To: vantage@yahoogroups .com

>

> Sent: Saturday, October 25, 2008 1:23 AM

>

> Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305

>

>

>

> Thanks for that Gary, I had not spotted that, and I must say the

data

>

> in that table looks quite interesting. Its definitely the base

data

>

> for the timephase alright... :o)

>

>

>

> Do you know if this data is updated in real-time, or does it get

>

> updated when MRP is run...?

>

>

>

> I had gone with my original plan and extracted the data via service

>

> connect, and this works fine, however the data in the partdtl

table

>

> may well have lots of other uses for BAQ's and dashboards etc...

>

>

>

> Nice one...

>

>

>

> Many thanks,

>

>

>

> Nick

>

>

>

> --- In vantage@yahoogroups .com, "Gary Parfrey" <garyp@> wrote:

>

> >

>

> > Nick, Rob

>

> >

>

> >

>

> >

>

> > Hope I got the whole thread. The timephase core dataset is in

the

>

> > partdtl table. We have rewritten the timephase report for

>

> customers in

>

> > the past in crystal.

>

> >

>

> >

>

> >

>

> > Gary Parfrey

>

> >

>

> >

>

> >

>

> > From: vantage@yahoogroups .com

[mailto:vantage@ yahoogroups .com] On

>

> Behalf

>

> > Of nmtaylor1969

>

> > Sent: 23 October :01

>

> > To: vantage@yahoogroups .com

>

> > Subject: [Vantage] Re: Time Phase Report To Excel In 8.03.305

>

> >

>

> >

>

> >

>

> >

>

> > Sorry, I'm on one now Rob...!

>

> >

>

> > If I have my timephase dataset in a format I can

crunch/manipulate,

>

> I

>

> > can then produce the horizontal time phase ( i.e. a report that

>

> > groups demand into weekly or monthly buckets ), dump this back

into

>

> > Excel, and use this as the main scheduling document with all my

>

> > vendors...

>

> >

>

> > 8.03.305 does not appear to produce this report out of the box

>

> which

>

> > is most frustrating. ..

>

> >

>

> > If we have this, then we can run just about everything via

KanBan,

>

> > and practically turn the suggestions of all together... ;o)

>

> >

>

> > Nick

>

> >

>

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

com> ,

>

> > Robert Brown <robertb_versa@ >

>

> > wrote:

>

> > >

>

> > > Nick,

>

> > >

>

> > > Unfortunately - No. Time Phase not being a Crystal based

report

>

> > means their is no intermediate xml source you can read into

excel

>

> as

>

> > a datasource.

>

> > >

>

> > > Since the data tables themselves are non-presistent temp

tables,

>

> > you can't even do a simple odbc poke query to bring the data

into

>

> > excel.

>

> > >

>

> > > I think you are on to something re: Service Connect's

exposure of

>

> > the timephase BOs & methods. That would at least trigger part

by

>

> part

>

> > population of the temp data they generate. How you would then

read

>

> > them back out into a massive single data source (xml, csv)

excel

>

> can

>

> > leverage is beyond my knowledge level at this time.

>

> > >

>

> > > Is it possible to invoke Service connect processing from

within

>

> > excel (via vbScript code)? Being excel/vbScript biased, that

would

>

> be

>

> > my first attack plan attempt.

>

> > >

>

> > > You are 'one clever dude' Nick Taylor... Your posts

consistently

>

> > challenge & expand my thinking on ways to make this

monstrousity

>

> work

>

> > for us instead of the other way around.

>

> > >

>

> > > Thanks

>

> > >

>

> > > Rob

>

> > >

>

> > > --- On Wed, 10/22/08, nmtaylor1969 <n.taylor@> wrote:

>

> > >

>

> > > From: nmtaylor1969 <n.taylor@>

>

> > > Subject: [Vantage] Time Phase Report To Excel In 8.03.305

>

> > > To: vantage@yahoogroups .com <mailto:vantage% 40yahoogroups.

com>

>

> > > Date: Wednesday, October 22, 2008, 3:56 PM

>

> > >

>


> > >

>

> > >

>

> > >

>

> > >

>

> > >

>

> > > Has anybody found a way of getting the time phase report out

to

>

> > Excel ??

>

> > >

>

> > > We are struggling with managing purchasing suggestions, and

this

>

> > report

>

> > > is useful, however Vantage produces this as a hardcopy report

>

> > only... :o

>

> > > (

>

> > >

>

> > > I know I can do this in ServiceConnect as the TimePhase

>

> calculation

>

> > is

>

> > > available as a webservice, but before I run off and do that,

I

>

> > wondered

>

> > > if anybody had found a quick way of getting it into Excel ??

>

> > >

>

> > > Thanks,

>

> > >

>

> > > Nick

>

> > >

>

> >

>

> >

>

> >

>

> >

>

> >

>

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

>

> >

>

>

>

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

>