Creating Reports - what is the best tool?

system · January 17, 2007, 3:55pm

From the DSN properties select the advanced tab and then select "Read
Uncommitted" from the "default isolation level" drop down.

Gerard M Wadman

Sr. Network Systems Engineer

Scandius BioMedical Inc.

11A Beaver Brook Road

Littleton, MA 01460

978/486-4088 x 124

978/486-4108 (fax)

http://www.scandius.com/

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of acp2g99
Sent: Wednesday, January 17, 2007 2:34 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Creating Reports - what is the best tool?

Can you please offer how you set up "your ODBC users setup for "Read
Uncommitted"?

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

system · January 10, 2007, 6:57pm

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel.
They love it.
Are there any drawbacks to letting users use the ODBC connection?
What about creating a backup of the DB (updated weekly) and letting
users pull data from there?

Thanks for your thoughts.

James

system · January 10, 2007, 7:47pm

As long as you keep your ODBC users setup for "Read Uncommitted" you
should be fine letting them use Access or Excel to create custom
reports.

The majority of custom reports my users reference I created in Excel
linked via MS Query.

What's the first thing any user asks for once they see a report
generated in a web browser, Crystal or any other reporting tool?
"This is great! Now how can I get this into Excel?"

Sad but true! :-)

_____________________________
Jim Kennedy
Manager, Information Technologies
Firstmark Aerospace Corporation
1176 Telecom Drive
Creedmoor, North Carolina 27522-8294
Office: (919) 956-4264
FAX: (919) 682-3786
Cell: (919) 697-9410
jkennedy@... <mailto:jkennedy@...>

www.firstmarkaerospace.com <http://www.firstmarkaerospace.com/>
www.firstmarkcorp.com <http://www.firstmarkcorp.com/>

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of okeefe_01
Sent: Wednesday, January 10, 2007 6:54 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Creating Reports - what is the best tool?

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel.
They love it.
Are there any drawbacks to letting users use the ODBC connection?
What about creating a backup of the DB (updated weekly) and letting
users pull data from there?

Thanks for your thoughts.

James

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

system · January 11, 2007, 12:12am

Using ODBC, your entire database is open for review. This can be
dangerous if you have confidential information such as employee
information, financial information or cost information that you don't
want disclosed. Using ODBC, vantage security is thrown out the window.
Since the tables are integrated in one file, Windows/Linux security is
also no good.

This is why the BAQ's are so important. BAQ security can be controlled
by menu maintenance. And with 8.03 and BAQ reports, the situation is
better.

Charlie Smith
Smith Business Services / 2W Technologies LLC
www.vistaconsultant.com <http://www.vistaconsultant.com/> /
www.2wtech.com <http://www.2wtech.com/>

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of okeefe_01
Subject: [Vantage] Creating Reports - what is the best tool?

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel. They
love it.
Are there any drawbacks to letting users use the ODBC connection?

.

<http://geo.yahoo.com/serv?s=97359714/grpId=20369/grpspId=1705007183/msg
Id=54093/stime=1168473462/nc1=3848550/nc2=3848642/nc3=3>

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

system · January 11, 2007, 7:58am

I prefer Crystal. It gives a huge amount of power for manipulating
data. If needed, it also exports easily into Excel / Access / PDF....

Bruce Butler

IT Manager

Knappe & Koester, Inc.

_____

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of okeefe_01
Sent: Wednesday, January 10, 2007 6:54 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Creating Reports - what is the best tool?

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel.
They love it.
Are there any drawbacks to letting users use the ODBC connection?
What about creating a backup of the DB (updated weekly) and letting
users pull data from there?

Thanks for your thoughts.

James

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

system · January 11, 2007, 8:14am

Setting up ODBC connections will work but keep in mind that each
connection has the potential to create record locks thus causing an
increase in DB waits.

When you setup a DSN on a client computer, click on the advanced tab and
select "Read Uncommited" as the connection type. Doing this will
minimize record locks and it will also prevent the users from writing to
the DB.

Crystal is the preffered third party reporting reporting app, but Access
and Excel can certainly be used if that's what your users prefer.

I personally prefer Access as it is way more powerful than Crystal IMO.

If you are comfortable navigating at the DB level, I would suggest
creating multiple user schema's with various privileges and then create
views within them. Then assign those user credentials to the DSN on the
appropriate clients, this will ensure that curious users don't take it
upon themselves to snoop around within the Vantage tables.

Cheers

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of okeefe_01
Sent: Wednesday, January 10, 2007 6:54 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Creating Reports - what is the best tool?

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel.
They love it.
Are there any drawbacks to letting users use the ODBC connection?
What about creating a backup of the DB (updated weekly) and letting
users pull data from there?

