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.

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

I use cross-tabs quite a bit and there is one thing I find frustrating. It is when you have a perfectly laid out cross-tab and realize that the row or column field needs to be changed. Changing a row or column field tends to reset the formatting of the cross-tab cells and often changes the size as well.

One time when I was trying to change a cross-tab row from one formula to another formula I realized that I could avoid losing my formatting if I just copied the contents of each formula into the contents of the other, reversing the formulas. Once I had reversed the contents, I renamed the formulas so that the names matched the contents as before. The cross-tab didn’t see either of these changes as a field change and so the formatting wasn’t affected.

So, now whenever I create a more complex cross-tab, I created dedicated formulas for the row and column fields. I usually name them Xtab Row 1, Xtab Row 2, etc. That way I can change the contents to any field or calculation,   without having to worry about the cross-tab losing any formatting.

This is similar to the technique I called ‘feeder’ formulas in a prior article.

When you do something one way for a long time, it can be hard to get out of the rut. So even though the Crystal formula editor has a pretty good ‘auto-complete’ feature, I still don’t take full advantage of it. I still tend to scroll through the list of fields. But recently I have started to take advantage of auto-complete to speed things up.

The auto-complete in CR has two components. One is for fields which was introduced in CR v12(2008). The other is for reserved words like functions and was introduced in CR v10.

Fields:
If you want to enter a field you simply Continue Reading »
Using auto-complete in Crystal Reports formulas

If you have worked with charts you know that the font sizes can be a bit tricky. With most text objects and fields on a report, the font size is independent of the object size. You can make an object bigger but the font size doesn’t change.  But with charts, all font sizes are relative. Make a chart bigger and you automatically get bigger fonts for all the text in the chart.

This poses a challenge for getting labels or legend entries in your chart to be the same size as the text outside the chart. You can’t simply set them to the same number because the chart labels are relative. Setting it to size 10 may not Continue Reading »
Font sizes inside charts

I was working with one of my customers on reports they needed to deploy in Japan. The customer asked me how to convert the rulers in design and preview to use metric units. I had never done this before, but I knew there was a setting that mentioned this in the Page Setup window (File>Page Setup) so we went there first. That changed the page sizes to metric units but didn’t have any effect on the the design or preview rulers. Next we went to the “View” menu and tried changing the Product Locale to Germany, since that country uses metric units. This changed the ruler to metric, but it also put the entire menu and all the GUI labels into German. Right below this menu option is a similar menu item called “Preferred Viewing Locale”. It wasn’t really clear how this was different but we tried that next. That changed the rulers to metric without changing the language of the product.

On my own installation of CR I only have one option in Product Locale (English), but I have dozens of options in the “Preferred Viewing Locale”.  It appears that these are installed automatically, while the full Product Locales are only installed if you select them at the time of install.   My customer had the full list in both places.

Two different customers contacted me recently with similar questions.

The first customer had a subreport that connected directly to the tables. She thought she could speed it up by changing the subreport to use a SQL command instead of tables.  But the command made the subreport take even longer.

The second customer asked me why her ERP vendor had created all of the reports using tables instead of using views and stored procedures. She quoted her consultant who said, “it’s like sending the whole mine to Crystal instead of a wheelbarrow.”

But I never assume that handwritten SQL (view, stored procedure or command object) will improve the performance of a report. There are some situations where Continue Reading »
Table-based reports vs custom written SQL

Most users know that you can split a section in Crystal to get subsections (e.g. Report Header a, Report Header b, etc.).  The standard ways to create a subsection are to:

A) Go into the section expert and hit the INSERT button at the top of the window.

B) Right-click on the name of a section and select “Insert Section Below”.

Not everyone knows the third way, which is the one that I find the most useful.  It is especially useful when you need to put some additional space in the middle of a large section.

Say you are working on a full page form and Continue Reading »
The third way to split a section