Historical Sales Order Backlog Report

Yahoo Archive
1
These issues (need to take snapshots of transaction processing systems
for trending, analysis, and ad hoc reporting) are why the IT trade mags
are full of articles about BI, DW, column-oriented data stores, and why
this area is a multibillion-dollar industry.

I would beat Epicor up too much on this -- its not the ERP system's
job to provide much support in this area (at least that's the way the
market divides this area up, and it makes sense to me). That said, the
ShopVision stuff that's included in Vantage sucks. Pieces of it were
broken in 405 and are still broken in 408B. That's definitely Epicor's
fault, and it makes me hate them a little. :-)

You're going to definitely need to maintain the data elsewhere if you
want to be able to ask questions like "what was the sales order backlog
like at the end of the month for the past 12 months" and "how will the
trends with our customers' ship-date changes affect my anticipated
quarterly performance".

-bws

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of laraines422
Sent: Thursday, July 08, 2010 4:11 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Historical Sales Order Backlog Report

Hi Brian,

Thanks for the input. You've really articulated the issue!

Another Vantage user has shared his Excel files with me - I need to look
at them and see if they'll work. I usually try to avoid Excel as a
deployed user solution, but in this case, it might be the quick and
dirty answer.

On the data warehouse and BI issue - we actually have CorVu, but I
believe that the previous ERP administrator hadn't deployed it, and I
have zero clue as to its workings; I guess I'll have to weigh the
learning curve to the benefits gained. And I used PivotLink by SeaTab
in my last job - it's really slick and yes, it is expensive.

We do have a SQL server available to us - but I hate the idea of
maintaining the same data in two different locations just for reporting
purposes - unless, like you state, it's a data warehouse. I'm not sure
how CorVu needs to look at the data - but if it needs to be ported to
some other database outside of our Production Progress - then we're back
to your original point. ARGH.

Thanks again, you've obviously thought about this one a bit.
Laraine



--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...>
wrote:
>
> In short, there's no way to do this in directly Vantage, because
> Vantage is an ERP system, and not a Data Warehouse. I've wanted to do
> this as well. Here's my perspective, which is probably making things
> more complicated than they need to be, but I think I'm being fairly
> complete and accurate here.
>
> Generally ERP systems are focused on transaction processing,
handling
> the entire 'quote to cash' cycle. Historical records are only
generally
> accessible for things that represent 'transactions', like moving parts
> in and out of inventory, things that touch the General Ledger, etc.
You
> have tables like PartTran, GLJrnDtl, etc. for those, and you can
> generate reports that show those areas of the system at a certain
point
> of time either using the current starting point and working backwards,
> or using intermediate data (e.g. GLOpnBal and GLPerBal) to do
historical
> reporting.
>
> So most tables in Vantage don't have transaction histories
associated
> with them -- they just represent the system in its current state.
>
> Vantage does provide a Change Logging capability that generically
> writes data to the ChgLog table based on BAM change action
> configurations (i.e. write a log record whenever something on the
> OrderHed table changes), but the kind of reporting you can do from
that
> is pretty basic and not suitable for your purposes.
>
> I did write a BPM package to do some logging of the kind of date
> information you're talking about, but I'm just appending ChgLog-style
> messages to a Character field in the OrderRel table. This gives my
> planners some visibility when order dates are changing, but you can't
> report off it except to show the log messages.
>
> So what you really need is a Data Warehouse, which is a database
> designed to store information from a transaction processing system at
a
> given point in time, to provide visibility for trending and analysis.
>
> Epicor gives you two options out of the box for this kind of
> reporting: CorVu for Vantage 8, and the rebranded Microsoft Business
> Intelligence tool for Epicor 9. Both are expensive, and folks here
have
> nothing good to say about CorVu.
>
> One approach is to do this manually. You could schedule a BAQ that
> exports its data on a daily or weekly basis that provides a snapshot
of
> the backlog (OrderRel with related info, including key dates), and
then
> post-process that data in Excel or other tool of your choice. That
> would require some programming and wouldn't necessarily be a very
> flexible solution.
>
> Another approach is to build a data warehouse that slurps all of
this
> data in and stores it along with a datestamp. You can build reports
on
> top of that using your favorite reporting tool. I bought the Kimball
> Data Warehouse bible and quickly realized this is way over my head and
> ability to implement.
>
> There are some on-demand BI solutions as well from GoodData.com and
> PivotLink.com that might be worth looking at. The amount of data to
> keep track of the backlog over time for most of the folks here is
> probably rather modest and could easily fit in the first-tier plans of
> these providers.
>
> -bws
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of laraines422
> Sent: Thursday, July 08, 2010 1:23 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Historical Sales Order Backlog Report
>
> Hi All,
>
> Has anyone had to recreate a Sales Order Backlog Report from a
> historical perspective? I've been tasked to look at our PAST orders -
> and evaluate what their status was in any given period, so that we can
> look at our trending (yes, it would have been easier to have taken
> snapshots of the Sales Order Backlog report, but we didn't have a
Sales
> Manager then! Guess what report I'm planning to schedule to
> automatically generate at the end of month?).
>
> For our purposes, I know that the key fields are Order Date, Ship Date
> and Need By Date - which then will be driven off a selected period or
> date. I've started to look at the data in Crystal Reports with ODBC -
> but I thought that I'd take a chance and see if anyone had already
done
> this. I've mapped out on paper some =, > and < conditions based on
the
> designated period or date for "Backlog" and "Not Backlog", so I have a
> basic overview of the logic.
>
> I did something similar in a previous lifetime (a historical Stock
> Status Report) - and that was a bear! It never seemed accurate
either,
> with subtracting receipts, adding and subtracting other inventory
> transactions, etc.
>
> Thanks in advance -
> Laraine
> (Vantage 407c with Progress)
>
>
>
> ------------------------------------
>
> 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
>




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

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
2
Hi All,

