One of my customers has many complex reports that we have created over the years. In the past few weeks one of the reports started behaving oddly. Opening the report with only 55 records of saved data caused Crystal to freeze for 8 minutes (not responding). Making a simple change in preview, like moving or sizing a field, would cause a similar freeze. The report wasn’t accessing the database so I wasn’t sure what was causing the delay. The report uses several arrays and some looping logic so a glitch in a formula could theoretically cause extra processing time.

As a test, we opened an earlier version of the same report. We found that this version behaved normally.  I took the two versions, exported them both to “Report Definition” format, and then did a text comparison of the two files.  This showed me all the formula changes we made to get to the current version. I added all those same changes to the old report, one at a time, to see if I could identify a specific change that cause the odd behavior.  When I was done the old version was virtually identical to the current version and was still behaving normally.

To confirm they were the same, I also compared the output of the two reports.  This time I exported the report output of both reports to text files. I did another text compare and found the output was also identical. This confirmed that the issue was the result of a glitch in the report file itself, some internal corruption that didn’t prevent the report from running.  Not sure I have seen that before.  Since we now had a working version we just scrapped the faulty report and went forward with report that worked. The main lesson in this, other than sharing the process, is the value of having historical versions of reports. The prior version gave us a window into when the glitch occurred and allowed us to recover without starting from scratch.

Even on simple modifications, I usually rename a report before making changes.  When problems show up I can run the original to determine if the issue was already in the original or was the result of the changes.  For a single update I might just add my initials to the modified report. For long development projects I like to add a sequence number to the end of the file name (01,02,etc.).  Some users prefer to add dates in the file name but I prefer shorter file names that sort cleanly.  The modification date is always visible in the folder.

I found an apparently longstanding bug in Crystal Reports recently. It can be demonstrated by creating a simple report with three formulas (A, B and X).  Formula A can reference any field. Formula B contains formula A. Formula X contains formula B and also uses either the Next() or Previous() function. For a very simple example, use these three formulas:

Formula A:  {table.field}

Formula B:  {@A}

Formula X:  Next ( {@B} )

Place these 3 formulas on the details band of the report, side by side. They should all return the same values, with X values one row ahead.

To see the bug, open Formula A and add or append a literal value to the existing field.  If the field is a string you can use:

{table.field} & ‘AnyText’

If your field is a numeric or a date you can add a number to it:

{table.field} +10

When you save Formula A with this change you will immediately see Formula A values change in preview.  You would expect to see the same change in Formula B, but because of this bug, Formula B will not change.  You can make any change to Formula A that doesn’t cause a refresh/recalculate, and Formula B won’t change – at least not right away.

Now go into Formula B and make a simple change.  For example, hit [Enter] to add a blank first line and save Formula B. Now Formula B displays the change and matches Formula A. The formula updates finally, but that update didn’t happen right away.

To see it again you can go back to Formula A and take out the added value. Again Formula B won’t change until you do something else to make the report recalculate or refresh.

Somehow, using the Previous()/Next() functions in this three-formula series confuses Crystal’s internal formula dependency.  The fields always get back in sync after a refresh, or whenever the report recalculates all the formulas.  But many minor formula changes don’t force the report to recalculate so things can be off for a while.  I discovered this bug while trying to troubleshoot a very complex report.  It took hours to isolate the behavior, confirm that the formulas were correct and identify Crystal as the source of the problem.

I also went back and tested CR v10 (2004) and CR v8.5 (2001) and found the exact same behavior, so this is not a new bug.  I have never noticed it before, but it might explain some of the unexplained gremlin situations I have encountered over the years.

One solution to this is to add “WhilePrintingRecords” to either Formula A or Formula B, but that adds significant limitations to how these formulas can be used.

So I have a report with a subreport. The subreport is in it’s own section. The link to the subreport is a formula in the main report that extracts a PO number from a long text field.

The report is 13 pages long but it kept stopping at page 8 with no error message. The total page count would say 8+ and if I tried to go to page 9 it would take me back to page one. I assumed there was some page number bug, so I checked all the properties that affect paging and found nothing. When I deleted the subreport I get all 13 pages.

So I checked the ‘suppress’ property for that subreport and suddenly the problem went away. This was very strange because the subreport has a suppress condition formula which should ignore the check mark.  Even with the suppress is checked the subreport still ran at all the correct places based on the formula condition, but for some reason the suppress check mark allowed it to page correctly to the end.  I was confused.

