I’ve been battling some unusual web traffic on my site. I get thousands of hits every day from random IP addresses and all of them look something like this:

GET /newsletter/consulting/otherlinks/public/support/support/default.html

The string is made up of actual folder names on my site, but they are combined in random order and a random page is on the end. I am told this is penetration testing by a hacker bot.  Great. 

While working on this problem I wanted a way to count the number of slashes in each request. Crystal doesn’t have a direct function for doing that, but after giving it some thought I found a relatively simple solution. I could:

• calculate the length of the string,
• replace all the slashes with an empty string (“”)
• recalculate the length of the shorter string.
• Subtract one length from the other to get the difference

That can even be used to count occurrences of a multiple character string. You would just have to divide the character difference by the number of characters in the target string. So I turned this into a generic formula (see below). You can plug in your field name and target string in the first two lines and it does the rest:

Local stringVar Field := {Table.Field};
Local stringVar Target := '/';Local numberVar x := Length(Field);
Local numberVar y := length (Replace (Field, Target, ''));
Local NumberVar z := length (Target); 
(x-y) / z

If you need to reinstall Crystal Reports, or if want the latest service pack of your version of Crystal, you might find this link helpful. I just tracked down the page where SAP has download links for all the different versions and service packs going back to CR 2008(v12).  This page currently doesn’t require any credentials, but pages like this one tend to move around and change.  Let me know if the link fails for you.

If you need files for Crystal Reports XI you can check this site and see if what you need is there.  If you are using something even older let me know and I will see if I can find a copy of the file you need.

You still have to have a license key that matches the version you are installing (unless you are repairing an existing install).  If you don’t know your license key you can get it from the registry of the PC that has Crystal installed.  Just make sure it is the same version.

If you are installing CR 2016 and you get error something like this:

Fatal Error: At least one port in the range 4520-4539 must be opened….

Read this article about how to get around it.  You shouldn’t get this error with service pack 9 of CR 2016.

Last, if you are doing repeated installs and are tired of having to repeatedly unpack the initial files, you can check this article for ways to get around this.

I don’t maintain a resume anymore, but I am sure some of you do. If your resume mentions Crystal Reports, you might find this post by Dallas Marks useful. It is short, with just a few tips and common sense suggestions. It comes from someone not directly involved in Crystal Reports development, so I think it gives a different perspective.

My favorite part of the post was his description of how Crystal Reports is still relevant in the age of visualization (the last paragraph). Dallas is an analytics and cloud architect and co-author of: SAP BusinessObjects Web Intelligence: The Comprehensive Guide (SAP Press).

I learned something new about Crystal Reports today. It has to do with Group Selection, which you may not be familiar with. Group Selection allows you to filter a report based on subtotals. For example: “only show me customer groups with a subtotal of more than $5000”.  There is a separate Select Expert and a separate Selection Formula just for Group Selection, which mirrors Record Selection.

The tricky part about Group Selection is that while it shortens the report, it normally doesn’t reduce your grand totals. This can confuse users. So if you have a grand total that says you have 50 customers, and you use a group selection filter to eliminate half of the customer groups, your grand total will not go down. It will still say you have 50 customers even if only 25 remain in the report.  The normal solution is to replace the summary field grand total with a running grand total.  Running totals reflect the group selection criteria while regular summary fields do not.

What I learned today is that when your group selection eliminates ALL the groups in the report, then your grand totals will all be blank or zero. I was expecting the Group Selection to eliminate the groups, but for the grand totals to remain the same.   

You can read more about Group Selection and Running Totals in my Expert’s Guide to Totals, which is now a free download.

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.