The future of Crystal Reports looks a lot like the past. According to a presentation at the SAP Users Group (ASUG), all new features in CR will go into CR for Enterprise, the new version of CR that is found only within the Business Objects Enterprise environment.  Standalone Crystal Reports is briefly mentioned and shows up again in the Q&A section at the end. The good news is that they are going to continue to provide and support a standalone product. There is even a plan for CR 2013. However this is described as a ‘re-branding’ which means that the primary change will be the name.

I have just updated my comparison of server-based scheduling tools for 2013. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.

There are 8 products on the list again this year with a few feature updates and price changes since last year. The article provides a brief overview of each product. It also links to a feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, you have come to the right place.

I often have reports that require me to include multiple copies of the same records. Normally this means using a command object in Crystal, rather than going directly to the tables. And in most cases I write a UNION query which appends one set of records to itself multiple times. But last month I had a report that required 24 copies of the same 2-year dataset.

I started testing the performance and found that getting a single copy of the data took nearly 5 minutes, partly because the data was coming from a view. I then found that adding a UNION of a second copy of the data added another 4 minutes to the query, and each additional UNION added 4 more minutes. It would take nearly 2 hours to get 24 copies of the data, so I had to scrap the UNION idea.

Then I wondered if using a CROSS JOIN might be faster. A CROSS JOIN is when you add a table to a report, but you leave it unlinked. Normally this is a very bad thing to do but it is handy when you need duplicate data.

So I found an unrelated table that had a column of consecutive numbers and wrote a query that selected the numbers from 1 to 7. Then I added the SQL for that small query as a cross-join in my command. The result was 7 copies of every record in the dataset, retrieved in about 5 minutes. Even when I scaled it up to generate all 24 copies of the data, it only took about 7 minutes total. Obviously much better than 2 hours for the UNION.

Now maybe this would be obvious to someone who works primarily writing SQL, but the magnitude of the difference was a surprise to me. I will be using CROSS JOINS whenever possible, now.

I try to avoid copying text directly from a formatted document (Word, PDF, etc) directly into a Crystal Reports text object. While Crystal can accept many of the formatting characters from other applications, they can sometimes cause unpredictable behavior.

I recently had a customer show me a text object in Crystal that refused to display the first few characters of the text. We would see these character when the text object was in ‘edit’ mode but they would disappear when we exited and went to preview. We couldn’t find the formatting characters that were causing this behavior, and if we copied the text to a new text object the behavior followed. We finally solved it by copying the text to Notepad, and then copying from Notepad to a new text object. This stripped out all the hidden formatting.

So now whenever I have to transfer formatted text to a report, I first paste it into a simple text editor like Notepad. Then I will copy the text and paste that into Crystal. I can then add any needed formatting within Crystal Reports.