Archive for the 'Bugs and Errors' Category



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:

DatabaseX.dbo.TableZ

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:

C:\Windows\Crystal.

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:

2015-02-04

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.




Next Posts »» «« Previous Posts
Jeff-Net

Recrystallize Pro

remiCrystal reporting solution