Trend report - cross tab? Running total?

[RRMLM54]

(-: Hello, all
I need to build a report that shows a count of pending & overdue work orders by week, by department. Like this:

Overdue
Week Ending: 7/6 7/13 7/20
Dept1 4 3 6
Dept2 3 6 2
Dept3 2 1 8

Pending
Week Ending: 7/6 7/13 7/20
Dept1 2 1 2
Dept2 4 2 9
Dept3 1 2 7

My table has fields: Assigned date, Due date, completed date, technician assigned, WONumber.
I built a formula to group tech's into departments. I need help getting it look at the assigned date, compare it to the week ending date (that's why I thought cross-tab?) & the due date & the completed date, and return a count of how many were pending for each week, and how many were past due. It "looks" so simple, but I've been fighting with it for days!!! Please help. Many thanks! (Crystal 8.5 or Crystal 9)
Rebecca (-:

 

[lbass]

I think we need some more definition. Do you only want to include in your report those work orders that are currently not completed? Or do you want to show an historical perspective--including, e.g., those work orders that were not completed by the due date, but which have been completed since? Similarly, for the pending crosstab, do you only want to show those which are still pending, or do you want to show those which were pending as of the particular week end?

It also appears from your example that you do not want a cumulative report--is that true? In other words, you are not planning to show those work orders that were overdue as of 7/6 and still not completed by 7/13 in both of those columns. I'm assuming that pending means a work order that was opened, not yet completed, and not past due--correct?

-LB

 

[RRMLM54]

Thank you so much for your response!
It's a historical perspective that I need and it can be separate reports or subreports (which might make it easier than putting both categories in the same cross-tab).
An overdue is also considered pending.
For both the pending and the overdue work orders, I need to show a count of those that were pending / overdue as of the particular week end.
If it was created on 6/1 and due on 6/30 then it's pending and overdue on 7/6, so it gets counted in both the 7/6 pending and overdue reports.
If it's still not completed on 7/13, it continues to get counted as pending and overdue.
But if it's now 8/19, and it was completed on 8/13, the column for the week ending 8/17 should no longer count it as pending or overdue.

I built a separate table like this
WO WkEnd Stat
1 7/6 P
2 7/6 P
3 7/6 OD
4 7/6 OD
5 7/6 OD
6 7/6 Complete

Then I built this
@Pending = If Stat = P or Stat = OD then 1, else 0
@OD = If Stat = OD then 1 else 0
I put both formulas in the detail section and did sums.
So sum of @Pending for week ending 7/6 =5.
Sum of @OD for week ending 7/6 = 3.

But then for the WkEnding 7/13, if nothing has changed, the Pending number should still be 5 and the OD number should still be 3 but the numbers grow with each WkEnding column.

It feels like I'm close, but I just can't get there ....
Thanks again.
Rebecca

 

[lbass]

With your new table, you should be able to insert two crosstabs to get the correct results. For the Overdue crosstab, use {newtable.wkenddate} as the column, {table.dept} as the row, and insert {@OD} for the summary.

For the Pending crosstab, do the same, but insert a different formula for the summary {@pending}:

if {newtable.stat} in ["P","OD"] then 1 else 0

If you have tried this already, please explain what problems you are running into. From your description, you shouldn't need to do a manual crosstab. It seems like your new table solves a lot of the potential problems.

-LB

 

[RRMLM54]

IT WORKS!
MANY, MANY THANKS!
Rebecca