I have seen a specific Crystal Reports failure with three different customers in the past few months and several other times before that. It happens whenever Crystal has to run “too many” recurring subreports instances. This results in a slow cascading failure. The most obvious symptom is the status bar along the bottom of the screen.  It starts to print new text over the existing text like this:

This gets worse for a few seconds and eventually Crystal locks up and has to be shut down in the task manager. Sometimes there is no error message and it just freezes, sometimes a message says “A required resource is unavailable”.  But the one consistent symptom is the status bar that overlaps while the subreports are running.

It is also an inconsistent problem, in that the same report might might fail earlier or later, and will sometimes run without an issue. You can stop the report when it is part way done with no problem, but once the status bar starts to print overlapped text, there is little you can do.

(update 6/8/17) There is one possible solution mentioned here.

I also found a few forum threads that discuss similar problems, but the solutions are all over the place.  The last resort solution is replacing the subreports with SQL Commands or complex arrays.  If you are experiencing the same issue or have any insight, please let me know.

Here are the threads I have found that might be related.

https://archive.sap.com/discussions/thread/1824033

https://archive.sap.com/discussions/thread/1899387

https://apps.support.sap.com/sap/support/knowledge/public/en/1768189

The “specified order” feature in Crystal is useful when you need to group on a field, but you don’t want the values in normal ascending or descending order. With specified order you can place some or all the values for a field into the exact order that you want them to appear. Specified order is available for groups, for the rows and columns of a cross-tab, and for the ‘on change of’ field in advanced charts. You can even use it if you are grouping on a formula field.

But what happens if you do specified order grouping on a formula field, and then change that formula to be a different data type? You will get a formula error – but not in the formula you created. It will be in the internal formula that creates the specified order, and will look like this:

If this happens to you just click [Save and Close]. Then go back into the Group Expert and change the group to Ascending order. This should clear the error and you can then go back and set up specified order for the new formula, if needed.

In some environments, developers have to maintain a master “dates” table with a complete list of dates. If used correctly, it can allow a report to fill in date gaps where there are no transactions. You would have the report start with the master dates table and then link to the date field in your transaction table using a Left Outer Join. The challenge is maintaining the master dates table with current dates.

Today, Adam Butt of APB reports (Norway) helped me create a SQL View (.SQL) for SQL Server  that generates a master dates table. It is based on the SQL for an inflation table (version B)  submitted last week by Michael Hurwood, combined with the SQL DateAdd() function. He also created a second SQL View (.SQL) for Oracle.

I see two ways to deploy this.

1) Run Adam’s .SQL file in your database to save a view. You can adjust the Begin and End dates as needed and then reference that view in your reports.

2) Extract lines 6 to 28 and insert it directly into a report as a command object. You can replace the Begin and End dates with command date range parameters if needed.

With either option, the Begin and End Dates could be replaced with calculations relative to today’s date, something like this:

WHERE
X.COL_DATE >= GetDate () - 366 AND
X.COL_DATE <= GetDate () + 366;

So if you find yourself needing a master “Dates” table, you can use this method to avoid maintaining a physical table.