The Crystal Reports® Underground

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

Update 4/25/2026:

After posting the above I heard from my long time colleague, Ido Millet.  He shared an even simpler way to accomplish this:

Count(Split({Table.Field}, "/")) - 1

As with the original formula, your target string can be any length.

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.

A customer gave me a challenge related to a [size] field. It was a string that stored values in fraction format like “2 1/8”. He wanted to do calculations with the sizes so he needed them converted to numeric values with the fraction converted to a decimal value. It is the opposite of another formula that I wrote to convert decimals into fractions.

The formula works.  It assumes that the there is a space between the (optional) integer and the fraction.  It also assumes that there are only numbers, spaces and a slash in the field, up to the end of the fraction. What comes after the fraction doesn’t matter.  The only change you need to make is to put replace the field name on the first line with your database or formula field:

Local stringVar z := trim({ItemMaster.Width});
Local NumberVar x := Instr(z, '/');
Local NumberVar y := Instr(z, ' ');
Local NumberVar NumStart := x - 2;
Local NumberVar NumEnd := x - 1;
Local NumberVar DenStart := x +1;
Local NumberVar DenEnd := x +2;
If DenEnd > Length(z) then DenEnd := Length(Z) ;
If NumStart < 1 then NumStart := 1;
Local Numbervar Num := if '/' in z then Val(z [NumStart to NumEnd]);
Local Numbervar Den := if '/' in z then Val(z [DenStart to DenEnd]);
Local Numbervar FractionVal := if Den = 0 then 0 else Num/Den;
Local NumberVar IntVal :=
if not ('/' in z) then val(z) else
if ' ' in z then val(z [1 to y]) else val(z);
IntVal + FractionVal

Let me know if you find any issues.

This post is an update to an older post where I suggested a method for getting cross-tabs to reflect group selection criteria.  I just discovered a flaw in that approach, and so here is a better way.  You can download a sample report that illustrates the issue and a screen shot of the report output below:

This report has a group selection formula that limits the results to customers with a subtotal of over 50K. There are three customers that meet this criteria out of the 77 customers in the report data. To get an accurate grand total I added a running total (in blue) to the report footer.

A normal cross-tab added to this report would show all 77 of the customers that met the RECORD selection criteria. But we want just the three customers that meet the GROUP selection criteria. So I added two modified cross-tabs to the report, one using my original solution and one using the new solution. You will notice that the grand total of the Original solution (in Red) doesn’t match the the running total in blue. It is off by the value of a 4th customer who is being included in the cross-tab incorrectly.

This customer doesn’t meet the criteria and is not shown in the final report output. But if you were looking at the report in preview mode you would see that this customer is the first customer listed in the group tree. If we then changed the group to Descending Order, then a different customer would be first in the tree, and that customer would show up (incorrectly) in the cross-tab.   I have no idea why the first group is treated differently.

But if you look at the New Approach, this extra customer is shown with a zero amount and the total of the cross-tab (Green) matches the running total in blue. We could eliminate this extra row completely by using the “Suppress Blank Rows” option in the cross-tab properties.

So what is the new approach?  We still create a formula for the Amount field, but we incorporate the Group Selection criteria into the formula, something like this:

if Sum ({Orders.Order Amount}, {Customer.Customer Name}) > 50000
then {Orders.Order Amount}
else 0

One note on using these types of formulas in the cross-tab.  You can add these formulas while in the cross-tab expert, but if you go to change any cross-tab properties, the formula will be replaced.  To get around this you can temporarily comment out the formula and just use the raw database field.  Then you can make any cross-tab changes.  When you are done you can put the formula back the way it was.

I just had a customer ask me to write a formula for determining the shift from a datetime value. Their shifts run:

07:00 to 14:59 (1st)
15:00 to 22:59 (2nd)
23:00 to 06:59 (3rd)

This is pretty common, including a shift that crosses midnight. The shift formula would look like this:

if Hour({tbl.DateTime}) < 7 then '3rd' else
if Hour({tbl.DateTime}) < 15 then '1st' else
if Hour({tbl.DateTime}) < 23 then '2nd' else '3rd'

