[ajibril]

I am running a proposal report with "Open" status, and I have two field "Target Gift date & Target Amount" in the report that I would like to summarize by quarterly and then annually. But if the target date is before today (target date <= today), I would like to group this seperately with its own summary Gift amount. So I have the "past due" group first, and then the rest of the groups.

This is how I would like it to look like:

Past Due Amount
Name Target amount targetDate
Bob $20,0000 1/7/8
Paul $20,0000 8/12/8
Summary Target Amount
$ 40,000

3rd Quarter, 2008
Name Target amount targetDate
Bob $20,0000 9/27/8
Paul $20,0000 9/30/8
Summary Target Amount
$ 40,000

4rd Quarter, 2008
Name Target amount targetDate
Bob $20,0000 11/27/8
Paul $20,0000 12/30/8
Summary Target Amount
$ 40,000

Grand total for all
$120, 000

[DrewAllen]

And where exactly are you stuck?

Drew

[ajibril]

DrewAllen,
I cannot figure-out how to move all the past due Target Due date on a separate grouping (first grouping). So if a prospect's target date is past due, he has to be moved to the first grouping. This is the only one issue I could not figure-out but the rest of the report works fine except that I could not sort the target date ASC within each Quarter.

[DrewAllen]

The easiest way to do this is base your group on a formula rather than a field. You'll want to supply a static value for dates less than today and a dynamic value for dates greater than or equal to today. For example

IF {target date} < today THEN #1/1/1900# else {target date}

Your {target date} may be a string field and may need to be converted to a date in order to get it to sort correctly. If the {target date} is a fuzzy date, you'll need to be careful about the conversion.

Drew

[ajibril]

DrewAllen,
It worked perfectly except that I would like to sort the target date within each quarter separately by ASC order.

[DrewAllen]

Crystal usually won't let you use the same field twice in your sorts, but since the group is based on a formula rather than a field, it doesn't see those as being the same field, so it won't complain if you add the {target date} as a secondary sort.

Drew

[ajibril]

DrewAllen,

That worked perfectly.