My only clue was that it always stopped on after page 8, so I figured there had to be something unique in one record on page 9. I started eliminating records until I had it narrowed it down to one record that caused the problem. Then I started to check the formulas related to the subreport.  I found that the formula I was using to link to the subreport would generate an error when I put it on the report. That was strange because the formula was already being actively used to link to subreport. But even though the formula was already being used, it would NOT generate an error message. Once placed on the report an error would appear. That allowed me to correct the error and now the report works fine. 

So I learned that a bad formula, when it is used only as a link to a subreport, can silently stop a report without generating an error message. When that happens you have to put the formula on the report (or use it in another formula) to see the error.  That gives us one more place to check when a report starts to behave strangely.

If you are using Sage 50 (PeachTree) on Windows 11 and your Crystal Report gets the SQL error below, I think I have a fix for you.

The error says:

Failed to retrieve data from the database
Details: ADO Error Code: 0x
Source: Pervasive PSQL OLE DB Provider
Description: [LNA][Pervasive][ODBC Engine Interface]Syntax error
[Database Vendor Code: 268435456]

The error means that Crystal is generating Pervasive SQL that has bad syntax. In the case I faced the same report would work on an older system but not on the Windows 11 system. Other reports were fine in Windows 11 but not this one.

First we were able to narrow it down to a subreport. Then I found that the subreport would work with no selection criteria so we knew it was an error in the WHERE clause. We took out each rule in turn to narrow it down to the Date criteria. The failure only occurred when there was a Date rule in the selection formula.

I took the faulty SQL that Crystal generated and tried running it as a command a few different ways until I figured out the Pervasive SQL was looking for a DateTime value instead of a Date Value. I changed the selection formula to use DateTime values and the syntax error went away. Here is the syntax I used with two date parameters:

{JrnlHdr.TransactionDate} in
DateTime({?FromDate}, Time(0,0,0)) to DateTime({?ToDate} , Time(0,0,0))

If you have a date range parameter you could use:

{JrnlHdr.TransactionDate} in
DateTime(Minimum({?DateRange}), Time(0,0,0)) to
DateTime(Maximum({?DateRange}), Time(0,0,0))

I can’t say for sure this error only occurs in Windows 11 since I found the exact same error in a forum question from 2018 (before Windows 11 was released).

Update – 6/24/2023:

In the forum discussion above a Sage specialist reports that the error is related to CR 2008 SP1, and that upgrading to CR 2008 SP2 solves the problem. You can check your version by looking up the full Crystal Reports version number on the [Help > About] screen.   If your version starts with  12.1 you are on CR 2008 SP1.  If it starts with 12.2 you are on SP2.   If your version doesn’t start with 12, then you aren’t using CR 2008. 

And thanks to John Dierkhising of Bottom Line Accounting for point this out.

Like many users, I have not yet upgraded to CR 2020. I connect to lots of legacy data using DAO connections (e.g. Raiser’s Edge export data). To do this using CR 2020 involves installing new 64-bit connectors and updating each report. I’ve written up instructions on installing the new drivers but I haven’t applied the steps here since I am still on CR 2016.

This week I heard from a user who has to read legacy MDB files from Raiser’s Edge, using CR 2020. They followed my instructions to download the new driver, but got an error message when they tried to install it. The error said:

“You cannot install the 32-bit version of Microsoft Access Database Engine 2016 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2016, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2016:”

The funny thing is that they also tried to install the 32-bit driver and got the opposite error. So even though I don’t have CR 2020 I downloaded the 64-bit Access driver and tried to install it. I got the same error.

After a bit of research I found a way to get the driver to install without having to uninstall anything, by running the install from the command line using the ‘quiet’ switch. Here are the steps I used:

1) Download the file to my downloads directory
2) open a command line window
3) change the directory in the command line window to \downloads
4) Run the command with the “quiet” switch. It will look something like this:

C:\Users\ken\Downloads>AccessDatabaseEngine_X64.exe /quiet

You can also read the discussion where I found this method here:

https://superuser.com/questions/1557350/

After testing it here I did the same thing for Raiser’s Edge user and got them working as well. 

I resolved another minor mystery recently after a question from a customer. It has to do with the behavior of variables whenever you open a new tab by doing a drill-down on a group. The same thing can happen when you double click on a subreport in preview mode to open the preview of that subreport.

