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

I recently helped a new user who wanted to purchase Crystal Reports, but wasn’t sure what product(s) to buy. He had read about a server component, a ‘developer’ component and a viewer, but wasn’t sure what the names were or which he needed. The SAP website didn’t clear things up for him.

“I saw an architecture diagram that broke it into tiers, but that did not help.”

This isn’t the first time I have had this type of question, and I thought my response might help other users.

There are three levels in the Crystal product line (four if you count the Business Objects Enterprise level) but most users only need one, SAP Crystal Reports. That is all I use. Here is the main product page. This is often called the Report Designer or the Developer, but the official name is SAP Crystal Reports. It is the only product that lets you create a Crystal Report.

You can currently buy 2 different editions:

2016 (32-bit)
2020 (64-bit)

If you get a free trial from the product page I think you will get 2020, but if you buy it you can choose either edition. I currently use 2016 because the 64-bit product is relatively new. I also use lots of third party tools that aren’t ready to jump to 64-bit and I don’t want to leave them behind.

The other products are Crystal Server and SAP Crystal Reports Viewer. Crystal server is a web portal that lets people run the reports from their browser. But, you still have to create them in Crystal Reports and then publish them to the portal.  This product can get quite expensive.

The Crystal Reports Viewer is free but is pretty much useless in my opinion. It lets you open reports, but only those that are saved with data. You can’t refresh reports unless you are logged into CR Server.  There are plenty of inexpensive third-party viewers out there so not many of my customers bother with the SAP Viewer. Here is a list of the ones I review each year.

If you have any questions about the Crystal Reports product line, let me know and I will try to help.

Several of my customers read Excel spreadsheets, Microsoft Access MDB files and even text files using DAO connections. This are simple connections made by pointing directly to the data file. Unfortunately, these direct connections are no longer supported in Crystal Reports 2020. I was hoping that might be a temporary situation, but this link on the SAP website provides confirmation:

Crystal Reports 2020 is a 64bit application, and therefore it is no longer possible to connect to Excel or Access using DAO, since there is no 64bit version of this Microsoft Technology. This is why the option “Access/Excel (DAO)” is not available in Crystal Reports 2020.

You can still connect to these data sources using ODBC or OLEDB, but you will need the new 64-bit Microsoft driver. OLEDB actually works in a similar way to DAO, but you will need to do a “Set Datasource Location” in each report to convert it from DAO to OLEDB. I posted instructions in February for using OLEDB to connect to the Xtreme Sample Database (mdb) and the steps would be similar for other types of DAO connections.

If you run both 32-bit and 64-bit versions of CR you might want to switch to ODBC connections (DSNs). With ODBC you can create a 64-bit DSN on one PC and a 32-bit DSN on another PC using the same DSN name. That way you can maintain one rpt file and it can run in both the 32-bit and 64-bit Crystal environments.