Archive for the 'Bugs and Errors' Category

Formula names that end with spaces

Thursday 4 June 2015 @ 9:23 pm

I have written before about using auto-complete when writing Crystal Reports formulas. I have actually written about it twice because I sometimes forget about my own previous posts).

During a recent remote consult I was writing a new formula.  I used auto-complete to insert an existing formula into the new one.  My instance of Crystal Reports immediately crashed.  I thought it was a fluke so I reopened Crystal and repeated the same steps. Crystal crashed a second time.

So I had the customer send me the report and I opened it on my own computer. I added the same formula using autocomplete and it didn’t crash, but I did get an error message saying “Invalid Argument”.  That happened every time I tried to add that field using auto-complete.  So I used the mouse to select the field and that worked fine. That is when I noticed that there was an extra space at the end of the formula name. I have seen one bug with spaces at the beginning of a formula name so I figured the space on the end might be causing the new problem.  I tried other formulas and found that auto-complete worked fine as long as there was no space on the end.  I guess in some environments that error can cause CR to completely crash.

I can think of no reason to intentionally end a formula name with a space. But when I am duplicating and renaming a series of formulas it is easy to leave a space at the end and not notice. And these spaces won’t be very obvious when you look at a list of formulas. So if you get one of these behaviors, this is one more thing to check.

Merging instances, another performance boost

Saturday 23 May 2015 @ 10:19 am

Last month I wrote about report that took 20 minutes to run, and how using the right indexes brought the run time to under a minute. Yesterday I was able to get another 20-minute report to run in under a minute by fixing a different issue.

At first I wasn’t sure if the run time could be significantly reduced. The report had to pull tables from two different databases, and that is usually a performance killer. So I checked the SQL being generated by the report to see how the two queries were being divided. Instead of two separate queries there were four. One of the two connections was showing up as 3 separate queries in the SQL – as if it were three different connections. So we went into the menu at “Database >> Set DataSource Location” and found that the report was using three different instances of same connection. Once we set all three instances to the same instance, the report ran in under a minute.

So why would tables that all come from one database connection end up under different instances of that connection? Usually I see this happen when the report is designed in stages. A few tables are added, then the user logs out and then more tables are added at a later time. Each new login can be treated as a separate instance of the database. And when that happens Crystal will make a separate query for each instance and combine the data in local memory.  This is very inefficient when compared to a single query that is handled entirely by the database.

Having two different instances of the same DB causes the same performance problem as connecting two different databases. But while it is very difficult to improve performance with two different databases, merging multiple instances on one database is usually pretty simple.

Testing a report that has to be run from application

Tuesday 12 May 2015 @ 8:06 am

There are environments where the only way to test a report is to run it from within an application. The steps to deploy a modified report vary, but they usually involve placing the modified report into a specific folder and/or publishing the report into the application. Sometimes the users aren’t clear on the steps. So when a user reports that a modified report returns the exact same result as the original, I have to wonder if they are actually still running the original. It may be that they missed a step when deploying the new report. Or it may be that the application still has a cached copy of the original report in memory and needs to be restarted to see the modified report.

The most reliable way to confirm that the report being run is the latest version is to mark the report with something obvious. For instance I often take a text object from the page header and underline it. If they run the report from the app and don’t see the underlined object then they know that they are not deploying the updated version correctly. Most people start out thinking this test is a waste of time. But more often than not we find that there is some key step that they forgot. This simple step has saved hours of troubleshooting time.

And if you have to work regularly on reports like this, you should read my previous article on exporting to RPT format.  That might allow you to bring data from the application back to the Crystal Reports designer so that you can immediately see the results of your design changes.

Missing Sage custom functions

Wednesday 22 April 2015 @ 10:38 pm

This weeks puzzle:

A customer upgraded to Sage (PeachTree) v2015 last week. Everything seemed to work fine, but their most important Crystal Report wouldn’t run because it uses Sage custom functions, and those functions were not showing up in Crystal. The Sage software has a button to install these custom functions, but clicking this button didn’t change anything. Sage support couldn’t solve the problem so the customer called me.

I know that all Crystal custom functions come from DLLs and I assumed that this Sage DLL was either missing or in the wrong place. The customer asked Sage support for the name of the DLL so we could search for it. They sent him lots of information, but not the file name.
So the customer did a fresh install of Sage v2015 on a local PC, and everything worked correctly. So I opened up Crystal and could see the Sage custom functions listed together – along with the name of the DLL.  We searched for that file (U2Lpeach.dll) and found it in:


We then checked that same folder on the server and found that the DLL was in that same folder. But yet when we opened Crystal on that same server the Sage functions were not visible.  Crystal had several other custom functions that were working, so I searched for those DLLs and found them in a different folder on the server:

C:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\win32_x86

Since we knew that Crystal could read the DLLs in that folder, we copied the Sage DLL and pasted it in with the others. Once we restarted Crystal the Sage functions appeared. The difference probably has to do with a windows environment variable somewhere, but rather than mess with the server settings we decided that a duplicate DLL was workable solution.

Calculating age for a ‘leap-day’ birth date

Thursday 19 March 2015 @ 2:33 pm

I have a formula for calculating an age integer that has been on my site for a decade.  Yesterday I thought I had found a bug in it.  After I fixed the formula I did some more research and found that the original was correct in most situations. The question centers on cases, when a person is born on a ‘leap-day’ (February 29) and when exactly they turn a year older in non-leap years where there is no date of February 29.  In the UK and in Hong Kong their age would increment on March 1. In New Zealand or Taiwan their age would increment on February 28.  In many other jurisdictions, like most of the US, this isn’t specified anywhere, although the norm seems to be March 1.  My original formula will increment them to the next year on March 1.  But I have added an optional 5 lines of code to the formula for those who want the change to happen on 2/28.

