The Crystal Reports® Underground

One of my customers has many complex reports that we have created over the years. In the past few weeks one of the reports started behaving oddly. Opening the report with only 55 records of saved data caused Crystal to freeze for 8 minutes (not responding). Making a simple change in preview, like moving or sizing a field, would cause a similar freeze. The report wasn’t accessing the database so I wasn’t sure what was causing the delay. The report uses several arrays and some looping logic so a glitch in a formula could theoretically cause extra processing time.

As a test, we opened an earlier version of the same report. We found that this version behaved normally.  I took the two versions, exported them both to “Report Definition” format, and then did a text comparison of the two files.  This showed me all the formula changes we made to get to the current version. I added all those same changes to the old report, one at a time, to see if I could identify a specific change that cause the odd behavior.  When I was done the old version was virtually identical to the current version and was still behaving normally.

To confirm they were the same, I also compared the output of the two reports.  This time I exported the report output of both reports to text files. I did another text compare and found the output was also identical. This confirmed that the issue was the result of a glitch in the report file itself, some internal corruption that didn’t prevent the report from running.  Not sure I have seen that before.  Since we now had a working version we just scrapped the faulty report and went forward with report that worked. The main lesson in this, other than sharing the process, is the value of having historical versions of reports. The prior version gave us a window into when the glitch occurred and allowed us to recover without starting from scratch.

Even on simple modifications, I usually rename a report before making changes.  When problems show up I can run the original to determine if the issue was already in the original or was the result of the changes.  For a single update I might just add my initials to the modified report. For long development projects I like to add a sequence number to the end of the file name (01,02,etc.).  Some users prefer to add dates in the file name but I prefer shorter file names that sort cleanly.  The modification date is always visible in the folder.

Those of you who have been using Logicity as a report viewer or scheduler are probably aware that Logicity has reached ‘end of life’. SaberLogic (which is now Bezlio which is now Docket) has announced that they are no longer selling licenses for Logicity. Looking at the site I no longer see the ability to download the free version. Here is the official statement:

How does this affect you?
⦁ The ability to purchase licensing was removed from our website on 12/15/23.
⦁ You will not be charged for your upcoming renewal once your current subscription term completes.
⦁No renewals were processed effective 12/5/23.
⦁ Your current access and subscription is your to keep. Current Logicity access will not be affected.
⦁ Logicity support will not be available beyond your renewal date. All Logicity Support channels will be removed 12/31/24.
⦁ No product updates will be applied to the current Logicity version.

If the product is working for you then you can probably continue using it indefinitely. If you run into issues and need to switch you can read about similar products using these links:

Crystal Reports Viewers

Desktop Scheduling Engines

A customer Emailed me a report that had a ‘Comment’ field. The field in the application allows the text to be formatted by the user, so they can specify the font, the size, make words bold, etc. The application stores these properties in HTML. The report uses HTML interpretation to display the comments in the appropriate format.

The customer asked if the report could ignore the HTML font and have all of the comments appear in Arial 9. He had tried choosing the font in the report, but with HTML interpretation turned on his choice was ignored. Turning off the HTML interpretation would expose the raw HTML in the output.

My first attempt worked in most cases. I wrote a formula that searched for the font size tag and replaced the number after it with a 9. This worked fine if there was only one font instance in the comment, but then I found comments with multiple instances. I would have had to write a loop to do it as many times as needed.

Then I wondered what would happen if I simply eliminated the words “font-size” from the entire comment. I wrote the following formula:

replace ( {tbl_abc.Comments}, 'font-size:', '' )

The result showed me I did not have to remove the entire tag. HTML is apparently forgiving enough to ignore any tag it doesn’t understand. Removing that key part of the tag meant the HTML engine no longer knew what to change so it skipped that tag. I did the same thing with [font-family] by using a nested replace and that allowed HTML interpretation to still work for other properties but not assign  font properties:

replace(
replace({tbl_History_Master_Newman.DETAILS},'font-size:','')
,'font-family:','')

That allowed me to specify any font or size I wanted in the report.

One of the perks of using Crystal Reports is the number of third party tools that have been developed to work with the software. My Product Links page has links to more than 70 items.

So it came as a surprise when a user pointed me to a free tool that I had never heard of. This user had asked me if there were tools that would run and export reports from the command line.  I pointed him to my ‘Viewers’ comparison page which has several options. He replied that he had decided to use Crystal Reports Helper which lets you run reports for the command line and export them to a handful of formats. Because it runs from the command line the scripts can be saved in .bat or .cmd files and run from a scheduler. It is a free tool and I had never heard of it.

Crystal Reports Helper was created by Big Ideas Computing Ltd. It was initially created for users of their software application, named dbFront. dbFront lets you quickly build simple web facing applications that allow users to enter and update data. Crystal Reports Helper has since been released it to everyone for free, all you have to do is request it from the vendor.

And thanks to Rich Head of the Greater Iowa Credit Union for sharing this link with me. I haven’t tried Crystal Reports Helper but Rich said it worked fine for him.

