Saved data vanishes when you narrow the criteria.

Monday 22 April 2013 @ 11:29 am

A user in Tek-Tips was puzzled. He was viewing reports with saved data and changing the parameters to filter the report in different ways. When the saved data was generated today, he could change the parameters and filter the data as expected. But if he tried to do this on an older report, the saved data would go away.  I have run into this before and so I know the likely cause – a  date-sensitive line in the filter, something like This:

{db.DateField} in LastFullMonth

When the report is refreshed, it uses today’s date to calculate the beginning and ending of the prior month and filters the data to those dates. So a report run in April would only include March data.

But what if that report is saved with data and then reopened in May? It will have March saved data but the filter will now be looking for April data. As soon as I try to change another part of the selection formula, Crystal will apply the updated formula to the data, which includes the updated date rule. It will try to find April records when all it has are March records and zero records will qualify.

But there is a workaround. Just change this line of the selection formula:

{db.DateField} in LastFullMonth

to say this:

( If DataDate < CurrentDate – 5  then true  else {db.DateField} in LastFullMonth )

This checks to see how old the saved data is and if it is more than 5 days old it will ignore the date filter. And because the saved data has already had the correct date filter applied, the date rule is no longer needed. You can change the number 5 to whatever works for you.

Note that the Tek-Tips user was looking at the saved data through InfoView which is a web based viewer built into Business Objects Enterprise.  But the same thing can happen in any viewer, even in the normal preview of Crystal Reports.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

Leave a Reply

Recrystallize Pro

Crystal Reports Server