The Crystal Reports® Underground

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

Crystal Reports 2025 is now available for purchase from SAP. The cost is the same: $495 for a full license and $295 for an upgrade. Normally, SAP offers a trial download of the products they are selling, but there is no trial download for CR 2025. You can still download a trial of CR 2020 or CR 2016.

I won’t be upgrading to 2025 in the near future. I have worked with CR 2020 with several customers but I have not installed it in my own environment. I still prefer using the 32-bit CR 2016, which is also still being sold. The main reason is that I am not yet ready to give up the legacy connection options available on the 32-bit platform.

If you are interested in what is new you can look at this SAP article.  Most of the new features are for users at the enterprise level.

I often work with large and complex reports. Sometimes making a minor change can have unexpected consequences. I like to be able to see that the only things that changed are the things I intended to change. My simple approach to this is described in this article.

R-tag has created a more sophisticated version of this process with their Unit Testing module.  It allows you to create a set of parameters and a validated output file (image, a PDF, a spreadsheet, etc.) for those parameters.  The parameter combinations are called theories, and the validated output file are called golden files.

After you make changes to the report, the data or the environment,  you can run a unit test for that theory.  The software will generate a new output file using the theory parameters, and will automatically compare that file with the golden file, listing all of the differences.  You can create multiple theories for each report with different different parameter combinations. You can then run the unit tests manually (e.g. right after a database update) or on a schedule.

My simple approach will only find values that change, but the unit testing feature can compare the output as two images, which will pick up formatting differences as well as data changes.

R-tag has a demonstration video here.

There is a chance that Crystal Reports 2025 will be released next week on 3/12/2025.  I just viewed an SAP community page that mentions Crystal Reports 2025. The only problem is that page points to an article that focuses on SAP BI 2025 and doesn’t specifically mention Crystal Reports 2025 anywhere. I guess we will know more next week. SAP has always been cagey about new versions of Crystal Reports.

You should also check out the rest of this “new” community page. I say “new” because it is new to me.  It looks like it has been around for about a year without me noticing it. The good news is that most of it doesn’t require you to have an SAP account ID.

This page lets you download the install media for every version of Crystal reports for the past 20 years (including Crystal Reports XI from 2005). Yes, you still have to have a license key to complete the installs, but this is great for when computers crash and you lose your install files.

This page also has links to trial versions of Crystal Reports, SAP generated sample reports and lots of how-to guides (formulas, charts, etc).

And thanks to Gordon Portanier of Crystalize in Canada for sharing the link.