Archive for the 'Bugs and Errors' Category

Missing dll for reading Windows folders

Tuesday 8 September 2015 @ 9:00 am

One of my customers received an upgrade from CR XI to CR 2011 when he upgraded to SAGE 100 software. He was surprised that the new version was missing the DB connector he used to report off of his file system for document management and version control. I have written about reports that read a Windows folder before. I suggested that he try a custom install to make sure that the option was checked and he reported that there was no option for this driver in his install.

So I had him run one of the existing reports that used this connection to see if it would run. CR gave an error that said he was missing the CRDB_FileSystem.dll. So I told him to move the CRDB_FileSystem.dll from the XI environment to the 2011 environment and then the report ran with no problems.

I am not sure if this dll was dropped from the SAGE distribution, or dropped from the 2011 upgrade but if you are using this type of connection and lose it on an upgrade, you should be able to get it back with the dll from your old environment.

The function to nowhere.

Saturday 22 August 2015 @ 12:19 am

A customer recently asked me how to use the vertical alignment formula functions in Crystal reports. He had found them on-line and was surprised that there were such functions available.  So was I.  But then I typed the following functions into a formula:


Sure enough, all three of them turned blue and were recognized by Crystal Reports as valid functions. But after the initial excitement I realized that having a function does no good unless there is a formatting property where the function can be used. For instance the function CrRed can be used in any color condition formula, and the function crCenteredHorizontally can be used in the condition formula for horizontal alignment. But there is no vertical alignment formatting property in Crystal Reports. So after some more on-line research we figured that there must have been a plan to add vertical alignment options to Crystal.  These function constants were added for them. But then the property never made it into the release and so the functions were orphaned.

And since SAP isn’t planning any major enhancements for standalone CR, I doubt that these functions will find a home any time soon.

Automating “Database > Set Location”

Thursday 16 July 2015 @ 11:34 pm

One of my customers has about 50 reports that all point to the same SQL Server database. When they moved their database they needed to “Set Location” for all 50 reports. Normally you can set all the tables from one connection to another in one step by updating the old connection to the new connection. This should work as long as all the tables have the same name.

But in SQL Server, Crystal sees the table names like this:


So if the name of the database is changed Crystal thinks the table name is different. So when this user tried to “Set Location” at the connection level, she got “invalid object” errors. She learned that the name of the database had been changed when setting up the new server.

When Crystal can’t find a matching table name in the new connection, you have to map each table in the report to the corresponding table in the new connection and update them individually. With 6-8 tables in each report the process would take a few hours. I thought we might be able to fool Crystal into just looking at the table portion of the name, but after trying a few things it didn’t seem like we were getting anywhere.

So I suggested that she check out the report management utilities on my annual comparison. There happens to be one that is only $99, CR Data Source by R-Tag, and it is designed to do just that one thing – Set Location. I did wonder if it would have trouble with the database change but the customer reported back that it did the job and saved her several hours of tedium.

Export file names forced to lower case

Sunday 12 July 2015 @ 11:55 pm

This only affects those of you using older versions of Crystal Reports, up through XI. This includes those of you who have the runtime engine from one of these older versions in your application.

In current versions of Crystal you can export a file to a spreadsheet or PDF and type in any file name you want. If you want to use upper case, lower case or mixed case Crystal will save the file exactly as you typed it. But in older versions the file name is converted to lower case regardless of how you type it. I tested this in v10 and v12 and got two different results. I then checked the ‘Options’ menu and looked through the registry to see if there was any way to turn this feature off. I found nothing.

You would think this would have come up before, but I never noticed it. I guess exported file names, whether in upper or lower case, were never critical to what I was doing. But apparently some people find this frustrating. The only solution is to upgrade to CR 2008 or later.

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.

Next Posts »» «« Previous Posts

Recrystallize Pro