6.1 Cust/Vend Transaction History

Steve,

Wow, nothing is easy with Vantage, is it?! Thank you so much for this info.
I'll try it as soon as our migration is complete, and I will let you know
what happens. Thanks again!

Rebecca

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Steve Wirch
Sent: Wednesday, August 10, 2005 12:14 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] 6.1 Cust/Vend Transaction History

Hi Rebecca,

We are using outside reporting via sql server. Aged receivables, payment
history, and A/P both aged and transaction history have gotten me a lot of
M&M's from the accounting staff here. We also e-mail our executive
management hyperlinked a/r summaries.

I've enclosed instructions for a a/r listing below. If this works for you,
let me know and we'll see about an aged listing, a/p, etc.

You will need to "add-in" the Excel MS Query Add-in to make this work, as
well as have an odbc connection into vantage.

Excel Add-in (done once per PC):

1.) From Excel, Pull down Tools > Add-ins.
2.) If the "MS Query Add-in" and "odbc add-in" 's are available, check the
boxes and select ok. If they are not, you already have already installed
them.


When you've done this, follow this recipe:

1.) Open a new excel workbook.
2.) Pull down Data > Import External Data > New Database Query. If these
are unavailable you don't have the add-in's installed.
3.) Choose your odbc datasource; if it has not been built, you will need to
choose the top option and create it from the datasource built by the Vantage
install. Every machine I've seen with a Vantage install has one of these,
here they are PROGRESS91D. If you don't see a vantagey look to any of these
you will need to:
+1.) Choose <New Data Source>
+2.) Click Ok.
+3.) Name the data source.
+4.) Choose the Merant driver from the pull-down in the second field.
+5.) Click [Connect].
+6.) You need the host server name, the port number, database name, odbc
userid and password. If you don't know these,...e-mail me. Make sure to
choose [Read_Uncommitted] for the connection type for read-only access.
+7.) Don't fill in a default table.

4.) You will probably be asked for a password; the default is the same as
the login, sysprogress.
5.) You will be presented with a list of tables. You can just list raw
tables if you like, but for this exercise, choose [cancel].
6.) Answer [Yes] you'd like to edit this in MS Query.
7.) You will be prompted to Add tables. Choose close.
8.) There is a button that says [SQL]. Click this.
9.) Copy and paste the following into the SQL window.

SELECT Customer_0.Name, InvcHead_0.OpenInvoice, Customer_0.CustID,
InvcHead_0.InvoiceNum, InvcDtl_0.InvoiceLine, InvcHead_0.PONum,
InvcHead_0.InvoiceDate, InvcHead_0.DueDate, InvcDtl_0.OrderNum,
InvcDtl_0.OrderLine, InvcDtl_0.OrderRelNum, InvcDtl_0.PackNum,
InvcDtl_0.OurShipQty, InvcDtl_0.UnitPrice, InvcDtl_0.ExtPrice,
InvcDtl_0.ShipDate FROM PUB.Customer Customer_0, PUB.InvcDtl InvcDtl_0,
PUB.InvcHead InvcHead_0 WHERE InvcDtl_0.Company = InvcHead_0.Company AND
InvcDtl_0.InvoiceNum = InvcHead_0.InvoiceNum AND InvcDtl_0.CustNum =
InvcHead_0.CustNum AND Customer_0.Company = InvcDtl_0.Company AND
Customer_0.Company = InvcHead_0.Company AND Customer_0.CustNum =
InvcHead_0.CustNum AND
((InvcHead_0.OpenInvoice=1))

10.) Click Ok to exit the sql window. The MS Query window will bring in and
link three tables and propagate the rows we've defined.

11.) Click the button that has a door with an arrow pointing into it,
normally the fourth from the left, and if you hover over it it says Return
Data to Excel. Alternatively pull down File > Return Data to Microsoft
Excel, the bottom option.

You will be prompted to choose how and where to place the data. If you
click ok, you'll get a raw listing. If you choose pivot table you will get
a pivot table that has the data in it. Since there is so much data here I
tend to use the pivot table. When presented with the pivot table wizard,
click [Finish]. If you are unfamiliar with pivot tables,...e-mail me and
I'll send you a finished spreadsheet. Roughly speaking you will have some
blue boxes; drag and drop fields onto the spreadsheet.

You can arrange, customize, graph, etc. this pivot table to specific needs.

Save the spreadsheet locally, it has the data in it. To refresh the
spreadsheet in the future, open it, right-click on the pivot and choose
[Refresh Data]. Right-click and choose Wizard, then Back, and then Get Data
if you want other fields from the three tables; it is a gui much like
Microsoft Access with a lobotomy. Oh,... you might want to do this in
Access as well.

Good luck,

Steve



-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Rebecca Schmuck
Sent: Tuesday, August 09, 2005 6:30 PM
To: vantage@yahoogroups.com
Subject: [Vantage] 6.1 Cust/Vend Transaction History

Hello.

How are 6.1 users getting customer and vendor transaction history? We are
unhappy with it on 5.2. For example, we don't have a simple way of getting
payment info on customers, such as invoice 123 was paid net 37, invoice
456
was paid net 63, etc.

We are migrating to 6.1 this fall, and my controller wants to know if he
will be happy with the increased functionality.

Thanks,
Rebecca
P-K Tool