Thanks for your thoughts.

James

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

system · January 11, 2007, 9:22am

I think that a ODBC Connection may also take a license. Depending on
the volume of users and the reporting there might a performance
issue. But Gerards comments are the priority ones whether there is
one or hundred users.

If you have corvu, it takes a snapshot of the vantage database, then
you could report off this and not the live database. However, corvu
is a different product completely and I don't think you directly
access it through microsoft office products.

--- In vantage@yahoogroups.com, "Gerard Wadman" <gwadman@...> wrote:
>
> Setting up ODBC connections will work but keep in mind that each
> connection has the potential to create record locks thus causing an
> increase in DB waits.
>
> When you setup a DSN on a client computer, click on the advanced
tab and
> select "Read Uncommited" as the connection type. Doing this will
> minimize record locks and it will also prevent the users from
writing to
> the DB.
>
>
>
> Crystal is the preffered third party reporting reporting app, but
Access
> and Excel can certainly be used if that's what your users prefer.
>
> I personally prefer Access as it is way more powerful than Crystal
IMO.
>
>
>
> If you are comfortable navigating at the DB level, I would suggest
> creating multiple user schema's with various privileges and then
create
> views within them. Then assign those user credentials to the DSN on
the
> appropriate clients, this will ensure that curious users don't take
it
> upon themselves to snoop around within the Vantage tables.
>
>
>
> Cheers
>
>
>
> ________________________________
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On
Behalf
> Of okeefe_01
> Sent: Wednesday, January 10, 2007 6:54 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Creating Reports - what is the best tool?
>
>
>
> I am new to Vantage. We have a number of users that want to be able
to
> create reports with Vantage data. A couple of them can use Crystal
> Reports, but the number of people is always growing.
> I have setup a couple uses to use ODBC with either Access or Excel.
> They love it.
> Are there any drawbacks to letting users use the ODBC connection?
> What about creating a backup of the DB (updated weekly) and letting
> users pull data from there?
>
> Thanks for your thoughts.
>
> James
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

system · January 11, 2007, 9:35am

To me...the question is not "which tool" but allowing users to create reports in the first place. Generally anyone outside of IT, in a line or staff position that depends on the data from the reports, are either unqualified to develop a report that provides accurate information or too deep into the results to recognize any bias introduced into the reporting. I have found the few "cross over" people who can develop and self-audit a report to be very rare. More often turning over a reporting tool (and access, even read-only, to the database) is asking for trouble.

Case in point....a former CFO who developed his own spreadsheets from extracted data and did not know his running totals were wrong. He used the report for several years and transfered elsewhere. His successor (a good friend of mine), after a couple more years, found the problem and determined an inventory write off of $200K was needed. Because he had relied on the accuracy of the report and despite his having found the problem (and four years of outside auditors NOT finding it!) he was fired. All because of a simple misunderstanding of running totals and failure to internally check the report.

