Normally if there is an unlinked table in one of my reports it is there by mistake.  However, I have recently been reminded of some of the neat things you can do with unlinked tables, as long as you are careful.  The official name for having unlinked tables is a ‘cross join’.  The data that is returned from a cross join is known as a Cartesian product, which means every possible combination of records between the two tables. That is why you have to be careful.

So say you have a customer table with 100 customers in it and you use that table to create a report listing just the customer name.  Without adding any filters you would get all 100 records in the report.  But then you add a second table that doesn’t link directly to the first, something like the master list of products.  If this table has 25 products in it then the cross-join for these two tables would be 2500 records (100 customers * 25 products).  Each customer would now have 25 records, one for each product.

So when can you use a cross join?

1) When adding a “Company Info” table (or something similar) to a report.  The “Company Info” table usually has only one record to store the software owner’s company name and address, etc.  There might be other single record tables in your system.  You can add these types of tables without a link, because with only one record it causes no inflation.  Even if it has two or three records you can filter it to the record you want to use so that it still returns only one record.  This way you have more columns available for your report.

2) Intentionally inflating the data.  You could use this method when you want to print a second “file” copy of every invoice, or when you want to generate duplicate copies of shipping labels.  You simply add an unlinked table with two recordsas a cross join (or a table you can filter to return only two records).  This causes your existing data to double in size.  Adding a cross join to a three-record table will repeat the data three times, etc. Just remember to add at least one field from the cross join table – otherwise Crystal will ignore your cross join.

3) Filling in missing groups.  Some users want every group to appear, even if there is no data for that group. This can be especially tricky if you have several group levels, because you need lots of combinations.  One option is to use a cross-join to quickly create all possible record combinations.  This will probably require using a command so you can union your existing results to the cross join results.

I plan on writing up a more detailed discussion of this, with examples, to include in Expert Techniques volume 5 (in the works).