Adding an inflation table (Part 1)

Saturday 22 April 2017 @ 10:23 am

I have a couple of really handy calendar report templates that I have sold to several of my customers. They are mostly simple to deploy but there is one tricky part, and that is that they require an “inflation table”.  This is a table that is added to the report to force each record in the original report to create a series of numbered duplicates.  The only reason that I consider it a challenge is that there are several ways to create/find an inflation table and some work better than others in different environments.  The ideal solution is to create a new table within your database, and index it on the inflator field, but not everyone is allowed to do that, so here is one quick and dirty method that uses a spreadsheet as the inflation table:

1) Create a simple spreadsheet that has one column with a heading of “inflator”
2) Type the numbers from 1 to 20 on the 20 rows below the heading
3) Save the spreadsheet
4) Follow the steps in this article to add the spreadsheet to the report
5) Make sure there are NO links between the spreadsheet and any of the existing tables
6) Add the new field “inflator” to the details band of the report

When you run the report Crystal will take every record in the report and create 20 duplicates of it, and these will be numbered from 1 to 20.   If you want only 9 duplicates you can go into the selection formula and use the inflator field in a rule something like this:

{Sheet1.Inflator} <= 9

Now you will have 9 duplicates of each row.

You can also vary the number of duplicates based on a field or calculation in the report.  My calendar reports use the number of weeks for each event.  The selection formula would say:

{Sheet1.Inflator} <= {@Weeks}

Events that are 3 weeks long will have 3 duplicate records in the report, one for each week.

There are two primary downsides of using a spreadsheet for inflation. You might see poor performance when dealing with large volumes of data.  And if you have to move the report to another environment you have to move and reconnect to the standalone spreadsheet.   That is why my next article will show you how to identify existing tables in your database that can serve as inflation tables.

Leave a Reply

Recrystallize Pro

Crystal Reports Server