Crystalkeen logo
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
 

CrystalReports
on Steroids

Crystal Reports: Join tables together with a UNION join

Sometimes, we need to have a record appear in more than one place in our Crystal Report. Examples of this include stock movements in and out of the warehouse, or call centers where calls come in and get resolved.

If you want to display activity or status on a series of days, the same call or stock item needs to appear on several days.

One solution to this problem is to use SQL to create a different view of the data. A UNION join will combine a series of tables into one recordset for your report:

SELECT "In" as CallType, InDate as CallDate, CallID from Calls

UNION ALL

SELECT "Out" as CallType, OutDate as CallDate, CallID from Calls

The UNION ALL includes duplicate records, otherwise a UNION is adequate. You can also combine several of these if you want stock status at different dates. You canít do this with the SQL inside Crystal Reports but you can use this technique if you can create SQL views or queries in your database. You can also do it in the Crystal SQL designer.

 

What is the difference between UNION and UNION ALL

When we use SQL to combine two tables using a UNION statement we usually use "UNION ALL" because thatís what seems to work properly. But why? What does the UNION ALL do that the UNION by itself does not?

UNION will do a reprocessing of the records to remove any duplicates, while UNION ALL will skip this step. For that reason UNION ALL will be a lot faster than a UNION. Some databases discourage UNION and will insist that a UNION ALL is used.

 

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.

Authorship

Except where an author's name is given at the start of the article, all of these articles were written by Mo Naughton or Bruce Ferguson and edited by Mark Lamendola. Mo is a Crystal Reports consultant, trainer, and developer for Chelsea Technologies, Inc. Bruce Ferguson is a Crystal Reports consultant, trainer, and developer for CrystalKiwi, Inc. Mark Lamendola is a writer and editor with over 15 years experience in professional and trade publications.

 

  • Questions? Please write to mark@crystalkeen.com. We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to mark@crystalkeen.com and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.