I was helping a customer troubleshoot a misbehaving report. He could add criteria to the report, but when he removed the original criteria from the selection formula the it was still applied. So I looked at the SQL that CR was generating and found that it was not being updated based his our changes.  After a few more tries my customer asked me about an unfamiliar button below the “SHOW SQL QUERY” window. Only then did I noticed the extra button that said RESET.   I had not seen that button in about 10 years, but I now knew the problem with this report.

This report was originally created in an early version of Crystal, probably v8.x. In these early versions you were allowed to tweak the FROM and WHERE clauses in the automatically generated SQL.  This is no longer allowed because now we can create SQL Commands.  Back then, when you modified the SQL it became ‘locked’ and Crystal would no longer update it. If you added more selection criteria the new criteria would not be incorporated into the SQL. Instead it would be applied after the data came back from the database.  When you wanted to revert to automatically generated SQL you would hit the RESET button.

In current versions of CR the RESET button is not needed so it doesn’t normally appear.  But it will appear automatically if you open an old report that has locked SQL.  This allows you to eliminate the use of an obsolete feature.  So I checked the SQL to see if the original tweaks were still essential.  There were no UNION queries, subqueries or filters built into outer joins. So I reset the SQL and the RESET button disappeared. After that the report behaved like a normal report.

After reviewing a few more BI products, I have decided to focus my comparison of Crystal alternatives on true reporting tools rather than the broader category of BI tools. My guiding question is still this:

If I were to switch from Crystal Reports to another product, what features would I gain and what features would I lose?

I found that several of the leading BI tools provide primarily high level summary and/or visualization. They don’t have the ability to create the day-to-day operational forms (invoices, purchase orders, custom reports).  I create these every day in Crystal Reports.  So I have dropped some of the tentative columns in the original grid and replaced them with two more true reporting tools, Jasper Reports and Cognos Impromptu.

The two new columns have been started but are not completed yet. I was able to fill in the rows that describe each tool’s basic approach, but I don’t know all of the detailed features that each supports. If anyone has a working knowledge of these tools and is willing to fill in some of the feature rows, that would be a great help.