[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/links
Yahoo! 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/links
Yahoo! Groups Links
Hello.

How are 6.1 users getting customer and vendor transaction history? We are
unhappy with it on 5.2. For example, we don't have a simple way of getting
payment info on customers, such as invoice 123 was paid net 37, invoice 456
was paid net 63, etc.

We are migrating to 6.1 this fall, and my controller wants to know if he
will be happy with the increased functionality.

Thanks,
Rebecca
P-K Tool


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

We are using outside reporting via sql server. Aged receivables,
payment history, and A/P both aged and transaction history have gotten
me a lot of M&M's from the accounting staff here. We also e-mail our
executive management hyperlinked a/r summaries.

I've enclosed instructions for a a/r listing below. If this works for
you, let me know and we'll see about an aged listing, a/p, etc.

You will need to "add-in" the Excel MS Query Add-in to make this work,
as well as have an odbc connection into vantage.

Excel Add-in (done once per PC):

1.) From Excel, Pull down Tools > Add-ins.
2.) If the "MS Query Add-in" and "odbc add-in" 's are available, check
the boxes and select ok. If they are not, you already have already
installed them.


When you've done this, follow this recipe:

1.) Open a new excel workbook.
2.) Pull down Data > Import External Data > New Database Query. If
these are unavailable you don't have the add-in's installed.
3.) Choose your odbc datasource; if it has not been built, you will need
to choose the top option and create it from the datasource built by the
Vantage install. Every machine I've seen with a Vantage install has one
of these, here they are PROGRESS91D. If you don't see a vantagey look
to any of these you will need to:
+1.) Choose <New Data Source>
+2.) Click Ok.
+3.) Name the data source.
+4.) Choose the Merant driver from the pull-down in the second field.
+5.) Click [Connect].
+6.) You need the host server name, the port number, database name,
odbc userid and password. If you don't know these,...e-mail me. Make
sure to choose [Read_Uncommitted] for the connection type for read-only
access.
+7.) Don't fill in a default table.

4.) You will probably be asked for a password; the default is the same
as the login, sysprogress.
5.) You will be presented with a list of tables. You can just list raw
tables if you like, but for this exercise, choose [cancel].
6.) Answer [Yes] you'd like to edit this in MS Query.
7.) You will be prompted to Add tables. Choose close.
8.) There is a button that says [SQL]. Click this.
9.) Copy and paste the following into the SQL window.

SELECT Customer_0.Name, InvcHead_0.OpenInvoice, Customer_0.CustID,
InvcHead_0.InvoiceNum, InvcDtl_0.InvoiceLine, InvcHead_0.PONum,
InvcHead_0.InvoiceDate, InvcHead_0.DueDate, InvcDtl_0.OrderNum,
InvcDtl_0.OrderLine, InvcDtl_0.OrderRelNum, InvcDtl_0.PackNum,
InvcDtl_0.OurShipQty, InvcDtl_0.UnitPrice, InvcDtl_0.ExtPrice,
InvcDtl_0.ShipDate
FROM PUB.Customer Customer_0, PUB.InvcDtl InvcDtl_0, PUB.InvcHead
InvcHead_0
WHERE InvcDtl_0.Company = InvcHead_0.Company AND InvcDtl_0.InvoiceNum =
InvcHead_0.InvoiceNum AND InvcDtl_0.CustNum = InvcHead_0.CustNum AND
Customer_0.Company = InvcDtl_0.Company AND Customer_0.Company =
InvcHead_0.Company AND Customer_0.CustNum = InvcHead_0.CustNum AND
((InvcHead_0.OpenInvoice=1))

10.) Click Ok to exit the sql window. The MS Query window will bring in
and link three tables and propagate the rows we've defined.

11.) Click the button that has a door with an arrow pointing into it,
normally the fourth from the left, and if you hover over it it says
Return Data to Excel. Alternatively pull down File > Return Data to
Microsoft Excel, the bottom option.

You will be prompted to choose how and where to place the data. If you
click ok, you'll get a raw listing. If you choose pivot table you will
get a pivot table that has the data in it. Since there is so much data
here I tend to use the pivot table. When presented with the pivot table
wizard, click [Finish]. If you are unfamiliar with pivot
tables,...e-mail me and I'll send you a finished spreadsheet. Roughly
speaking you will have some blue boxes; drag and drop fields onto the
spreadsheet.

You can arrange, customize, graph, etc. this pivot table to specific
needs.

Save the spreadsheet locally, it has the data in it. To refresh the
spreadsheet in the future, open it, right-click on the pivot and choose
[Refresh Data]. Right-click and choose Wizard, then Back, and then Get
Data if you want other fields from the three tables; it is a gui much
like Microsoft Access with a lobotomy. Oh,... you might want to do this
in Access as well.

Good luck,

Steve



-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Rebecca Schmuck
Sent: Tuesday, August 09, 2005 6:30 PM
To: vantage@yahoogroups.com
Subject: [Vantage] 6.1 Cust/Vend Transaction History

Hello.

How are 6.1 users getting customer and vendor transaction history? We
are
unhappy with it on 5.2. For example, we don't have a simple way of
getting
payment info on customers, such as invoice 123 was paid net 37, invoice
456
was paid net 63, etc.

We are migrating to 6.1 this fall, and my controller wants to know if he
will be happy with the increased functionality.

Thanks,
Rebecca
P-K Tool


[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/links
Yahoo! Groups Links