I'd like to underscore and elaborate on Adam's comments.
First, if you're new to SQL server, stay out of it until you get
training. Install SQL locally on your PC or another test server and
restore your Vantage database there so you can safely play in a
segregated environment. One little wrong mouse click may result in dire
consequences. You can install the free version of MSSQL (SQL Express)
and use that to play in. It's essentially the same but has a database
limit of 10GB I believe and can't do replication... etc. But for your
purpose, it's the same and FREE.
I will caution anyone to NOT have Crystal Reports hit your SQL tables
directly via ODBC. Instead, however, create your queries in SQL and
turn them into a View. Ensure you use WITH(NOLOCK) statements on all
Select statements. Even if you just want to do a very simple report on
the Part table, I still strongly recommend you use a view.
In SQL, with the above simple example, if you wanted to run a report on
the Part table, make a view like this:
CREATE VIEW [VIEW_PART] AS
SELECT * FROM PART WITH(NOLOCK)
Create a user in SQL; a generic user like "report_user" and then give
that user permissions ONLY to the views. Since views are read only and
you've incorporated the NOLOCK clause, you will be 100% safe. Then in
Crystal Reports hit that View and develop your report on the View.
With SQL you have the amazing benefit of data freedom. And once you
hone your skills you can do some magic with nested views, triggers,
stored procedures and user-defined functions. Be very careful when you
start doing the latter tricks with triggers and such.
Then you can also have more fun with ASP and SQL. I have a whole
intranet of ASP webpages hitting the Vantage tables via ODBC and it
circumvents Vantage licensing altogether. So for some folks who only
like reports (execs), you don't even need to install Vantage. Check out
this product called ASPRunnerPro http://xlinesoft.com/asprunnerpro/
This is the best darn money I've ever spent besides the night of my
bachelor party. ;-)
You can set up reports hitting views and your users can sort, filter and
export the results easily all with your web browser. Crystal Reports
still has to be used for complex and "pretty formatted" reports, but
this is a great alternative to the clunky Vantage dashboards.
Good luck.
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Adam
Sent: Friday, August 19, 2011 2:04 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: SQL and Crystal Reports
First,
I would caution against using SQL if you are unfamiliar with writing SQL
Queries. Crystal will allow you to manually pull in the tables, and link
them, etc. However, when doing this, versus properly structured queries,
the entire table of data is brought into Crystal Reports, and reports
can take a long time if they reference years worth or orders, invoices,
etc.
I would suggest BAQs if you are unfamiliar with SQL. That being said...
Install the SQL Native Client. I believe Epicor recommends version 9,
and not version 10 for performance reasons. I have used both, and have
switched everything to version 9 to avoid giving Epicor any cop-outs.
Create a (I'm assuming Active Directory) group and assign users to that
group. Within the SQL Management studio, give that group Read Only
access.
When creating a report, create a new OLE DB (ADO) Connection, and select
the SQL Native Client as the driver. You will then be able to input the
server name (If you used the Active Directory group as recommended
above, check the Integrated Security checkbox). Select your DB, and
either add tables, stored procedures, or SQL commands as needed.
Adam
--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"wipaire_vantage" <vantage@...> wrote:
2008R2 SQL server? Do I need to create a new user in SQL from the
reports to run against?
First, if you're new to SQL server, stay out of it until you get
training. Install SQL locally on your PC or another test server and
restore your Vantage database there so you can safely play in a
segregated environment. One little wrong mouse click may result in dire
consequences. You can install the free version of MSSQL (SQL Express)
and use that to play in. It's essentially the same but has a database
limit of 10GB I believe and can't do replication... etc. But for your
purpose, it's the same and FREE.
I will caution anyone to NOT have Crystal Reports hit your SQL tables
directly via ODBC. Instead, however, create your queries in SQL and
turn them into a View. Ensure you use WITH(NOLOCK) statements on all
Select statements. Even if you just want to do a very simple report on
the Part table, I still strongly recommend you use a view.
In SQL, with the above simple example, if you wanted to run a report on
the Part table, make a view like this:
CREATE VIEW [VIEW_PART] AS
SELECT * FROM PART WITH(NOLOCK)
Create a user in SQL; a generic user like "report_user" and then give
that user permissions ONLY to the views. Since views are read only and
you've incorporated the NOLOCK clause, you will be 100% safe. Then in
Crystal Reports hit that View and develop your report on the View.
With SQL you have the amazing benefit of data freedom. And once you
hone your skills you can do some magic with nested views, triggers,
stored procedures and user-defined functions. Be very careful when you
start doing the latter tricks with triggers and such.
Then you can also have more fun with ASP and SQL. I have a whole
intranet of ASP webpages hitting the Vantage tables via ODBC and it
circumvents Vantage licensing altogether. So for some folks who only
like reports (execs), you don't even need to install Vantage. Check out
this product called ASPRunnerPro http://xlinesoft.com/asprunnerpro/
This is the best darn money I've ever spent besides the night of my
bachelor party. ;-)
You can set up reports hitting views and your users can sort, filter and
export the results easily all with your web browser. Crystal Reports
still has to be used for complex and "pretty formatted" reports, but
this is a great alternative to the clunky Vantage dashboards.
Good luck.
________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Adam
Sent: Friday, August 19, 2011 2:04 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: SQL and Crystal Reports
First,
I would caution against using SQL if you are unfamiliar with writing SQL
Queries. Crystal will allow you to manually pull in the tables, and link
them, etc. However, when doing this, versus properly structured queries,
the entire table of data is brought into Crystal Reports, and reports
can take a long time if they reference years worth or orders, invoices,
etc.
I would suggest BAQs if you are unfamiliar with SQL. That being said...
Install the SQL Native Client. I believe Epicor recommends version 9,
and not version 10 for performance reasons. I have used both, and have
switched everything to version 9 to avoid giving Epicor any cop-outs.
Create a (I'm assuming Active Directory) group and assign users to that
group. Within the SQL Management studio, give that group Read Only
access.
When creating a report, create a new OLE DB (ADO) Connection, and select
the SQL Native Client as the driver. You will then be able to input the
server name (If you used the Active Directory group as recommended
above, check the Integrated Security checkbox). Select your DB, and
either add tables, stored procedures, or SQL commands as needed.
Adam
--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"wipaire_vantage" <vantage@...> wrote:
>2008R2. I'm new to SQL Server. How do I connect Crystal Reports to the
> I want to connect to a Epicor 9.05 SQL database that is running on
2008R2 SQL server? Do I need to create a new user in SQL from the
reports to run against?
>[Non-text portions of this message have been removed]
> Thanks
> Joe Sanders
> Wipaire, Inc.
>