We have many people making their own spreadsheet databases (although I prefer they don't) but at least from data on validated reports and the spreadsheets are peer reviewed.

As mentioned BAQs may be a good middle ground where IT develops an XML extract and the users can take it from there with just about anything - Access, Excel, Crystal, etc... As long as there is good oversight and control. I shake in my boots at the thought of a user doing their own BAQ or ODBC and not understanding something like assembly levels or order releases and then summarizing something at the wrong level and getting multiple occurances added up. Realisticaly should a Production Manager or Engineering Supervisor be a quasi-DBA in addition to their primary function? How many users really have time to understand a left outer join?

This concludes today's ramble on IT Philosophy. ;)
-Todd C.

-----Original Message-----
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On Behalf Of okeefe_01
Sent: Wednesday, January 10, 2007 5:54 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Creating Reports - what is the best tool?

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel.
They love it.
Are there any drawbacks to letting users use the ODBC connection?
What about creating a backup of the DB (updated weekly) and letting
users pull data from there?

Thanks for your thoughts.

James

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

system · January 11, 2007, 9:58am

Very true.

--- In vantage@yahoogroups.com, "Todd Caughey" <caugheyt@...> wrote:
>
> To me...the question is not "which tool" but allowing users to
create reports in the first place. Generally anyone outside of IT,
in a line or staff position that depends on the data from the
reports, are either unqualified to develop a report that provides
accurate information or too deep into the results to recognize any
bias introduced into the reporting. I have found the few "cross
over" people who can develop and self-audit a report to be very
rare. More often turning over a reporting tool (and access, even
read-only, to the database) is asking for trouble.
>
> Case in point....a former CFO who developed his own spreadsheets
from extracted data and did not know his running totals were wrong.
He used the report for several years and transfered elsewhere. His
successor (a good friend of mine), after a couple more years, found
the problem and determined an inventory write off of $200K was
needed. Because he had relied on the accuracy of the report and
despite his having found the problem (and four years of outside
auditors NOT finding it!) he was fired. All because of a simple
misunderstanding of running totals and failure to internally check
the report.
>
> We have many people making their own spreadsheet databases
(although I prefer they don't) but at least from data on validated
reports and the spreadsheets are peer reviewed.
>
> As mentioned BAQs may be a good middle ground where IT develops an
XML extract and the users can take it from there with just about
anything - Access, Excel, Crystal, etc... As long as there is good
oversight and control. I shake in my boots at the thought of a user
doing their own BAQ or ODBC and not understanding something like
assembly levels or order releases and then summarizing something at
the wrong level and getting multiple occurances added up.
Realisticaly should a Production Manager or Engineering Supervisor be
a quasi-DBA in addition to their primary function? How many users
really have time to understand a left outer join?
>
> This concludes today's ramble on IT Philosophy. ;)
> -Todd C.
>
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf Of okeefe_01
> Sent: Wednesday, January 10, 2007 5:54 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Creating Reports - what is the best tool?
>
>
>
> I am new to Vantage. We have a number of users that want to be able
to
> create reports with Vantage data. A couple of them can use Crystal
> Reports, but the number of people is always growing.
> I have setup a couple uses to use ODBC with either Access or Excel.
> They love it.
> Are there any drawbacks to letting users use the ODBC connection?
> What about creating a backup of the DB (updated weekly) and letting
> users pull data from there?
>
> Thanks for your thoughts.
>
> James
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

system · January 11, 2007, 11:45am

Not exactly true, you can create users for ODBC and apply security to
tables for the users, I.e make them read only or not at all. (however
your statement is true if you use the default (v6.1) sysprogress user)

Regards,

Stephen Edginton

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Charlie Smith
Sent: 11 January 2007 05:03
To: vantage@yahoogroups.com
Subject: [Vantage] ODBC drawbacks

Using ODBC, your entire database is open for review. This can be
dangerous if you have confidential information such as employee
information, financial information or cost information that you don't
want disclosed. Using ODBC, vantage security is thrown out the window.
Since the tables are integrated in one file, Windows/Linux security is
also no good.

This is why the BAQ's are so important. BAQ security can be controlled
by menu maintenance. And with 8.03 and BAQ reports, the situation is
better.

Charlie Smith
Smith Business Services / 2W Technologies LLC
www.vistaconsultant.com <http://www.vistaconsultant.com/
<http://www.vistaconsultant.com/> > /
www.2wtech.com <http://www.2wtech.com/ <http://www.2wtech.com/> >

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of okeefe_01
Subject: [Vantage] Creating Reports - what is the best tool?

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel. They
love it.
Are there any drawbacks to letting users use the ODBC connection?

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

system · January 11, 2007, 4:09pm

Which is prima facie evidence as to why it is never a good idea in Vantage
to deploy ODBC access with the default login in a production environment.

Best regards,

Michael

Michael Barry
Aspacia Systems Inc
866.566.9600
312.803.0730 fax
<http://www.aspacia.com/> http://www.aspacia.com/

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Stephen Edginton
Sent: Thursday, January 11, 2007 8:45 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] ODBC drawbacks

Not exactly true, you can create users for ODBC and apply security to
tables for the users, I.e make them read only or not at all. (however
your statement is true if you use the default (v6.1) sysprogress user)

Regards,

Stephen Edginton

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Charlie Smith
Sent: 11 January 2007 05:03
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] ODBC drawbacks

Using ODBC, your entire database is open for review. This can be
dangerous if you have confidential information such as employee
information, financial information or cost information that you don't
want disclosed. Using ODBC, vantage security is thrown out the window.
Since the tables are integrated in one file, Windows/Linux security is
also no good.

This is why the BAQ's are so important. BAQ security can be controlled
by menu maintenance. And with 8.03 and BAQ reports, the situation is
better.

Charlie Smith
Smith Business Services / 2W Technologies LLC
www.vistaconsultant.com <http://www.vistaconsultant.com/
<http://www.vistaconsultant.com/> > /
www.2wtech.com <http://www.2wtech.com/ <http://www.2wtech.com/> >

________________________________

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
<mailto:vantage%40yahoogroups.com> ] On
Behalf
Of okeefe_01
Subject: [Vantage] Creating Reports - what is the best tool?