A few years ago I started documenting the syntax differences between the main flavors of SQL. This, eventually, turned into the SQL Functions cheat sheet I have made available on my site (and still refer to regularly).

The main challenge I had creating the cheat sheet was that I didn’t have all these databases installed to experiment with. I had to ask volunteers who worked in specific environments to provide syntax examples for their flavor of SQL.

I was recently researching SQL syntax when a poster linked to a “SQL Fiddle” he had created.  SQL Fiddle is a site that allows you to ‘fiddle’ with SQL statements online, using a variety of common SQL languages. In many cases you can test statements using multiple versions of the same language. That led me to a handful of other sites that do the same thing with a different mix of languages. Here are three of the most complete ones I found, along with the languages they support:

http://sqlfiddle.com
SQL Server, Oracle, MySQL, Postgre, SQLLite

https://dbfiddle.uk
SQL Server, Oracle, MySQL, Postgre, MariaDB, SQLLite, DB2, Firebird, TimeScaleDB, YugabyteDB

https://sqlize.online
SQL Server, Oracle, MySQL, Postgre, MariaDB10, SOQOL

Note, these databases don’t always come with sample tables. You might need to run a statement to create the table(s) you want. Or you may be able to run your queries against the system tables.

One of the managers at SAP recently shared a link with me that I had never seen before.   It is a comprehensive FAQ page for the Crystal Reports product family.  If looks like about 40 pages of material and covers a wide variety of topics.  (For instance, A5 has a searchable list of all the service packs and C5 links to the Knowledge Base).  Here is the outline:

A. Overview
A. 1. SAP Crystal Solutions Overview
A. 2. SAP Crystal Solutions Licensing
A. 3. SAP Crystal Solutions Hardware specs, minimum requirements, etc
A. 4. What is the numbering of versions
A. 5. SAP Crystal Solutions Service Packs
A. 6. SAP Crystal Solutions Product versions
A. 7. SAP Crystal Solutions Maintenance and Support
A. 8. SAP Crystal Solutions compatibility

B. Purchase
B. 1. SAP Store purchase
What versions of SAP Crystal Reports are available through the SAP Store?
What versions of SAP Crystal Server are available through the SAP Store?
SAP CR 2020 and SAP CR 2016 available on selected 3rd party marketplaces.
B. 2. SAP Store additional questions
B. 3. SAP Store Upgrades
B. 4. SAP Store Order Fulfillment
B. 5. End User license agreements (EULA)

C. Installation
C. 1. SAP Crystal Solutions Installation
C. 2. Lost license keys and registration numbers
C. 3. SAP Crystal Solutions How-to Guides
C. 4. SAP Crystal Solutions Videos and Tutorials
C. 5. SAP Crystal Solutions Guided Answers and KBAs
C. 6. SAP Crystal Solutions Third Party Training and Consulting

D. Developers
D. 1. SAP Crystal for Developers
D. 2. Retirement of the SAP Distribution Reselling and PartnerEdge Open Ecosystem

E. Error and Issues Handling
E. 1. SAP Crystal Solutions product tips and tricks
E. 2. Vulnerabilities

F. Other topics
F. 1. SAP Crystal Solutions Third Party Apps
F. 2. Ask your own question
F. 3. Recruitment with SAP Crystal skills

Some of the links in this FAQ, like the link to the knowledge base search, require an SAP “Universal ID” to get full access.  These are free.

I found an apparently longstanding bug in Crystal Reports recently. It can be demonstrated by creating a simple report with three formulas (A, B and X).  Formula A can reference any field. Formula B contains formula A. Formula X contains formula B and also uses either the Next() or Previous() function. For a very simple example, use these three formulas:

Formula A:  {table.field}

Formula B:  {@A}

Formula X:  Next ( {@B} )

Place these 3 formulas on the details band of the report, side by side. They should all return the same values, with X values one row ahead.

To see the bug, open Formula A and add or append a literal value to the existing field.  If the field is a string you can use:

{table.field} & ‘AnyText’

If your field is a numeric or a date you can add a number to it:

{table.field} +10

When you save Formula A with this change you will immediately see Formula A values change in preview.  You would expect to see the same change in Formula B, but because of this bug, Formula B will not change.  You can make any change to Formula A that doesn’t cause a refresh/recalculate, and Formula B won’t change – at least not right away.

Now go into Formula B and make a simple change.  For example, hit [Enter] to add a blank first line and save Formula B. Now Formula B displays the change and matches Formula A. The formula updates finally, but that update didn’t happen right away.

To see it again you can go back to Formula A and take out the added value. Again Formula B won’t change until you do something else to make the report recalculate or refresh.

Somehow, using the Previous()/Next() functions in this three-formula series confuses Crystal’s internal formula dependency.  The fields always get back in sync after a refresh, or whenever the report recalculates all the formulas.  But many minor formula changes don’t force the report to recalculate so things can be off for a while.  I discovered this bug while trying to troubleshoot a very complex report.  It took hours to isolate the behavior, confirm that the formulas were correct and identify Crystal as the source of the problem.

