cancel
Showing results for 
Search instead for 
Did you mean: 

Defining SQL Command

Former Member
0 Kudos
55

I am trying to learn more about the Defining SQL Command in the Database Expert.

I understand from what I've read that it can be used to create a virtual table that can provide access to a smaller number of fields and records than the entire database, so that accessing the data is quicker. Also, if you need this data structure across reports, you can save the command to the Repository.

I understand that such a virtual table would be helpful with a large database having a lot of fields. It would reduce the number of fields in Field Explorer, which would make designing a report easier.

I don't understand why it would be necessary to parcel out certain records, though. Doesn't the Record Selection Formula do the same thing -- it only pulls into the report those records that are needed? If someone could explain this further, I'd appreciate it.

Are there other benefits (or disadvantages) of the SQL Command functionality that I'm missing?

If there are any readings out there that discuss the SQL Command in Crystal, I'd appreciate the reference.

Thanks.

Gary

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Thanks for all of your help.

Former Member
0 Kudos

When designing a Crystal Report using the tables directly, and there is a Record Selection formula, Crystal can (sometimes) push the record selection to the database. When the report is based on an SQL Command, Crystal can't to that (I don't think).

By adding record selection to the SQL Command, the number of records fed back to Crystal will be reduced to only those necessary, whereas if you put the selection in the Record Selection formula all of the data will be fed to Crystal, which will then have to sift through it all to select the records to report. On huge files, that can really hurt performance.

Another huge benefit of using the SQL Command is with data aggregation. If you are writing a summary report and let the database do the summing, it will return far fewer records to Crystal (can be many orders of magnitude depending on the data and aggregation level) and Crystal won't have to do the aggregation.

Table joins are also almost always quicker in a database than what Crystal can do (depending on database design).

IMHO, I would base (virtually) all of my reports on an SQL command that does all of the data manipulation required by the report, returning only the data required for Crystal to format and display. (I know there are arguments not to do this - some with great merit - but generally speaking I believe this to be the best performance-wise.) After all, that's what databases are built to do!

HTH,

Carl

Former Member
0 Kudos

Carl,

I gather from your other posts on this issue (I've just been browsing prior threads), you are in the COMMAND camp!

Just a follow-up technical question:

Let's say that I have three parallel databases, each for a separate company. The 5 tables comprising each database are structured uniformly across databases, using identical names for all fields and identical linkages between tables. If I want to create one report that has the ability to report on any of the three databases, can I create a dynamic SQL Command that points to the correct database, based on a user-defined parameter value (e.g., Which company would you like to report on -- A, B, or C?).

What would the syntax be?

Thanks.

Former Member
0 Kudos

No, this wouldn't be done through the SQL Command. Each report has a database connection for each database that it needs to execute. Since the tables are the same in the various database, the SQL should work on any of them. All you need to do is point the connection to the database that you want to report off of at the time you run the report. (You probably want to add a parameter to the report so the database used is displayed somewhere - or get that info from the database using a one-record table.)

HTH,

Carl

Former Member
0 Kudos

So, basically, I would have a separate report set up for each separate database (company), right?

Also, where can I get guidance on the correct syntax for the SQL command?

Thanks.

Former Member
0 Kudos

So, basically, I would have a separate report set up for each separate database (company), right?

The same .RPT file can be used, but for instance, if you are using InfoView (Crystal Enterprise / BOE), you could set up copies of the report with each pointing to the desired database.

Also, where can I get guidance on the correct syntax for the SQL command?

The SQL Reference for your database. Or, one of the multitudes of books on SQL.

HTH,

Carl