Refresh prompt when you change pages?

Sunday 16 July 2017 @ 2:03 pm

This strange behavior has nagged at me for a while. I refresh a report that has subreports and preview the first page. When I move to the next page I get a prompt that says:

“Change in record selection formula”

and the choices are “Use Saved Data” or “Refresh Data”.

Now it is obvious that I haven’t changed the selection formula by clicking the “next page” arrow, and yet I have seen this behavior in several reports. Usually it doesn’t cause a problem and I just ignore it.

But today I worked with a customer who wanted to fix that behavior so we took the time to do some experiments. After a few tries it finally dawned on me that there was one way that a formula will change, all by itself, between one subreport and the next. When a formula references the current time it will change slightly as you page through the report. Sure enough, the selection formula in the subreport was limited to activity in the last 15 minutes:

{DateTime.Field} in DateAdd ('n', -15, CurrentDateTime) to CurrentDateTime

To fix the problem we need a single DateTime value to use in all of the subreports. So we decided to calculate a value in the main report and pass it down to the subreports as a parameter. To make sure the value didn’t change on each page we wrote a formula to combine the functions DataDate and DataTime like this:

// {@DataDateTimeEnd}
DateTime(DataDate, DataTime)

The advantage of using the DataDateTime is that once the report is refreshed that value doesn’t change from one page to the next. The CurrentDateTime function can return different values as the report goes through the pages.
I then calculate a second DateTime that is 15 minutes before the value above:

// {@DataDateTimeBegin}
DateAdd ('n', -15, {@DataDateTimeEnd})

Last, I pass these values down to the subreports as linked parameters and use them in the selection formula like this:

{DateTime.Field} in {?Pm-DataDateTimeBegin} to {?Pm-DataDateTimeEnd}

We still get the last 15 minutes of transactions, but without any prompts.

Sorting numbers that come after letters

Monday 10 July 2017 @ 7:01 pm

I recently had a customer ask if I could help him sort part numbers. The problem was that the part numbers start with one or more letters followed by 2 to 7 digit number and sometimes a dash in between. Since the field is a string, and since the numbers vary in length, the sort doesn’t work as expected:


(See this article for why dashes are sometimes ignored in sorting. )

So here are the steps to get these to sort in a meaningful way. First I wrote a formula to determine how many initial characters there are. This is based on a formula I wrote about before that was designed to strip all numbers off of the right end of a string:

//{@Initial Chars}
Local StringVar x := replace ( {ITEM.ITEMNO} & '1' , '-' , '' );
Local NumberVar y := Length( Totext( Val( strReverse( x ) ) ,0,'') );
if length(x) = y then 0 else length(x) - y

Then the remaing steps are:

1) Eliminate the dashes.
2) Strip off the initial letters and convert the remainder to a numeric using Val().
3) Use Totext () to convert the number into a zero-padded string
4) Tack the initial letters back on.

With local variables you can see the process, step by step.

Local StringVar x;
Local NumberVar y;
x := Replace ({ITEM.ITEMNO} , '-', '') ;
y := val (x [ {@Initial Chars} +1 to 99 ]);
x [ 1 to {@Initial Chars} ] & Totext (y, '0000000')

The resulting values would look like this if you placed them on the report. But these would probably only be used for sorting and not displayed.


Crystal Reports versions over time (2017)

Wednesday 28 June 2017 @ 6:43 pm

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.

Version of Crystal Reports used by my subscribers each year

Caution when using “Select Distinct”

Monday 19 June 2017 @ 7:56 pm

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 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.

Date range parameters for dates stored as strings

Thursday 15 June 2017 @ 9:45 pm

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:

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

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.

Possible solution the cascading subreport failure

Thursday 8 June 2017 @ 3:10 pm

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.

Server-based scheduler comparison (2017)

Monday 29 May 2017 @ 4:28 pm

I have just updated my comparison of server-based scheduling tools for 2017. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.

There are 11 products on the list this year (one new) and a few few feature updates and price changes. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.

Recurring subreports cause “cascading” failure

Tuesday 23 May 2017 @ 1:49 pm

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.

How to “break” specified order grouping

Tuesday 16 May 2017 @ 2:00 pm

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.

Creating a master “dates” table on the fly.

Tuesday 9 May 2017 @ 10:32 am

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:

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.

«« Previous Posts
Versa Reports

remiCrystal reporting solution