I do this chart each year based on my newsletter subscribers. It shows the version of Crystal Reports that they were using when they signed up for the newsletter. Over time, it shows which versions have staying power and how quickly new versions take hold. I have included numbers for 2017, even though the year is only about half way there. Those numbers will change in next year’s chart when 2016 is complete.

The chart shows that over 40% of my 2017 subscribers are using Crystal Reports XI, which is over 10 years old. I was concerned that some users might really be on CR 2011 and confused the numbers. Those two versions get confused quite often. So I Emailed some of those people to see if that was a factor, but so far I haven’t heard from anyone who made that mistake.

I think the main factor is probably related to the runtime engine. For some CR customers, upgrading past XI would be a major undertaking, because all the newer versions are limited to the .NET runtime. Customers built an application around the older runtime engine would have to rebuild their app in order to upgrade to a later version of CR.

There is one thing you have to watch out for when you use the “Select Distinct Records” setting to eliminate duplicate records. There is often a difference between what you consider a duplicate and what SQL considers a duplicate.

When you activate this feature (Database > Select Distinct Records) Crystal changes the first line of the SQL query from:

Select

to

Select Distinct

This causes the SQL engine to look for duplicates within your raw results.  To the SQL engine, duplicates are any records that have the exact same value for every column in the results or every field listed in the SELECT clause of the query. This will be every field used by the report in any way.  You can see which fields these are by looking for the check marks next to the field names in the field explorer. If the database finds multiple records with the exact same values for every field, it will eliminate the extras and return only one of the duplicate records.

So here is the risk. Pretend that you have three records in the results that were mostly identical but had one field that was different, say 3 different timestamps. As long as the report doesn’t use the timestamp field then the “Select Distinct” will return only 1 of the 3 records. But as soon as someone decides to use that timestamp field, even if they just place it on a section, the SQL will see three unique records. Your report will then start to show all three records, even though you might consider them duplicates.

So if you are using “Select Distinct”, make sure you test any changes carefully, especially when they involve adding new fields to the report.

A few years ago I wrote a blog post about creating a date range parameter when your date field was stored in numeric values (eg. 20170614). I found that the best approach was to create a normal date range parameter, write formulas to convert the begin and end dates into numeric values and then use those two formulas in the selection formula. This gives the user a familiar calendar interface but still passes index eligible values to the SQL query.

Recently I faced a similar problem, but in this case the customer’s database stored the dates as strings. They were using a formula to convert the string values from the database into a true date and then they used that in the selection formula. The problem is that this criteria can’t be passed to the SQL, so the database ends up sending back ALL of the dates to Crystal. Then Crystal has to select the correct dates locally.

I changed the report to follow the same model as above. I used the existing Date Range parameter but wrote two formulas to convert the begin and end dates from the parameter into corresponding strings.  The formulas to convert the parameter values looked like this:

//BeginDate:
Totext (Minimum ({?Date Range}), 'yyyyMMdd')

//EndDate:
Totext (Maximum ({?Date Range}), 'yyyyMMdd')

Then our selection formula was:

{GL_MASTER.LAST_DATE_CHG} in {@BeginDate} to {@EndDate}

The users didn’t see any differences when selecting dates, but they certainly saw a difference in the performance of the report.

After my newsletter went out I heard from a user who had the “cascading” failure problem and received a solution from SAP. She was told to go into each subreport and open “File > Report Options”.  There she was told to take out the check mark for the feature “Show Preview Panel”.  She started doing this with all of her subreports and she never saw the problem again.

I suggested this fix to two other users that had experienced the same problem.  It solved the problem for one user but not the other.  So it may not be a sure fix for everyone, but it certainly is worth trying. Please let me know if this works for you or if it doesn’t.

That is also something that I didn’t know, that subreports can have “report options” that are set different than the main report.  I had never checked but just assumed that the properties in “File > Report Options” applied to the entire container.  Always good to learn something new.