Date range parameter Grouping Issue

[skries]

I am trying to design a report that will group records based on date range parameters.

There is a WORK ORDER table joined to a TIME CHARGES table by the WORK ORDER NUMBER.

I need to indicate the work order as completed based on when time was entered into the TIME CHARGES table.

For example:
work order # 123456 has time dates of
6/1/2004, 6/5/2004

work order #123458 has time dates of
6/1/2004, 7/3/2004

I need to indicate that all work orders that have the maximum time dates from 6/1/2004- 6/30/2004 as 'Complete'
and all others as 'Not Complete'

Any ideas would be appreciated.

 

[lbass]

You have two options, I think. Group on {workorder.wo#} and insert a maximum on {timecharges.date}, and then go to report->topN/group sort and choose "maximum of timecharges.date", which will order the workorders so that the complete ones are clustered together, as are the incomplete ones, but without true groups. You can't really group on a summary unless you use a SQL expression.

If you are able to use a SQL expression, then you could create a new SQL expression {%maxdate}:

(select max(AKA.`timedate`) from WorkOrder AKA where AKA.`wono` = WorkOrder.`wono`)

Substitute your exact table name for "WorkOrder" and your exact field name for "wono" (work order number). The correct creation of SQL expressions can vary depending upon datasource, so the exact way to use quotes, e.g., might be different for you. If you get this to work, you can then use the result in a formula in the formula editor:

if {%maxdate} <= Date(2004, 06, 30) then "Complete" else "Incomplete"

Then you can group on this.

-LB

 

[skries]

I was able to perform the Crystal suggestion and was able to identify what was complete or incomplete.

I am unfamiliar as to how to implement the SQL suggestion you gave me.

I will still need to subtotal the completes and incompletes.

 

[lbass]

You might want to have a formula that identifies the completes/incompletes, so create {@completionstatus} for the group header:

if maximum({timecharges.timedate},{workorder.workorderno}) <= Date(2004, 06,30) then "Complete" else "Incomplete"

Then create another formula {@countbystatus} and place it in the group header or footer and suppress it:

whileprintingrecords;
numbervar x;
numbervar y;

if {@completionstatus} = "Complete" then x := x + 1 else
if {@completionstatus} = "Incomplete" then y := y + 1;

Then create two formulas for placement in the report footer:

//{@complete}:
whileprintingrecords;
numbervar x;

//{@incomplete}:
whileprintingrecords;
numbervar y;

-LB

 

[skries]

That's pretty much what I ended up doing.

Thanks for your help. I learned something new about the SQL Statements as a result.