I have written several times about using inflation tables to force duplicate data. I even posted some SQL queries that generate inflation tables in Microsoft SQL Server. Today I needed an inflation table for an Oracle based report and didn’t have one in my library. So I did a bit of research and found this one mentioned frequently. It worked well for my needs today:

SELECT ROWNUM FROM DUAL
CONNECT BY ROWNUM <= 100;

The “100” value can be replaced with whatever number you need.

So when do we use inflation tables? I use them whenever I need to turn a single record into multiple records. Here are the common uses I have seen:

• Print multiple labels for a single row based on the quantity value in that row.
• Splitting some orders into multiple records for shared commissions.
• Creating a series of dates from a single date record.
• Separating a multi-value field into separate single value records.
• To repeat all records several times, grouped differently each time.

If you have a task like this and want some help, give me a call.

I have written several times in the past about script errors on the Crystal Reports “Start” page. The message is:

“An error has occurred in the script of this page”

I have seen at least 3 customers experience this problem in the past week so I figure it is time to mention this again. You will see this error when you first open Crystal Reports or when you close a report and Crystal Reports reverts to the “Start” page.  Some users think there is a problem with the report they are trying to open, but in truth this error is unrelated to any reports.

The error is usually caused by one of SAP’s servers having a problem.  If you want to prevent this error you can follow the directions in this blog post on how to prevent Crystal from requesting an internet connection.

I found a limit that exists in Crystal that I didn’t know about before. Even if I had known about this limit I wouldn’t have ever expected to exceed it.  But it just popped up in one of my reports.

I created a report related to donors for a large organization. One part of the report needed to show the top 10 donors from a very large pool of people. I decided to use a cross-tab with a “Group Sort” so we wouldn’t need another subreport.  It tested just fine on the sample data set.

But, when they ran the report on the full table they received an error saying a cross-tab couldn’t have more than 65K rows or 65K columns. Apparently, the data set included over 100K donors.  To find the top 10 donors, the cross-tab would have to initially create a row for every donor. Even though I only needed to see the top 10, the cross-tab needed to see ALL of them, and that exceeded the limit.

Instead I created a subreport and grouped by donor. Then used the “Group Sort” on the actual groups, rather than a cross-tab. Fortunately this limit doesn’t apply to groups in the report, only to groups (rows or columns) in cross-tabs.