Crystal Report Sum total for each date in date range

this is my first attempt at Stack overflow, so hopefully you all can help me. I'd be happy to start contributing here, it's been such a useful tool in the past.

Okay, so I am trying to write a report that charts the total past due hours of a manufacturing facitility. What I have is a conglomaration of tables, that list each manufacturing order and give a due date and a completion date. The person running the report enters a date range and I want to generate a chart with dates along the x-axis and the sum of past due hours on the y-axis. Maybe I am missing something with an eimplementation of the three-formule trick, but i can't get this to work. I can group by due date, but that only gives me the total orders that went past due on a given date.

What I need is something like the following, that is chartable:

    For each (date in DateRange) {
       If (Due Date < i) && (Comp Date > i) {
          Past Due Hours = Past Due Hours + manufacturing order Hours
       }
    }

I know it's not in Crystal Syntax, I was just trying to get the picture across. Any help?

Solution

This sort've question comes up regular- if i've understood correctly. You have something like this?

OrderNum DueDate  CompletedDate
1        01/01/01 01/02/01
2        01/01/01 01/01/01
3        01/02/01 01/03/01
4        01/02/01 01/02/01

Now the first thing you need to do is get a table (there are other similar approaches) with all dates in it and join into your dataset so you'll end up with:

OrderNum DueDate  CompletedDate Date
1        01/01/01 01/02/01      01/01/01
2        01/01/01 01/01/01      01/01/01
3        01/02/01 01/03/01      01/01/01
4        01/02/01 01/02/01      01/01/01
1        01/01/01 01/02/01      01/02/01
2        01/01/01 01/01/01      01/02/01
3        01/02/01 01/03/01      01/02/01
4        01/02/01 01/02/01      01/02/01
1        01/01/01 01/02/01      01/03/01
2        01/01/01 01/01/01      01/03/01
3        01/02/01 01/03/01      01/03/01
4        01/02/01 01/02/01      01/03/01

Next you need to add a field which determines whether the order is {@overdue}:

if {table.duedate} < {table.date} and {table.completeddate} > {table.date} then 
 1
else 
 0;

Make sure you select default values for null values to avoid any anomalies).

You can now create a graph plotting sum({@overdue}) against {table.date}