July 30, 2003 at 4:33 am
......does anyone know if you can insert SQL queries into crystal reports using SQL and not crystals' own 'SQL' language? Thanx
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
July 30, 2003 at 1:59 pm
use a stored proc as your source in the set location. Although the code is not in crystal it is far more efficient to have it in a stored proc as the processing will be done on the server and the result set will be sent back to the report where you can do the formatting.
Sql cannot be entered directly into Crystal report designer (the show sql query is just that, "SHOW" -- you cannot modify it there)
July 31, 2003 at 6:26 am
My God man, why would you ever let Crystal touch your DB!?? Kidding!
I never let Crystal touch the Database and the number one reason for this is for some reports (where you are Grouping by fields), Crystal will do 2 or 3 pass-throughs on the data! Ok, I've never done benchmarks but that seems like an awful lot of Network traffic.
Anyway, the best method I've used is retrieving the Data into a Recordset / DataSet (this way you can use Standard SQL syntax) and passing the Data to the Report. Look up on Crystal's website on how to do this. You have to develop Reports in a whole new way, they use TTX files (data definition) instead of connecting to a DB.
In Crystal .NET, I use an XSD Schema instead of the above method. In using the Methodology, we've spend up reports in some of our apps that used to takes Hours, now only take 15min!
Now, I have developed two very simple apps for creating Crystal Reports very fast! These apps help test a Report that uses a Data Definition file. All you do is tell it which Report to run, what the SQL statement is...and Wham! It prints! Let me know if you want them. I have one for Crystal 8 and one for Crystal 9.0. And the best part is...you don't have to be a programmer to use them =P.
July 31, 2003 at 6:34 am
Though I have not yet played with version 9 much, I believe that Crystal Version 9.0 will not only let you directly write the SQL, it is called a command, it will allow you to store it for reuse in the object repository.
Good luck.
July 31, 2003 at 6:41 am
Yes, this is correct. If you are familiar with ADO.NET, then this makes sense.
A Command (or DataCommand) is any kind of SQL Statement in ADO.NET whether it's a Select, Insert, Update, or Delete.
Connection
DataAdapter
SelectCommand (Command)
InsertCommand (Command)
UpdateCommand (Command)
DeleteCommand (Command)
Command (i.e. Stored Proc, Text, Table)
July 31, 2003 at 7:14 am
quote:
Anyway, the best method I've used is retrieving the Data into a Recordset / DataSet (this way you can use Standard SQL syntax) and passing the Data to the Report. Look up on Crystal's website on how to do this. You have to develop Reports in a whole new way, they use TTX files (data definition) instead of connecting to a DB.
We also use this way, although it's not very common. We pass the request through MTS objects, create, and then pass the recordset object to the web crystal viewer. While occasionally cantankerous, we have some complex reports that run quickly and efficiently.
If you do this, ensure your recordsets disconnected, static and read only. If the exact accuracy is not crucial, set your connection propery to ReadUncommitted.
While this took some time to figure out at first, the result has been well worth it.
July 31, 2003 at 12:02 pm
I think you can write the SQL in QE and then paste it in Crystal Reports and set it to run as a pass through querry.
July 31, 2003 at 2:57 pm
Also using the route / method RonKyle is refering to.
August 1, 2003 at 4:41 am
Depends on your version of Crystal but yes you can set the query via code to any valid SQL syntax for the connection object you are using. I always make my connections ADO in Crystal and at runtime I submit the proper SQL string to process the data (remember thou you have to provide the fields with the proper datatypes to the report for the binding to occurr). But I too suggest use an SP for best efficiendy and try to do soem of the stuff Crystal will be doing with group if you don't need drill downs. You will note a lot of CPU cycles and temps files on the location Crystasl is invoked from if you provide the data and let it do the summations (we do web Crystal). Also, consider anything you can do to limit the data from SQL that is need will speed the report up. I can provide you the ASP file we use as it is based on the Crystal Example anyway.
August 4, 2003 at 2:06 pm
You can also create a view in SQL Server that formats the data in the way you want and Crystal will access it just like a table. The views are nice because you can use User Defined Functions, etc. to pull the data together BEFORE Crystal gets it.
August 8, 2003 at 3:34 pm
The quickest way I have found after about 10 years of using Crystal is to write SQL stored procs (with the advantages of plan caching, ability to use UDFs, etc) and hook Crystal to a single SP that then returns all the data nicely formatted. The less time spent formatting data and joining tables in Crystal, the better the performance and the less the development time.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply