Archive for the 'Bugs and Errors' Category

Reset ‘locked’ SQL in Crystal Reports.

Sunday 10 January 2016 @ 5:15 pm

I was helping a customer troubleshoot a misbehaving report. He could add criteria to the report, but when he removed the original criteria from the selection formula the it was still applied. So I looked at the SQL that CR was generating and found that it was not being updated based his our changes.  After a few more tries my customer asked me about an unfamiliar button below the “SHOW SQL QUERY” window. Only then did I noticed the extra button that said RESET.   I had not seen that button in about 10 years, but I now knew the problem with this report.

This report was originally created in an early version of Crystal, probably v8.x. In these early versions you were allowed to tweak the FROM and WHERE clauses in the automatically generated SQL.  This is no longer allowed because now we can create SQL Commands.  Back then, when you modified the SQL it became ‘locked’ and Crystal would no longer update it. If you added more selection criteria the new criteria would not be incorporated into the SQL. Instead it would be applied after the data came back from the database.  When you wanted to revert to automatically generated SQL you would hit the RESET button.

In current versions of CR the RESET button is not needed so it doesn’t normally appear.  But it will appear automatically if you open an old report that has locked SQL.  This allows you to eliminate the use of an obsolete feature.  So I checked the SQL to see if the original tweaks were still essential.  There were no UNION queries, subqueries or filters built into outer joins. So I reset the SQL and the RESET button disappeared. After that the report behaved like a normal report.

Troubleshooting a missing record

Thursday 17 December 2015 @ 10:03 am

I had two different cases in the past week where a customer wanted to know why a specific record was missing from a report. I used the same process in both cases and thought I would share it. So say a report should include Order 273 and it doesn’t.

The preliminary step: missing or hidden?

Before we start we want to make sure that the ‘missing’ record is really missing and not just hiding in the report.  Go into the select expert and add a new rule that says [ {Order ID} = 273 ] and then refresh the report. When it is done look at the record count in the status bar at the bottom of the screen. Do NOT look at the report totals or anything else for this step. If the number in the status bar is not zero than the record is not missing, it is hidden. It might be suppressed or it might have been grouped in an unexpected place, or it might be filtered out using group selection, but it is already in the report. If the record count is zero then you have a missing record and can start the process below.

Selection Criteria or Inner Join:

There are really only two things that can prevent a record from being included in a report, an Inner Join or the Selection Criteria. So if Order 273 is not in the report then there is either an inner join to a table with no matching records, or Order 273 does not qualify on one of the report’s criteria. So we need to back these things out until the record shows up. Go into the selection formula and cut out or comment out the entire formula. Then add the test rule that I mentioned above:

{OrderID} = 273

Refresh the report with that criteria and see if the record count in the status bar shows more than zero records. If it does then the problem is in the selection formula. Leave the new rule in place and add one of the original rules at a time back into the selection formula.  See which one makes the record count drop to zero. If you find a suspect rule, put back all of the other rules and see if Order 273 still shows up. This way you know for sure that only one rule was the problem.

If you have removed all of the rules in the selection formula (except for the test rule) and the record count is still zero, there is one more place to check. Criteria can sometimes hide in the group expert if you use specified order. If all of the groups use ascending (A) or descending(D) order you can skip to the “Joins” step below. But if any groups show “S” for “Specified Order” go into the Options button for that group and check the “Others” tab.  An others setting of “discard” could eliminate Order 273. Change this to say “Leave in their own groups”, refresh and check the record count.

If after all that the record count is still zero then the problem has to be an inner join. Go into the Database Expert (Links tab) and hit the “auto-arrange” button. If the table used in our test criteria is not on the far left, then reverse any joins  that come out of the left side of that table.  To reverse a join, right click on the line, select reverse join, and then hit “Auto-Arrange” again. Repeat this process until the table that contains the test criteria field is on the far left. Then take all the join lines and change them to Left Outer Joins. Now the record should show up. To figure out which join is the problem you can start changing the Left Outer joins back to inner joins, one at a time, and refresh each time. Start with the joins that come out of the far left table. Then change the joins that go from those tables to other tables, and so on. When the record count goes back to zero, that will be the table with unmatched records.

And, if you need help applying these steps, you can always give me call to schedule a session.

“Failed to Export the Report” (PDF)

Saturday 5 December 2015 @ 10:47 am

I had a user ask me about this error. He was using Version (the original release), so I suggested that he apply some updates or check for font problems. He did some digging and found that if her removed Windows Update KB3102429 the problem went away. That led me to this discussion on the same topic which nails down a font problem.  Since XI is still widely used, and since this might affect other versions, I thought this might help someone else.

And thanks to  Josh Pals, Controller at United Gear and Assembly, Inc., or sharing his find.

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.

Next Posts »» «« Previous Posts

Recrystallize Pro

The Expert Series