Using “Table Inflation” to your Advantage

Tuesday 8 May 2007 @ 3:16 pm

I am working on a Crystal Reports project where I have to print multiple packing slips for one report record. The number of packing slips is determined by the quantity field in each record. One way is to do this is to create a fixed number of separate subsections, each holding one copy of the packing slip. You could then conditionally suppress some of the copies based on the quantity needed. This technique works well in some environments but has 2 weaknesses:

1) If the quantity can be very large, you may have to create MANY different sections.
2) The repeating information has to print in the same place as the original.

In SQL environments there is an alternative technique that solves both of these problems. It requires that you have the ability to create a simple table in your database, a table that contains one numeric field with the numbers from 1 to 99 (or a higher number if you think you might need it). You add this table to the report and link it from the Quantity field to the new numeric field. You then change the join type to be “Less Than or Equal to”. If a record has the quantity of 6 it will link to 6 records in the new table, records 1 through 6, causing the source record to repeat 6 times.

Usually, we work hard to ELIMINATE this type of duplication, but this is an example that allows you to use it to your advantage. There are several variations on this process. For instance:

  • What do you do if your quantity is a calculation and can’t be used as a linking field?
  • What if your database doesn’t support this join type?

If you need help implementing this method in your reports, please give me a call.









Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server