	Resources
	Best sellers: cView Report Analyzer cViewSERVER ReCrystallize

	Articles: Administration Advanced Basic Crystal eNL Database Financial Problems Solved Books: CR Books Database Books Developer Books
	Tools: Analyzers Bestsellers CR Schedulers CR UFLs CR Viewers DataBase Tools Graphics International Mail UFLs ReCrystallizePro
	Add'l: About us Contact Us cViewSUITE Ppt Support
	CrystalReports on Steroids

Crystal Reports: SUB SELECT to retrieve summary data

In other articles, we covered how you could use the SQL select statement in the Crystal SQL designer to retrieve a record set to use in your report.

SELECT name, city, region from Customer

You can use a lower level SELECT clause (a sub SELECT) to summarize data from a second table and display the values in a summary field. This is done in the database so it is faster than using table joins and crystal summary fields, and a lot faster than using a subreport.

SELECT customerID, name, city, region,

(SELECT Sum(Order.Amount) from Orders

WHERE Order.CustomerID = Customer.customerID ) as TotalOrders from Customer

The trick to designing these is to write the lower level SELECT first, and then to combine them into the full statement. If you have designer access to your database and can create a view or query in the database, then a sub SELECT can be a useful way to collect and summarize data before they are processed by your report.

Here's some related information, so you can more fully explore this whole line of functionality:

One of the more advanced technical sessions at a late 2006 conference was Kurt Rheinhardt’s talk on “the Power and Possibilities of SQL expressions”. Kurt is a very experienced report designer and he demonstrated some amazing things with SQL expressions.

In particular, we were impressed to see you could do sub select clauses inside SQL expressions. If you can use these in your reports, performance improvements can be dramatic.

Our sample report extracts a few customer names from the Xtreme sample database. We then created a SQL expression of:

(Select Sum("Order Amount") from Orders

where "Customer ID"=`Customer`.`Customer ID`)

The report before using the SQL expression returned six records from the region we selected. Using the SQL expression we still had six records, and had got the database to do all the hard work.

The key to making this work is to enclose the entire expression inside parentheses. It is also important that the subselect clause returns a single record with a single field.

Because SQL expressions are calculated in the database, the entire select clause is passed to the database

engine to evaluate. This is a fast way to process the data. The major disadvantage of SQL expressions is that they are database and driver specific. Kurt recommends you build your SQL using a SQL editor. Once you have that working, you can paste the SQL into the SQL expression in the report.

Used this way, One SQL expression can replace a subreport that returns a shared variable. It simplifies your report and makes it faster to run, and easier to design.

Not all that familiar with SQL? Here's some help. If you have a report that uses an ODBC/SQL data source, look at "Show SQL query" in the database menu. This will display the SQL that Crystal Reports sends to the database server to retrieve records.

Look at the SQL each report creates and see the SQL the report writer generates. This is an easy way to learn this database language.

You can make some changes here, but we don’t recommend it because changes to selection criteria in SQL override the Select Expert and Selection Formula, and we believe those are more flexible and powerful tools.

You can also use SQL to retrieve data:

Sometimes we need to manipulate our data with SQL prior to processing a report.

For example, we combine a SELECT DISTINCT, UNION join and Sub SELECT to retrieve some data from our SQL server.

The SQL command can be added to the repository, so you can share it between several reports. If you wish to change your SQL, you need to detach the SQL from the repository, make your changes and then place it back in the repository.

The SQL Command feature is why Crystal Reports 9 does not include the Crystal SQL designer. You can do far more with Crystal Reports SQL commands.

SQL command parameters

Use SQL command parameters. Using SQL commands as the source of data for your report has several benefits. This SQL command feature also includes the ability to include a parameter in the SQL command.

For example, we have created a parameter {?Region} and use that to select a single region
from our table.

The SQL command now says Select * from Customer where Region = '{?Region}'

The parameter editor in the SQL command dialog box lets you enter only a single default value. So if you wish to load more default values, return to the Field Explorer and add more default values to the parameter there.

This feature supports string, date and numeric parameters, but they must be a single value. Multi value parameters are not supported.

The real benefit of this feature is where you need to extract a few records from a large table. Doing the selection in an SQL command will have a major improvement in report performance.

SQL Designer

If you do a "Full" install of Crystal Reports, you get the Crystal SQL Designer and Crystal Dictionary programs installed. These can be very useful additions to your reporting weaponry.

Click here for an article on the Crystal Dictionary.

The Crystal SQL Designer has an expert that can gently lead you through an inquiry against your database or you can enter your SQL directly. The retrieved records can then be used as the source for a report, or exported to another format.

So why use it? There are several advantages you might discover.

1) You can save data in your QRY file. This means you execute the SQL against the main database once, and then can process your Crystal Reports against the QRY file without compromising the performance of your main database.

2) You also have the ability to send the QRY file to a remote site, so a user there can process reports without requiring a connection to your original data

3) If the Application developer doesn’t want you to have permission to make changes to the Db, you can use this tool to process the SQL you would normally use in a view or stored procedure.

4) A full set of SQL functions is available here including Summary functions, and UNION joins.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.