Crystal Reports: Grouping by date

0
votes

thanks for looking at my question.

I am trying to group in crystal reports by the reports revised date, I have 4 different categories. Basically I'm trying to group reports that are: Past Due, Due within 30 days, Due within 30-90 days, and due after 90 days.

  1. if the {reviseddate} is <= {currentdate} then these are past due.
  2. if the {reviseddate} is > {currentdate} and < {currentdate} + 30 days then these are due within 30 days.
  3. if the {reviseddate} is >= {currentdate} + 31 days and <= {currentdate} + 90 days then these are due within 30-90 days.
  4. if the {reviseddate}is >= {currentdate} + 91 days then these are due past 90 days.

I am not great with date coding and could use a little guidance on how to write this in Crystal Reports (2008). Hope I explained it right.

Thank you very much, Mike

1
crystal-reportsbasic

1 Answers

0
votes

first before any grouping create a formula field that goes something like this (pseudocode)

if ({reviseddate} <= {currentdate}) then 1
else if (({reviseddate} > {currentdate} ) and ({reviseddate} < DateAdd("d", 30 , {currentdate}) )) then 2
else if (({reviseddate} >= DateAdd("d", 31 , {currentdate}) ) and ({reviseddate} <= DateAdd("d", 90 , {currentdate}) )) then 3
else if (({reviseddate} >= DateAdd("d", 91 , {currentdate}) ) then 4

Then put your formula in the details section. Once you see your formula producing the 1,2,3,4 values as expected, then group on the formula field.

Notes:

  1. Use Crystal Report's DateAdd function DateAdd("d", 30 , {currentdate}) to handle the addition of days
  2. If your date fields are coming into Crystal as string instead of Dates, use the DateValue function to convert them to Dates first.