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.)

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.

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 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 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.

This week I had to do something new in SQL.  I needed to take a list of multiple route records for each driver and append the routes into single string for that driver.  This is relatively simple in a Crystal report, but not so simple in a SQL Query.  After I realized how complex this would be in a normal SQL query I searched to see if there was a simpler way.  What I found was that several databases have a function specifically designed for this task.

In SQL Server and Postgre SQL it is called String_Agg().
In Oracle and DB2 it is called ListAgg().
In MySQL it is called Group_Concat().

Even though SQL is considered a “standard” language, some function names and syntax will vary from one database to another. That is why I created a “cheat sheet” a few years ago that lists the most common SQL functions (with their correct syntax) for the 8 SQL flavors I encounter the most:

• • • SQL Server
    • MySQL
    • Oracle
    • MS Access
    • Pervasive
    • Progress
    • PostgeSQL
    • DB2

Today I added these “aggregate” functions to the cheat sheet along with a few other updates.  The cheat sheet is now also available on the library page of my web site.

My customers often use large, high-resolution monitors.  Sometimes the icons and menu options are microscopic – even to the local user. It is even worse for me since I am seeing their screen through GoToMeeting which reduces the screen even further.

But recently, one of my colleagues pointed me to a video that might help. It shows how to change a compatibility setting specific to high-resolution monitors.

If you are seeing microscopic icons on your Crystal toolbar, check out the video and see if it helps. And thanks to Gordon Portanier with the Crystalize consulting group in Canada for sharing this tip.  And also to Mike Pieczynski of OnPoint Consulting for sharing the video.

In the past few weeks I have been contacted by two different users with xBase data (dBASE, FoxPro, etc). Both were trying to read the data using CR 2020 and not finding a way to connect. One had to upgrade to 64-bit because some of the xBase.dbf files had gone past 2GB.  As I now know, a 32-bit process can only use a maximum of 2 GB of memory. I didn’t know this was a general limit, but I did know that when one of my MS Access databases gets to 2GB I have to purge and pack it before my reports will run.  The other user upgraded without knowing that his connectivity would be affected. They might be able to revert back to CR 2016.

I have still not upgraded to CR 2020 because I use DAO connections and have little motivation to change them. However, if I get a slow week, I plan to setup a test environment to experiment with CR 2020. Then I can see if there are any workarounds for the xBase issues.

If I can’t find a driver that can connect directly to xBase there is one other thing I may try. I have read that MS Access 2016 can connect to dBASE files. It may be possible that MS Access 2016 can be used as a bridge between Crystal and dBASE files.  If so that might work for other xBase flavors like FoxPro.  I will post what I find.

If anyone else has any ideas, I would love to hear from you.

If you put a picture object on a report, that object will start out in full size. Usually I find this is much larger than I need and I have to reduce the size of the object. When you resize an image object using the sizing handles (sides and corners) you can easily change the ratio of the height to the width, which might distort the image. This ratio is called the “aspect ratio” and typically you want to keep the same ratio as in the original image.

One way to change the image size and keep the aspect ratio is to right click on the image, select “Format Graphic” and go to the “Picture” tab. There you can use the “Scaling” percentage to enlarge or reduce the height and width.  If you use the same number in both boxes the image will get bigger or smaller but will keep the aspect ratio of the original.

Today one of my colleagues showed me something he learned from one of his customers. Maybe some of you know this already but it was new to me.  If you select an image object and then hold down your “Shift” key you can change the size of the image and the “Shift” key will lock the aspect ratio.  Using the sizing handles you can make the image bigger or smaller, but the height and width will stay in proportion to each other.

One advantage of this over entering the numbers as described above, is that you can visually choose the size you want. When entering the numbers you have to exit to see the result.  This often means some trial and error to get the image to the size you want.  In testing this tonight I found that after using the “Shift” method the “Scaling” numbers in the “Picture” tab were sometimes slightly off – usually less than 1% different.  If you want them exactly the same you might still use the “Shift” method to get the size to look right visually, then go into the “Picture” tab and adjust the “Scaling” numbers so that they are exactly the same.

I had a customer this week who updated a report to read Oracle instead of SQL Server. The report was based on a command and the SQL was virtually identical. But for some reason the Oracle version would take a full 5 minutes to refresh while the SQL Server version only took a few seconds.

Once the report was run in Oracle the first time it could be refreshed using different parameters and it would only take a few seconds. I had the customer check for indexes but those had been set up to match SQL Server. I wondered if it was a data cache, but that didn’t explain why setting new parameters would be fast.

The only other thing that I could think of that was unique to the first refresh was a setting in Crystal that does a “Verify Database” on the first refresh. This usually takes a few seconds and isn’t noticeable, but as a test I turned that feature off. The first refresh was now as fast as SQL Server. Apparently, something in their environment caused the “Verify” process to take 5 minutes to complete.

If you run into something similar, go into File > Report Options and look for the check mark that says “Verify on First Refresh”.  Unless your environment is changing frequently, this doesn’t need to be checked, although in most environments you won’t notice a difference.

If you aren’t familiar with the “Verify” feature you can invoke it at any time by using the option in the database menu. This forces Crystal to poll the database for a complete list of fields for every table used by the report, including the data types.  Crystal stores this info in the report, which gives you a full field list even when you aren’t connected to the database.  Whenever fields are added, removed or their data type is changed this list needs to be updated . That is what it means to “Verify Database”.