Spreadsheet doesn’t list any tables

Sunday 15 March 2015 @ 10:30 pm

I recently worked with a customer who needed to incorporate some Excel data into a report. Excel tables are not my first choice, but sometimes it is the only practical solution for the client. Since these were XLSX files we setup up an ODBC connection. But when we connected from Crystal we could not see any tables under that ODBC connection. We were thinking about re-saving it as an XLS when I did a quick online search and found the answer we needed in a forum post.

We had to go into File > Options > Database tab. Then under Tables and Views we had to add a check mark for ‘system tables’. Then the spreadsheet table showed up. I was surprised, because I have created many reports that read spreadsheets and I don’t ever remember having to do that. It is possible that there was something unique in that environment that required this change, but I never argue with success. And, if you ever run into something similar you have one more thing to check.

ToText() function throws in an extra space.

Thursday 26 February 2015 @ 5:48 am

You can use the ToText () function to turn dates into strings, which is handy in cases when you need to combine a date with other characters. For instance I use this pattern in IF-THEN-ELSE formulas, when I want to give the user a parameter to choose sorting on a date value vs a string value:

else ToText ({Tale.Date} , 'yyyy-MM-dd')

This turns the date into an 8 character string that can still be used as a sort field and will still sort the records in chronological order. The [yyyy] returns a 4-digit year, the [MM] in the middle returns a 2-digit (0 padded) month and the [dd] on the end returns a 2-digit (0 padded) day. The dashes can be any character that you want to use as a divider. So using the formula above the date value 2/4/2015 ends up looking like this:


For purposes other than sorting you might want a 2-digit year or a month/day value that is not zero padded. In that case this formula:

ToText ({Tale.Date} , 'yy-M-d')

creates a date that looks like this:

15-2- 4

The digits are correct, but if you look closely you will see an extra space in front of the days value. For some reason a single-digit month value does not have a space but a single-digit day value does have a space. This must be a bug since I can’t think of any reason for the difference. And it is a long standing bug since I just tested this in v10 (2004) and v8.5 (2001) and both have the exact same behavior.
If you ever encounter this and want to remove the space from in front of a single digit day, you have to use a replace function like this:

Replace ( ToText ({Tale.Date} , 'yy-M-d') , " " , "" )

You should activate the AutoSave feature

Friday 16 January 2015 @ 10:09 pm

One of my favorite sayings:

Good judgement comes from experience.
And experience comes from bad judgement.

I was reminded of the value of AutoSave this week when one of my reports froze up on me before I had a chance to save a large number of changes. I waited for it to come back and even went to lunch. But an hour later the report was still frozen. Normally I am pretty good about saving my work often, but I had been concentrating on a problem and lost track of time.

The machine I was working on did not have the CR AutoSave feature activated. But somehow the report that froze did not freeze the rest of  Crystal. I was able to use the menu and save my other reports, but I could not save the report I had been working on.

Since the menu was still working I took a long shot and activated the AutoSave feature. After a few minutes I noticed a new RPT file in the temp directory and it had all of my most recent changes. So even though it would not let me save the “frozen” report, CR was able to AutoSave it for me.

And as if that wasn’t a strong enough message, I had three client workstations crash on me this past week. None of them had AutoSave activated. So allow me to make a public service announcement:  Read my prior post on how to use the AutoSave feature and then activate it in your environment.

The ) is missing ?!?

Saturday 27 December 2014 @ 12:32 am

If you make a mistake in a Crystal Reports formula, the error messages are usually pretty helpful. But every once in a while the error can send you on a wild goose chase. The message above is a good example. It will appear when you have an opening paren without a closing paren. But it will also appear in the following formula, even when the parens match:

NumberVar Save01;
NumberVar Save02;
NumberVar Save03;

if {@criteria}
then (Save1 := 0; Save02 := 0; Save03 := 0)

Now in this formula it is obvious that the parens are not the problem. So why would the error message mention a missing paren? This happens only when you have a specific combination of factors – a Continue Reading »
The ) is missing ?!?

Group Tree takes you to the wrong page

Saturday 20 December 2014 @ 6:30 pm

I recently had a report grouped by Employee with one page per employee. Each Employee showed up in the group tree on the left of the screen. But every time we clicked on an employee in the group tree, we ended up on the page of the employee just BEFORE the one we wanted. I thought it was a glitch to I changed the group options, then closed the report and eventually closed CR completely.  The problem didn’t go away so I realized it was something in the report.

After some experiments I realized what was going on. The report was printing one page per employee and printing only the group footer. But there was no actual “new page after” checked for the group footer. There was one employee per page only because the group footer was so large that only one group footer could fit on a page. So it mimicked a page break.

But the group tree doesn’t take you to the group footer of the group, it takes you to the Group Header. And in this report the Group Header for each employee was suppressed. So as soon as each Group Footer finished on a page, the next Group Header would start right below it. And because it was suppressed, it would always fit on the prior page.

So the Group Tree took us to this (invisible) Group Header which was always on the page before the corresponding group footer. All we had to do to solve the problem was check “new page after” for the Group Footer and that moved the (invisible) Group Header to the next page, right above the corresponding Group Footer. And then the Group Tree worked as expected.

Next Posts »» «« Previous Posts

Versa Reports

remiCrystal reporting solution