Blank dates float to the top on refresh: Crystal Reports 2008

0
votes

I have a report that I have been working on and have determined that Crystal Reports is playing games with me. Looking to display most recent survey for a customer in the group header, but on refresh blank dates float to the top of the details section giving me blank data in the customer group header. Some customers have no survey data, so this will help identify those ppl too.

Customer table

'custID'  'custName' 'custAddress' ...

Survey table

'custID'  'surveyDate'  'surveyRate'  ...

My report

  • Linked Customer table to the Survey table, left outer join by 'custID'
  • Grouped by 'custID'
  • Details section contains survey data
  • Survey date sorted descending.
  • I want to show the most recent survey in the group header

**Some dates in the survey table are blank, but do contain other data

Problem

  • If I refresh the report, blank dates start the details records
  • If I change sort order after refresh to ascending, then back to descending [without refreshing the data] blanks go to the bottom and the most recent survey will display in the group header. << this is what I want

What gives?

I'm making this report available in info view so I don't have to run it for the business.

1
crystal-reports

1 Answers

1
votes

If the null dates won't sort the way you want then just substitute an actual date you know will sort properly.

// Sort formula
if isnull({Survey.Date}) then date(1900,01,01) else {Survey.Date}

If you descending sort your report by this field you know every record with a null date will go to the bottom of the group.