What I had seen was that some variables would carry to the new tab others would reset to zero/blank. So when a customer explained that this was causing a problem in their report I did some digging and experimenting. I discovered that this problem only occurs with shared variables, which pass to and from subreports. Shared variables will not carry from the preview into the new tab, while default (global) variables carry just fine.

I did some more digging to find a workaround and only found a partial one. I say partial because it will work for group drill-downs but not subreport drill-downs.  If you drill down on a group, typically you will see the Group Header, the Group Footer and the details for that group. The workaround is to make a second copy of the subreport that generates the shared variable, and put it into the group header of that group. To make sure that subreport doesn’t run repeatedly you can either hide the group header, or suppress the subreport with a condition like this one:

DrillDownGroupLevel = 0

Either of these will keep the extra subreport from firing until you do the drill-down. Then one instance of the subreport will run inside the drill-down and the shared variable will carry to the details and group footer inside that drill-down tab.

The downsides are

1) The subreport will have to run again if you do another a drill-down.

2) It won’t work for a subreport because you can’t put one subreport inside another.

If you have any questions about using variables, call to schedule a session.

Added 4/2/2023 – Another solution, if you are willing to use a third party dll, is to use the “Key-Value pair” functionality in CUT Light. This allows you to “Set” and “Get” values that are independent of the report’s variable process, so they aren’t reset by the drill-down.
https://www.milletsoftware.com/CUT_Light_User_Manual/#uflLookupGetEntry()

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.

If you want to change the properties of a section, you go into the Section Expert(“Report” menu). You can also get to the “Section Expert” by right-clicking on a section name and selecting the option “Section Expert” from the “Flyout” menu. This menu also gives you a few properties you can change for a section without going into the Section Expert, like hiding, suppressing or deleting a section.

But I’ve noticed something strange in recent versions of CR. This flyout menu doesn’t always respond to user choices. I have noticed it both in my own environment and also when working remotely with several customers. It can be annoying to repeatedly right-click on a section name and have your choice ignored. One customer just stopped using the flyout menu altogether and just started navigating the main menu to open the Section Expert.

Now I think I have figured out a pattern for this behavior. If I right-click on a section quickly, without moving the mouse at all, the flyout menu will appear but it won’t respond to my choice. If, however, I right-click on a section and hold the cursor while dragging down a bit before I (left) click on my choice,  then the menu will respond to my choice.

I am guessing this is a bug, since I have not seen this problem with any other flyout menu in Crystal.

Crystal Reports is a great tool, but it isn’t perfect. One of my long standing complaints is how hard it can be to grab the sizing handles on an object using the mouse. The active zone around the sizing handle is tiny, so you have to be very precise or you end up dragging the object around instead of sizing it. You would think that zooming in would make the active zone bigger, but it doesn’t.

So I was very happy when I discovered, years ago, that you could size objects with the keyboard. If you hold down the shift key and hit the left/right arrow keys you can make the object’s right side move in and out. If you hold down the shift key and hit the up/down arrow keys you can make the objects bottom edge move up and down. If instead you hold the CTRL key while you hit the arrow keys, the entire object moves in one of the 4 directions but the size doesn’t change.

But soon after I started using Shift-Arrow I found that it didn’t work half the time. It was just this past week when I figured out the two situations where you can’t use Shift -Arrow to size objects:

1) When you are in Preview mode. For some reason using Shift-Arrow only works in Design mode. This is odd because using CTRL-Arrow to move objects can be done in both design and preview. That may be a bug but I found it true in all the versions I tested.

2) When the edge of the object snaps to a guideline. Once an object is ‘snapped’ to a vertical or horizontal guideline, the snapped edge can’t be moved using Shift-Arrow.  But following the pattern mentioned above, guidelines snapped to the top or left side do not prevent using CTRL-Arrow to move objects.

I was working with a customer’s report on my PC and trying to change a parameter. Instead of showing me a list of values I was presented with an unusual dialogue, looking for credentials. This dialogue didn’t look familiar and it took me a few minutes to figure out why it was there. The parameter I was using was a dynamic parameter and it was trying to pull a list of values from the database. That database didn’t exist on my PC, of course, so this is the window I saw:

As you can see it doesn’t allow you to change the name of the server or the DB. It does allow you to log in but that won’t help because I suspect you will only see this when the DB doesn’t exist. To get by this you have cancel and then change the dynamic parameter to pull form a local source.

So if you see a strange login that has “Enter Values” at the top and “Use Single Signon Key” at the bottom, check to see if your dynamic parameters can find their data.