Has anyone had to recreate a Sales Order Backlog Report from a historical perspective? I've been tasked to look at our PAST orders – and evaluate what their status was in any given period, so that we can look at our trending (yes, it would have been easier to have taken snapshots of the Sales Order Backlog report, but we didn't have a Sales Manager then! Guess what report I'm planning to schedule to automatically generate at the end of month?).

For our purposes, I know that the key fields are Order Date, Ship Date and Need By Date – which then will be driven off a selected period or date. I've started to look at the data in Crystal Reports with ODBC – but I thought that I'd take a chance and see if anyone had already done this. I've mapped out on paper some =, > and < conditions based on the designated period or date for "Backlog" and "Not Backlog", so I have a basic overview of the logic.

I did something similar in a previous lifetime (a historical Stock Status Report) – and that was a bear! It never seemed accurate either, with subtracting receipts, adding and subtracting other inventory transactions, etc.

Thanks in advance –
Laraine
(Vantage 407c with Progress)
3
In short, there's no way to do this in directly Vantage, because
Vantage is an ERP system, and not a Data Warehouse. I've wanted to do
this as well. Here's my perspective, which is probably making things
more complicated than they need to be, but I think I'm being fairly
complete and accurate here.

Generally ERP systems are focused on transaction processing, handling
the entire 'quote to cash' cycle. Historical records are only generally
accessible for things that represent 'transactions', like moving parts
in and out of inventory, things that touch the General Ledger, etc. You
have tables like PartTran, GLJrnDtl, etc. for those, and you can
generate reports that show those areas of the system at a certain point
of time either using the current starting point and working backwards,
or using intermediate data (e.g. GLOpnBal and GLPerBal) to do historical
reporting.

So most tables in Vantage don't have transaction histories associated
with them -- they just represent the system in its current state.

Vantage does provide a Change Logging capability that generically
writes data to the ChgLog table based on BAM change action
configurations (i.e. write a log record whenever something on the
OrderHed table changes), but the kind of reporting you can do from that
is pretty basic and not suitable for your purposes.

I did write a BPM package to do some logging of the kind of date
information you're talking about, but I'm just appending ChgLog-style
messages to a Character field in the OrderRel table. This gives my
planners some visibility when order dates are changing, but you can't
report off it except to show the log messages.

So what you really need is a Data Warehouse, which is a database
designed to store information from a transaction processing system at a
given point in time, to provide visibility for trending and analysis.

Epicor gives you two options out of the box for this kind of
reporting: CorVu for Vantage 8, and the rebranded Microsoft Business
Intelligence tool for Epicor 9. Both are expensive, and folks here have
nothing good to say about CorVu.

One approach is to do this manually. You could schedule a BAQ that
exports its data on a daily or weekly basis that provides a snapshot of
the backlog (OrderRel with related info, including key dates), and then
post-process that data in Excel or other tool of your choice. That
would require some programming and wouldn't necessarily be a very
flexible solution.

Another approach is to build a data warehouse that slurps all of this
data in and stores it along with a datestamp. You can build reports on
top of that using your favorite reporting tool. I bought the Kimball
Data Warehouse bible and quickly realized this is way over my head and
ability to implement.

There are some on-demand BI solutions as well from GoodData.com and
PivotLink.com that might be worth looking at. The amount of data to
keep track of the backlog over time for most of the folks here is
probably rather modest and could easily fit in the first-tier plans of
these providers.

-bws

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of laraines422
Sent: Thursday, July 08, 2010 1:23 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Historical Sales Order Backlog Report

Hi All,

Has anyone had to recreate a Sales Order Backlog Report from a
historical perspective? I've been tasked to look at our PAST orders -
and evaluate what their status was in any given period, so that we can
look at our trending (yes, it would have been easier to have taken
snapshots of the Sales Order Backlog report, but we didn't have a Sales
Manager then! Guess what report I'm planning to schedule to
automatically generate at the end of month?).

For our purposes, I know that the key fields are Order Date, Ship Date
and Need By Date - which then will be driven off a selected period or
date. I've started to look at the data in Crystal Reports with ODBC -
but I thought that I'd take a chance and see if anyone had already done
this. I've mapped out on paper some =, > and < conditions based on the
designated period or date for "Backlog" and "Not Backlog", so I have a
basic overview of the logic.

