The Crystal Reports® Underground

I was recently tasked with trying to speed up a very complex report that was based on a Stored Procedure. We wanted to see if any of the formula logic could be moved from the report to the SQL. Since there were close to 600 formulas I was hoping to skip over anything that used specific parameters that won’t easily convert. The challenge was that a formula might not mention these parameters, but might refer to other formulas that do.

To quickly rule out these formulas I needed a tool that could show me all the ‘ancestors’ of each formula and flag when these ancestors used one of the target parameters. No such tool existed, so I decided to create one using my limited programming experience and some significant AI assistance.

The source data comes from Crystal’s “Report Definition” file. This export option creates a text file that includes most of the settings in your report, including a list of all the formulas and their contents.

I used AI to create a program that would parse all the formulas in this text file and generate a dynamic tree showing them in alphabetical order. Each formula that contained other formulas would show an expandable node that would list all the formulas that it used (it’s ‘ancestors’). These formulas would in turn have nodes to show the formulas that they used, and so on. Some of the formulas in this report went down 12 or more levels.

I then asked the AI to read a separate (optional) text file containing the names of the parameters I was trying to skip. The program would highlight any formula that contained any text string contained in this file, or contained in one of it’s ancestors. Now I could go down the complete list of formulas and quickly skip the highlighted ones. knowing that these would not easily convert to SQL.

Once I had this working, I decided to add the ability to flip a switch and go in the opposite direction. This would make the nodes expand to show the formulas that use this formula (descendants) rather than the ancestors. The app met my needs for this project. It will also help me to get an overview of new reports I am asked to fix.

If anyone else has a use for this or is interested in playing around with this, let me know.

And, if you have an odd requirement where a simple application might make your life easier, let me know. I might be able to help you create something useful.

I found a strange bug in Crystal Reports today. It has to do with the functions Previous() and Next(). These functions normally return the value from either the record just BEFORE the current record, or the record just AFTER the current record.

To see the bug you can download my example report. It is a simple report that lists 31 Order IDs, sorted in ascending order, for one customer group. In the group header and group footer it displays the first and last order IDs.  It also has two formula fields that use Previous() and Next() to display the second order and the next to last order.  Everything works correct as long as the details are shown.

But, if you hide the details to create a summary version of the report, the two formulas show different values. The Next() formula now returns the value of the last order ID while the Previous() formula returns the first order ID.  There is no reason why these formulas should change values after hiding the details, so this is a bug.

Also, if you double-click the customer name to drill down, the formulas now show their original expected values in the drill-down window.

If you use suppress instead of hide the behavior is the same.  However, if suppress using a condition formula instead of the check mark, then the formulas work as expected in both detail and summary layouts.  I tested this in in CR v8.5 (2001) and CR 2016 and got the same results in both, so I assume this is universal, although I have not tested this in CR 2020.

The only solution I have found to correctly display these formulas in headers and footers is to avoid the normal hide/suppress check marks and instead use a suppress condition formula.  Any formula condition works, so here are two simple examples:

If you want the report to allow drill-down to the details (like using ‘Hide’) then use the following as the suppress formula:

DrilldownGroupLevel = 0

If you don’t need the drill-down behavior then you can simply use:

True

Both of these get rid of details while allowing the Previous() and Next() functions to work normally in the summary report.

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.