Problem with record selection and left joins

Many report writers begin to explore table joins in Crystal Reports when they aren't getting the results they expect. While changing a join from an inner to an outer is sometimes the easy solution, it may not always yield the results you expect. Imagine you want all your customers in a report, whether they had transactions or not. Where they do have transactions, you are only interested in a selection of transactions

To illustrate with a simple example, let's look at a customer table and a transaction table of orders.

This one-to-many table relationship is common to many reports. This is a problem we have seen on many occasions. There are several solutions you might like to consider if your report has this problem. The default Inner Join will give us only those customers who have orders. To get ALL customers and their orders if they have any, you would use a Left Outer join. Under normal circumstances, the Order table fields will be NULL for those customers without any orders. And that is the foundation of the problem we are about to describe.

Usually, you don't want all the records in the transaction table. At a client site recently, we only wanted the transactions from a selected date range. But we still wanted to see all the customers on the reports. Because we are doing a selection on the date field in the orders table, we restricted our customer selection to only those customers who had orders in the selected dates. You might suggest that those customers without any orders will have NULL in the fields in the order table, so select the NULL values as well. Sure, that will pick up customers who have NEVER had any orders. But customers who have orders, but not in the date selection are still missing from the report. This is because the LEFT join is done in the database prior to record selection. We need a solution that shows all customers, and their orders in the date selection (if they have any). There are several solutions to this. Thanks to Tim Wilson for his help investigating these.

Brute Force Solution

Use a formula on all records After examining and trying some of the other methods, this was the solution we implemented. We expected a performance hit, but the report was fast and gave us the results we wanted.

As faster and faster processors are delivered it is time we used some of that extra power for more productive purposes than 3D helicopter games. We joined the two tables with a left join, but with no selection from the orders table.

Yes, all the transactions were loaded into the report and had to be processed. We calculated our “This Month” total using a summary of an If .. Then .. Else formula.

A Running Total Field would also work fine here. You might try and avoid a report that loads millions of records into one report. But Crystal can handle the volume without any problems. This solution performed well and worked with our CR 8.5 user.

Stored Procedure Solution

We looked at the previous problem from the database side. If Crystal wasn't part of the solution, how would we do this using SQL? The raw data tables won't do what we require, so we need an SQL stored procedure.

* We looked into using SQL to create the left join we needed, and it just didn't happen. If any of you SQL experts out there have a pure SQL solution for this, then let us know.
* If only summary data are required, a sub SELECT would work. But if you need the transactions as well, it is not enough.
* A UNION join also looked like it might provide a solution. With a stored procedure we could select the required transactions from the orders table. Then we could use Crystal Reports 9 or 10 to left join the stored procedure to the master table. But this solution is not available for CR8.5 or earlier and has a big performance hit.

Business View Solution

Business Views are a new feature in Crystal Reports 10. We created a SQL command with the date selection parameters inside a Business Foundation. Left join that to the customer table, and create the required Business Elements and Business Views.

This worked fine in our Crystal Reports 10 test report.

SELECT INTO Solution

One stored procedure solution we thought would work was to use a stored procedure with several steps. The first step did a SELECT INTO to create a temporary table with the only the records we needed, and then it left joined the temporary table to the master table. This would work with any version of Crystal Reports.

The negative side of this solution is that the report can only be run by one user at a time. A second user would reset the temporary table while the first was still using it.

Subreport Solution

Back into Crystal Reports with a main report from the master table, and a subreport for each transaction record to get the data we required. This did work correctly.

But the performance and selection of the transactions for each master record made this the slowest performing solution of any we implemented. Also, you could not do any grouping or primary record selection on fields in the transaction table.

Conclusion

Use the solution that best suits your needs. Each of the above has specific technology and performance limits. But we now have several ways to get that report with the data we want on it.

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.