My last post was about inflation tables and included a quick and dirty method for deploying one using a spreadsheet.  I started with that method because anyone can use it and it should work in any report.  But there are better methods you can use most of the time.  The ideal method is to create an actual table in the same database as your report data, but not everyone has the ability or permissions required to create tables. So here are two alternates.

Re-purpose an existing table.
Any table that has a field with unique consecutive numbers can be used.  For example, in the Xtreme Sample Database that came with older versions of Crystal Reports there is a customer table that has consecutive Customer IDs from 1 to 270.  If I wanted an inflation table of 150 records I could add the Customer table to the report, make sure it is not linked to any other tables and then add a criteria that says “Customer.ID is between 1 and 150”.  After adding this to the report I would get 150 copies of every original record.

It often takes a bit of hunting to find a suitable table for one of my customers.  Here are characteristics of the ideal inflation table and the field withing it that holds the consecutive numbers:

1)The table doesn’t not change much.  I want a range of records that do not change so I don’t have to worry about my report not working in the future. I find code tables for decoding required fields often work well.

2) The table is not much bigger than the number of records you need.  You don’t want the database to have to select 50 records out of thousands every time you use the inflation table.

3) They field is indexed.  One way to tell if a field is indexed is to look at the table in the Crystal Reports linking window.  Indexes are shown as little colored tabs.  An indexed field will select records faster.

4) The field contains unique values (no duplicates).  If there are duplicates you will need to write a command to “SELECT DISTINCT” and use the command as an inflation table.

5) The field has consecutive numbers starting at 1.  If the field has consecutive numbers but they start at another number you have to write a formula in the report to adjust them.  For instance if the consecutive numbers went from 1000 to 1150 then the formula would subtract 999 from each value.  The formula would give you a series of consecutive numbers from 1 to 150.

Use a SQL Command:
You can also create an inflation table with a SQL command.  Here are two SQL commands that work in Microsoft SQL Server using different techniques. The first generates up to 2000 rows in any SQL Server database.  It uses an undocumented table that is currently found in all SQL Server installations. I found a great article comparing this and several similar methods.

SELECT Distinct Number
FROM master..spt_values
WHERE Number between 1 and 2000

The next two SQL commands were submitted by Michael Hurwood, a Crystal Developer at Manufacturing Software.  These commands create a temporary table in memory and don’t reference any existing tables. Version A generates a sequence from 1 to 256, while version B generates a sequence from 1 to 65536 (the square of 256).   You can trim the results down to a specific number by changing the number in the WHERE clause.

Version A:

;WITH
N1 AS (SELECT 1 AS x UNION ALL SELECT 1),
N2 AS (SELECT A.x FROM N1 AS A, N1 AS B),
N3 AS (SELECT A.x FROM N2 AS A, N2 AS B),
N4 AS (SELECT A.x FROM N3 AS A, N3 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY x) AS n FROM N4)
SELECT *
FROM Nums
WHERE n <=256

Version B:

;WITH
N1 AS (SELECT 1 AS x UNION ALL SELECT 1),
N2 AS (SELECT A.x FROM N1 AS A, N1 AS B),
N3 AS (SELECT A.x FROM N2 AS A, N2 AS B),
N4 AS (SELECT A.x FROM N3 AS A, N3 AS B),
N5 AS (SELECT A.x FROM N4 AS A, N4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY x) AS n FROM N5)
SELECT *
FROM Nums
WHERE n <=65536

There are probably equivalent commands in other flavors of SQL.  If anyone wants share a version for Oracle, MySQL or some other flavor I will post them here.