The Crystal Reports® Underground

I have written lots of articles about the rotation issue with Crystal Reports and Zebra printers. Apparently it can still crop up since I just helped a customer with this issue.  They installed the drivers from Seagull Scientific which usually solves the problem.  But in this case, once they had the drivers installed, the problem didn’t go away.  But after they re-calibrated the printer to the label media, the issue went away for them.  I am sharing the steps just in case this comes up for anyone else. These are the steps for the Zebra GK420d desktop printer:

1) Holding the feed button down
2) Wait until it blinks once, then blinks twice in quick succession.
3) Immediately release the button
4) It will automatically print out a few labels or so to figure out the dimensions.

(and thanks to Samuel Lindemulder of Kentwood Powder Coat for sharing these steps.)

When Crystal Reports 2011 came out, SAP also introduced Crystal Reports for Enterprise (CR4E).  The two products would be parallel but:

“SAP Crystal Reports for Enterprise is the foundation for all future releases of Crystal Reports.”

At that time most users were simply happy that the stand-alone version wasn’t being abandoned.

But CR4E never caught up with Crystal Reports (in features or usage).  According to an article I just discovered, CR4E is finally being allowed to sunset.  They are now providing instructions on how to migrate back to Crystal Reports from CR4E.

One of my customers uses Crystal Reports 2016 and wanted to buy more licenses. I recommended that they purchase licenses of Crystal Reports 2016 to keep all the environments the same. Crystal Reports 2016 is the last version that is 32-bit ODBC drivers. Crystal 2020 uses 64 bit ODBC drivers.

The customer then sent me a link showing that CR 2016 support is reaching “End of Life”.

The link says that “mainstream maintenance” ended in 2022 and “Priority One support” ends this December. They asked if this was cause for concern and I answered that it wasn’t. Few of my customers have ever needed SAP support. Many of them use even older ‘unsupported’ versions of Crystal. A few still use v8.5 from 2001, so I still have that installed on my system. The lack of SAP support has never been an issue. I haven’t upgraded to CR 2020 yet because I don’t want to recreate all my local connectivity. Also, it is interesting to note that SAP still sells CR 2016.

But, if you do need to upgrade to CR 2020 it isn’t usually a big deal. It causes the most work when users are connecting using Crystal’s DAO drivers to connect to spreadsheets and text files. There are no DAO drivers in CR 2020 so you have to connect using 64 bit ODBC drivers.

If you need some help upgrading to CR 2020 just let me know.

I had a strange one today – working on someone else’s report. The report was grouped by a formula that combined 2 fields, ItemCode and ItemSize. The formula looked like this:

{table.ItemCode} & ' - ' & {table.ItemSize}

I group on formulas like this often, so I didn’t see anything unusual. But the customer reported that two different sizes were being combined into one group. To troubleshoot this I put the ItemCode, the ItemSize and the formula all on the details band and looked at the values in the problem group. It was clear that two sizes (1.48 and 1.481) were being combined in a group that showed them both as 1.48.

I checked the data type of the ItemSize field and found that it was numeric which pointed to the problem. If you concatenate numeric fields and don’t specify the format using the ToText() function, Crystal will use your default windows format. This is typically to round to 2 decimals. So the two values above were rounded to the same value.

I changed the formula to be:

{table.ItemCode} & ' - ' & Totext({table.ItemSize} , 5 , '' )

This forced it to include 5 decimal places, and the empty string in the second argument eliminates commas in any numbers over 999. This version of the formula separated this item into two separate groups.

This is easy to miss so I thought others might appreciate the tip.

Adam Butt at APB reports just added a new feature to Crystal Dispatch, his ‘simple-to-use’ Crystal Reports viewer. Crystal Dispatch lets you create a shortcut for any report. Clicking that shortcut runs the report without needing to have Crystal Reports installed. After the first time you run the report it can encrypt and store your credentials.

The new features allows you to automatically export a grouped report to multiple PDFs – one per group. Each file is automatically named based on the Group Name field in the report.  This gives you a simple way to control the exported file names. The bursting happens very quickly.

If you want to try Crystal Dispatch, or any of the other viewer programs available for Crystal Reports, you can follow this link for Crystal Dispatch. To learn about all Crystal Reports viewers in more detail you can read my Viewers Comparison page.

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.