Data Management Group

TRAINER'S TALK

Creating Sub-Queries in Crystal Reports Using a SQL Expression

The information in the article applies to

Crystal Reports

Synopsis

Have you been struggling with creating sub-queries in Crystal SQL Expression? Let's say you have a set of sales data in a Crystal Report. The data is grouped by an order number. Each line item of an order contains a different shipping date due to location of the product. You would like to show, on each line, the maximum shipping date. Not only do you want to display that date, which can be done by simply adding a "Maximum"” summery, you would like to base your record selection off that maximum date. Due to report run time you cannot use the Maximum summery of shipping date in the selection criteria.

Solution

The solution is to create a sub-query using a Crystal SQL Expression. In the formula editor you would write code similar to this;

(
SELECT MAX(shipping_dt)
FROM SalesTable TABLE_Sales_01
WHERE order_no = "SalesTable"."order_no"
)

There are two crucial elements for this code to work properly. First you need to encompass the code with an open parenthesis and a closed parenthesis; this enables the database to know that this is a sub-query inside the main SQL that Crystal passes. Second, an alias is created in the FROM statement for the data (shipping_dt in the SELECT and order_no in the WHERE) that this sub-query is analyzing. The table of the field from the main SQL ("SalesTable"."order_no") that is used to link the sub-query to the main SQL is not referenced in the FROM statement.

* One Crystal SQL Expression sub-query can only result in one expression

**The above SQL code is SQL Sever specific

*** Using sub-queries in Crystal SQL Expressions is not supported by Business Objects

Have a question for our trainers?
Click here to send your questions!