I am new to Vantage. We have a number of users that want to be able to
create reports with Vantage data. A couple of them can use Crystal
Reports, but the number of people is always growing.
I have setup a couple uses to use ODBC with either Access or Excel. They
love it.
Are there any drawbacks to letting users use the ODBC connection?

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

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

system · January 11, 2007, 4:47pm

I agree 100%. Users should not create their own reports.

At our company I am the report developer. I use Crystal and Access
depending on the situation. ODBC is safe, as far as writing to the
DB, if you set it up right. The instructions I had told me to create
a odbcuser account and give that account read rights, only IT knows
the sysprogress user id and password. I would caution about certain
situations where people could get into sensitive tables if they know
how to create their own reports or queries. We don't have any
payroll info in Vantage so its not a big worry and none of my users
know how to create an ODBC link. Nobody but IT has Crystal Reports
to develop with, and every Access application I develop has table
security built in to the DB.

Many of my users (bean counters especially) misuse Excel to an
extreme keeping track of things on their own that could easily be
pulled from the system if they'd only ask. I've seen several,
Controllers included, that don't know how to properly use formulas,
but they love their spreadsheets.

Brian Stenglein

--- In vantage@yahoogroups.com, "Todd Caughey" <caugheyt@...> wrote:
>
> To me...the question is not "which tool" but allowing users to
create reports in the first place. Generally anyone outside of IT,
in a line or staff position that depends on the data from the
reports, are either unqualified to develop a report that provides
accurate information or too deep into the results to recognize any
bias introduced into the reporting. I have found the few "cross
over" people who can develop and self-audit a report to be very
rare. More often turning over a reporting tool (and access, even
read-only, to the database) is asking for trouble.
>
> Case in point....a former CFO who developed his own spreadsheets
from extracted data and did not know his running totals were wrong.
He used the report for several years and transfered elsewhere. His
successor (a good friend of mine), after a couple more years, found
the problem and determined an inventory write off of $200K was
needed. Because he had relied on the accuracy of the report and
despite his having found the problem (and four years of outside
auditors NOT finding it!) he was fired. All because of a simple
misunderstanding of running totals and failure to internally check
the report.
>
> We have many people making their own spreadsheet databases
(although I prefer they don't) but at least from data on validated
reports and the spreadsheets are peer reviewed.
>
> As mentioned BAQs may be a good middle ground where IT develops an
XML extract and the users can take it from there with just about
anything - Access, Excel, Crystal, etc... As long as there is good
oversight and control. I shake in my boots at the thought of a user
doing their own BAQ or ODBC and not understanding something like
assembly levels or order releases and then summarizing something at
the wrong level and getting multiple occurances added up.
Realisticaly should a Production Manager or Engineering Supervisor be
a quasi-DBA in addition to their primary function? How many users
really have time to understand a left outer join?
>
> This concludes today's ramble on IT Philosophy. ;)
> -Todd C.
>
>
> -----Original Message-----
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]On
Behalf Of okeefe_01
> Sent: Wednesday, January 10, 2007 5:54 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Creating Reports - what is the best tool?
>
>
>
> I am new to Vantage. We have a number of users that want to be able
to
> create reports with Vantage data. A couple of them can use Crystal
> Reports, but the number of people is always growing.
> I have setup a couple uses to use ODBC with either Access or Excel.
> They love it.
> Are there any drawbacks to letting users use the ODBC connection?
> What about creating a backup of the DB (updated weekly) and letting
> users pull data from there?
>
> Thanks for your thoughts.
>
> James
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

system · January 17, 2007, 2:34pm

Can you please offer how you set up "your ODBC users setup for "Read
Uncommitted"?

system · January 17, 2007, 3:23pm

--- In vantage@yahoogroups.com, "acp2g99" <acp2g99@...> wrote:

>
>
> Can you please offer how you set up "your ODBC users setup for "Read
> Uncommitted"?
>

Open the Control Panel - Administrative Tools - ODBC data source
administrator. On the System DSN tab, select the Data Direct
connection - Select the configure option - go to the advanced tab - on
the default isolation level select read uncommitted.

Topic		Replies	Views
Crystal Reports and ODBC Yahoo Archive	3	394	January 22, 2003
Vantage 6.1 - Best way to extract data? Yahoo Archive	4	1380	September 23, 2009
ODBC Question Yahoo Archive	14	543	May 16, 2005
Vantage ODBC Blank Title 84725 Yahoo Archive	13	539	November 4, 2009
ODBC for Vantage Version 8.0 Yahoo Archive	2	303	August 5, 2005

Creating Reports - what is the best tool?

Related Topics