But invariably the next request is to group the records by date and then by shift. But the first hour of the third shift has a different date so it will be grouped with the 7 hours the night before, rather than the 7 hours that follow. Of course that makes no sense, since we want the 3rd shift to be 8 consecutive hours, despite the fact that they are (technically) on different days.

You resolve this by writing another formula called “Shift Date”, and there are two ways to write it.  You choose, depending on how you view the date of the third shift.  Lets say our third shift runs from Monday night to Tuesday morning. I think most users would consider the entire third shift an extension of Monday, since that is when the shift started. Also it is the last shift of the series that starts on Monday. But I have seen cases where the users consider the third shift part of Tuesday since most of it is on Tuesday. Here are both formulas

If you are in the first category your formula would look like this to shift 7 hours back to the previous date:

if Hour({tbl.DateTime}) < 7
then Date({tbl.DateTime})-1
else Date({tbl.DateTime})

If you are in the second category, your formula would look like this to move the last hour to the next date:

if Hour({tbl.DateTime}) = 23
then Date({tbl.DateTime}) + 1
else Date({tbl.DateTime})

Crystal Reports 2025 was released for purchase recently, but wasn’t available for a free trial download at that time.  As of today, Crystal Reports 2025 is available as a free trial.    If you are looking for what is different in Crystal Reports 2025 vs 2020, you can check out the “What’s New” page.

I haven’t touched my comparison of alternative reporting tools in a long time. This year I am adding two new reporting tools to the comparison: Clear Reports by iNet and CxReports by Codaxy. I have a separate article describing Clear Reports, which is very similar to Crystal.

CxReports takes a different approach. They use a page based layout approach (similar to SSRS) as opposed to the banded approach used by Crystal. The basis for each reports is a SQL query (or a script that combines SQL queries).  Calculated expressions are also done in the SQL query. Beyond creating the SQL, the GUI is designed for end users rather than developers. There are reusable themes and templates that allow consistent headers and footers across reports. The settings for each report are stored in an open format (JSON) file.

CxReports is designed as a SAAS tool, but it can be purchased and run locally on Windows or Linux, using Docker.  Pricing is tied to volume-based tiers, with more expensive tiers allowing for more users, more reports and more generated PDFs. Scheduling and automatic Email report delivery are included at all levels, subject to volume limits at each tier.

For a more detailed feature comparison you can download the feature matrix in the comparison article.

I was recently asked to do a project converting some Crystal Reports to iNet Clear Reports.  The customer assured me that the Clear Reports designer was very similar to Crystal Reports, and he was correct.  During the project I had no trouble finding my way around the Clear Reports menu.  The formula language is also virtually the same allowing me to copy and paste formulas directly from Crystal to Clear.  Below are the most important differences I found so far. I have also added Clear Reports to my comparison of alternative reporting tools which provides a more detailed feature comparison.

Note: The comments below are based on version 21, which this customer uses.  The latest version is 24 and may have improvements over 21.

Major disappointments:

• You can’t make modifications in preview. You must make all changes on the design tab. Every time you go back to the design tab you will have to refresh to preview.
• You can’t save the report with data. This is one of my favorite Crystal features, allowing me to work off line.
• All summaries are created as named objects, similar to Crystal running totals. When you create them you can determine if they behave as running totals or normal summaries.
• In Crystal you could copy a row of subtotals and past them into the report footer to make the same row of grand totals, because summary fields in Crystal are relative to the section containing them. You can’t do that with the named summaries in iNet.
• None of the third party software in Crystal Reports’ ecosystem will work with iNet.

Minor annoyances:

• You can’t drag multiple fields from the field explorer to the report design area. You must drag them out one at a time.
• When you drag an object from the field explorer there is no visible object outline to guide your placement. The object outline IS there when you move an object, but not when you first bring it out from the field explorer.
• The record count does not display in the status bar of the preview screen.
• iNet has a format painter (paintbrush) toolbar button, but you can’t make it “sticky” so a format can be applied to multiple fields by clicking on them in turn. You must click the paint brush again every time you want to apply it.
• The ruler divides each inch into 5 parts rather than 8 (a metric mindset?).

Other differences:

• iNet is licensed by server and the designer is free.
• iNet will run on Linux as well as Windows.
• iNet shows a real-time list of ‘problems’ (formula errors, unused fields, etc)
• iNet shows the data-type of all fields in the field explorer, not just database fields