I did something similar in a previous lifetime (a historical Stock
Status Report) - and that was a bear! It never seemed accurate either,
with subtracting receipts, adding and subtracting other inventory
transactions, etc.

Thanks in advance -
Laraine
(Vantage 407c with Progress)



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

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
4
Hi Brian,

Thanks for the input. You've really articulated the issue!

Another Vantage user has shared his Excel files with me - I need to look at them and see if they'll work. I usually try to avoid Excel as a deployed user solution, but in this case, it might be the quick and dirty answer.

On the data warehouse and BI issue - we actually have CorVu, but I believe that the previous ERP administrator hadn't deployed it, and I have zero clue as to its workings; I guess I'll have to weigh the learning curve to the benefits gained. And I used PivotLink by SeaTab in my last job - it's really slick and yes, it is expensive.

We do have a SQL server available to us - but I hate the idea of maintaining the same data in two different locations just for reporting purposes - unless, like you state, it's a data warehouse. I'm not sure how CorVu needs to look at the data - but if it needs to be ported to some other database outside of our Production Progress - then we're back to your original point. ARGH.

Thanks again, you've obviously thought about this one a bit.
Laraine



--- In vantage@yahoogroups.com, "Brian W. Spolarich " <bspolarich@...> wrote:
>
> In short, there's no way to do this in directly Vantage, because
> Vantage is an ERP system, and not a Data Warehouse. I've wanted to do
> this as well. Here's my perspective, which is probably making things
> more complicated than they need to be, but I think I'm being fairly
> complete and accurate here.
>
> Generally ERP systems are focused on transaction processing, handling
> the entire 'quote to cash' cycle. Historical records are only generally
> accessible for things that represent 'transactions', like moving parts
> in and out of inventory, things that touch the General Ledger, etc. You
> have tables like PartTran, GLJrnDtl, etc. for those, and you can
> generate reports that show those areas of the system at a certain point
> of time either using the current starting point and working backwards,
> or using intermediate data (e.g. GLOpnBal and GLPerBal) to do historical
> reporting.
>
> So most tables in Vantage don't have transaction histories associated
> with them -- they just represent the system in its current state.
>
> Vantage does provide a Change Logging capability that generically
> writes data to the ChgLog table based on BAM change action
> configurations (i.e. write a log record whenever something on the
> OrderHed table changes), but the kind of reporting you can do from that
> is pretty basic and not suitable for your purposes.
>
> I did write a BPM package to do some logging of the kind of date
> information you're talking about, but I'm just appending ChgLog-style
> messages to a Character field in the OrderRel table. This gives my
> planners some visibility when order dates are changing, but you can't
> report off it except to show the log messages.
>
> So what you really need is a Data Warehouse, which is a database
> designed to store information from a transaction processing system at a
> given point in time, to provide visibility for trending and analysis.
>
> Epicor gives you two options out of the box for this kind of
> reporting: CorVu for Vantage 8, and the rebranded Microsoft Business
> Intelligence tool for Epicor 9. Both are expensive, and folks here have
> nothing good to say about CorVu.
>
> One approach is to do this manually. You could schedule a BAQ that
> exports its data on a daily or weekly basis that provides a snapshot of
> the backlog (OrderRel with related info, including key dates), and then
> post-process that data in Excel or other tool of your choice. That
> would require some programming and wouldn't necessarily be a very
> flexible solution.
>
> Another approach is to build a data warehouse that slurps all of this
> data in and stores it along with a datestamp. You can build reports on
> top of that using your favorite reporting tool. I bought the Kimball
> Data Warehouse bible and quickly realized this is way over my head and
> ability to implement.
>
> There are some on-demand BI solutions as well from GoodData.com and
> PivotLink.com that might be worth looking at. The amount of data to
> keep track of the backlog over time for most of the folks here is
> probably rather modest and could easily fit in the first-tier plans of
> these providers.
>
> -bws
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of laraines422
> Sent: Thursday, July 08, 2010 1:23 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Historical Sales Order Backlog Report
>
> Hi All,
>
> Has anyone had to recreate a Sales Order Backlog Report from a
> historical perspective? I've been tasked to look at our PAST orders -
> and evaluate what their status was in any given period, so that we can
> look at our trending (yes, it would have been easier to have taken
> snapshots of the Sales Order Backlog report, but we didn't have a Sales
> Manager then! Guess what report I'm planning to schedule to
> automatically generate at the end of month?).
>
> For our purposes, I know that the key fields are Order Date, Ship Date
> and Need By Date - which then will be driven off a selected period or
> date. I've started to look at the data in Crystal Reports with ODBC -
> but I thought that I'd take a chance and see if anyone had already done
> this. I've mapped out on paper some =, > and < conditions based on the
> designated period or date for "Backlog" and "Not Backlog", so I have a
> basic overview of the logic.
>
> I did something similar in a previous lifetime (a historical Stock
> Status Report) - and that was a bear! It never seemed accurate either,
> with subtracting receipts, adding and subtracting other inventory
> transactions, etc.
>
> Thanks in advance -
> Laraine
> (Vantage 407c with Progress)
>
>
>
> ------------------------------------
>
> 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
>