December 16, 2004 at 11:58 am
I am relatively new to SQL and need some advice. I recently started a new job and the previous adm. used access to run queries and reports against an SQL 7.0 database. The designers of the database said access will tend to lock up the database and may even cause corruption. They say they warned him [the previous adm] of this. He had his SQL statements in access broken down into sometimes 10 steps saving the data results in access tables along the way. They [designers] recommended I go with something like crystal reports so I went with crystal reports 10. I am trying to rebuild a lengthy report the adm used to run in crystal reports and one I get to a poitn where I have about 6 tables involved crystal reports freezes every time. I am ok running the query and then putting the data in the report until the time I need that piece of data from that one more table. Any suggestions on anything, further reading, anything, etc. to get this to work or should I just give up and go back to access and run it in the 10 steps overnight when people are off the system. And yes, I am desparately trying to convince the company to upgrade to at least 2000!
December 16, 2004 at 12:06 pm
I would suggest creating a view that contains the needed tables using the with(nolock) clause then use that view as your data source in crystal. I would also strongly suggest the migration to SQL 2000 so you can use SQL Reporting Services.
If the phone doesn't ring...It's me.
December 16, 2004 at 12:41 pm
ALso don't let Crystal neccessarily do the joins and groupings sql for you as it will not optimize a whole lot in itself. Many times if the report is fairly static write the query to return the needed data in QA and provide a view or storted procedure for Crystal to read from. But in that situation you could have done similar in Access with fair performance and not lock the database either. It is in generaly how you connect and work with the data in access that can cause issues server side.
December 17, 2004 at 1:32 am
Whatever reporting tool you use, whether Access or Crystal Reports (shudder), let SQL Server do all the joining and qualifying (filtering) and use the reporting tool for formatting and display only. I know Crystal wants to justify its outrageous price by constructing SQL queries, but I would not let it do anything other than "select *" from a view defined in the SQL Server.
I build views on top of views, usually "group by" views, and use Excel as the destination of the "select *" from the top view. Most of the data users I support are accountants. They love getting "reports" as Excel worksheets-- often several sheets grouped in one workbook .xls-- and as the DBA I remain responsible for joins that deliver what people expect and for query performance.
Spending $$ on the latest Crystal instead of on keeping SQL Server current?! What are they thinking? Don't they know SQL Server includes a powerful tools, is not just a box that holds data?
_________________
"Look, those sheep have been shorn."
data analyst replies, "On the sides that we can see.."
December 17, 2004 at 7:03 am
I have been using Crystal and SQL Server for about 12 years. The previous posts are correct, Crystal is a great presenter of data, just do not let it build the query.
All of my reports use Stored procedures. If you decide you need to use a temp table and if it is going to take several queries to populate that temp table, be sure to use "SET NOCOUNT ON" as the first statement of the stored procedure. Otherwise you will greatly confuse Crystal since it cannot handle multiple recordsets.
Also if your reports get really complex use multiple stored procedures and Sub reports in Crystal.
Good luck.........
COGunn
December 17, 2004 at 7:23 am
Just a thought but why not look into SQL Report Services might even be more economical than Crystal but has a lot of power.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply