0

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?

Caleb Keller
  • 2,151
  • 17
  • 26

1 Answers1

0

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}

L

Lee Tickett
  • 5,847
  • 8
  • 31
  • 55
  • Thanks Lee, is this the only method, it seems inefficient when we're talking about returning several hundred rows, which would turn into thousands of rows if they got selected once for each date in the date range? – Caleb Keller May 21 '12 at 19:30
  • I think so. The data points you're looking for simply don't exist in the current dataset. Depending on the size of your date range you could consider hard coding a formula for each data-point but i imagine that would be even more painful. – Lee Tickett May 21 '12 at 19:42
  • So to fill in the blanks, I used a "WITH" statement to create the Date Range. And then a "CROSS JOIN" to add it into my data. I got exactly what I needed thanks for your help. – Caleb Keller May 21 '12 at 22:38