The Crystal Reports® Underground

I was helping a customer set the location of several tables from one database (or catalog) to another identical database on the same server. We tried to set the location using the ODBC connection, but they are on the same server so it didn’t change the catalog. We also tried logging out and logging back into the ODBC connection so that we could select a different catalog on the login screen. But this only changed the catalog shown in the connection properties. The table properties still showed the original catalog. When we refreshed the report the data still came from the original catalog.

So we figured we would have to set the location table by table. That would allow us to specify a table within the new catalog. But that is when things got weird. We
Continue Reading »
No tables shown in the Database Expert

And now, another post in my series Crystal Reports vs SSRS:

As part of my research, I am recreating one of my own reports in SSRS. I can already see several things that will frustrate a Crystal Reports user. I have listed the first three below. It may be that my lack of experience in SSRS is showing, but to me these seem like true limitations:

1) When I start a new report in Crystal I can use the Database Expert to quickly explore the data structure. I can log into an ODBC or OLEDB connection and instantly get a list of the tables it has available. Then I can select one or more tables and see the list of fields in each table. I can even
Continue Reading »
Crystal Reports vs SSRS #2

People love to work with Excel, and Crystal does a decent job of reading Excel tables. But there is one huge headache that I have had repeatedly over the years. Excel columns don’t have a data type so Crystal (or more specifically the Excel driver) has to guess the data types of each column. The problems start when you have columns with mix of data types, usually a string column that has some numeric values mixed in. Crystal has to decide (guess) the data type of the column based on the data that it sees. Once it decides on a type, all values that are NOT that type become null.

The problem for me has been that I could never find the rules for the decision. But with a bit of research and
Continue Reading »
Data-type changes in Excel.

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.

A user in Tek-Tips was puzzled. He was viewing reports with saved data and changing the parameters to filter the report in different ways. When the saved data was generated today, he could change the parameters and filter the data as expected. But if he tried to do this on an older report, the saved data would go away.  I have run into this before and so I know the likely cause – a  date-sensitive line in the filter, something like This:

{db.DateField} in LastFullMonth

When the report is refreshed, it uses today’s date to calculate the beginning and ending of the prior month and filters the data to those dates. So a report run in April would only include March data.

But what if that report is saved with data and then
Continue Reading »
Saved data vanishes when you narrow the criteria.

When I started teaching Crystal Reports in 1995 I was also teaching another tool called R&R. In comparing the two I found that R&R had about 20 features that were missing from that year’s version of Crystal.  Crystal had five features I liked that were missing in R&R. But the next year, when the next version of CR came out, the numbers had flipped. Most of the R&R features that I had mentioned were now included in Crystal. That started my transition from R&R to Crystal. So yesterday, when I made my first attempt at comparing Crystal Reports and SQL Server Reporting Services (SSRS) there was a bit of déjà vu.

For the past 8 years I have been able to
Continue Reading »
Crystal Reports vs SSRS

I received this question from a customer last week. Since it isn’t the first time I thought I would mention it here. He noticed that:

1) The group tree missing many dates that have data within the range.

2) The group tree including dates that are outside the range.

The answer is pretty simple. In Crystal, whenever you group on a date field, the default setting for the new date groups is “for each week”. Don’t ask me why. And you don’t even see the setting while you are adding the group, unless you go into Group Options.  Now I am sure
Continue Reading »
Date groups that don’t do what you expect