I also went back and tested CR v10 (2004) and CR v8.5 (2001) and found the exact same behavior, so this is not a new bug.  I have never noticed it before, but it might explain some of the unexplained gremlin situations I have encountered over the years.

One solution to this is to add “WhilePrintingRecords” to either Formula A or Formula B, but that adds significant limitations to how these formulas can be used.

So I have a report with a subreport. The subreport is in it’s own section. The link to the subreport is a formula in the main report that extracts a PO number from a long text field.

The report is 13 pages long but it kept stopping at page 8 with no error message. The total page count would say 8+ and if I tried to go to page 9 it would take me back to page one. I assumed there was some page number bug, so I checked all the properties that affect paging and found nothing. When I deleted the subreport I get all 13 pages.

So I checked the ‘suppress’ property for that subreport and suddenly the problem went away. This was very strange because the subreport has a suppress condition formula which should ignore the check mark.  Even with the suppress is checked the subreport still ran at all the correct places based on the formula condition, but for some reason the suppress check mark allowed it to page correctly to the end.  I was confused.

My only clue was that it always stopped on after page 8, so I figured there had to be something unique in one record on page 9. I started eliminating records until I had it narrowed it down to one record that caused the problem. Then I started to check the formulas related to the subreport.  I found that the formula I was using to link to the subreport would generate an error when I put it on the report. That was strange because the formula was already being actively used to link to subreport. But even though the formula was already being used, it would NOT generate an error message. Once placed on the report an error would appear. That allowed me to correct the error and now the report works fine. 

So I learned that a bad formula, when it is used only as a link to a subreport, can silently stop a report without generating an error message. When that happens you have to put the formula on the report (or use it in another formula) to see the error.  That gives us one more place to check when a report starts to behave strangely.

If you are using Sage 50 (PeachTree) on Windows 11 and your Crystal Report gets the SQL error below, I think I have a fix for you.

The error says:

Failed to retrieve data from the database
Details: ADO Error Code: 0x
Source: Pervasive PSQL OLE DB Provider
Description: [LNA][Pervasive][ODBC Engine Interface]Syntax error
[Database Vendor Code: 268435456]

The error means that Crystal is generating Pervasive SQL that has bad syntax. In the case I faced the same report would work on an older system but not on the Windows 11 system. Other reports were fine in Windows 11 but not this one.

First we were able to narrow it down to a subreport. Then I found that the subreport would work with no selection criteria so we knew it was an error in the WHERE clause. We took out each rule in turn to narrow it down to the Date criteria. The failure only occurred when there was a Date rule in the selection formula.

I took the faulty SQL that Crystal generated and tried running it as a command a few different ways until I figured out the Pervasive SQL was looking for a DateTime value instead of a Date Value. I changed the selection formula to use DateTime values and the syntax error went away. Here is the syntax I used with two date parameters:

{JrnlHdr.TransactionDate} in
DateTime({?FromDate}, Time(0,0,0)) to DateTime({?ToDate} , Time(0,0,0))

If you have a date range parameter you could use:

{JrnlHdr.TransactionDate} in
DateTime(Minimum({?DateRange}), Time(0,0,0)) to
DateTime(Maximum({?DateRange}), Time(0,0,0))

I can’t say for sure this error only occurs in Windows 11 since I found the exact same error in a forum question from 2018 (before Windows 11 was released).

Update – 6/24/2023:

In the forum discussion above a Sage specialist reports that the error is related to CR 2008 SP1, and that upgrading to CR 2008 SP2 solves the problem. You can check your version by looking up the full Crystal Reports version number on the [Help > About] screen.   If your version starts with  12.1 you are on CR 2008 SP1.  If it starts with 12.2 you are on SP2.   If your version doesn’t start with 12, then you aren’t using CR 2008. 

And thanks to John Dierkhising of Bottom Line Accounting for point this out.

I just wrote about installing 64-bit drivers to get CR 2020 to work with Raiser’s Edge legacy MDB files. Here are a few other things I learned:

1) If you modify a Raiser’s Edge report using CR 2020 you are saving it connected to a 64-bit data source. Raiser’s Edge will still run the report just fine using 32-bit connections. Apparently, when RE runs the report automatically replaces any connections in the report with the connection  configured in RE. This was a happy surprise.

2) When running reports from inside other applications you can export the report to .rpt format. This saves the report with data, letting you open it in Crystal Reports and page through the saved data. When I tried this from RE the saved data would generate an error. It appears that RE adds a new formula to the report when it is run.  This formula uses a proprietary custom function that is only available inside the RE environment. This prevents the report’s saved data from opening up in Crystal.

3) You can make most changes using CR 2020 without having to connect to the data at all. You wouldn’t be able to preview the changes in CR, and you wouldn’t be able to add any new tables, but most other changes can be made without a connection.

4) You can still purchase Crystal Reports 2016 and keep using the legacy connections. CR 2016 is still for sale from SAP and simplifies things when you are using legacy data like this. SAP’s web site shows that support for CR 2016 will no longer be available in 2025, but that isn’t an issue for most users. I have many customers using ‘unsupported’ versions, some from 20+ years ago, and the lack of SAP support has never been an issue.