Max Numeric Value or Final

[Luther1978]

I have one column which holds a payment type for works done. These payment types are Interims which can be many over the life of the order, or Final payments on completion of the works. The Interims are stored in the PayType field as I1, I2, I3 depending on the number of interims submitted. Final payements are exactly that and there will only ever be one and so that payment type will simply be F.

I want a method to report on only those Interims of the highest numerical value, so I3 rather than I2, or the final payment F if this exists. There may be many lines against each payement an example of data would be:

WorkOrder PayType WorksItem Usage Rate
========= ======= ========= ===== ====
A I1 1 10 20
A I1 3 15 10
A I1 7 5 50
A I2 1 15 20
A I2 3 17 10
A I2 7 6 50
A I2 10 20 3
A F 3 19 10
A F 7 8 50
A F 10 20 3
A F 1 20 20

So in this case the cystal report will report only on the 4 lines with PayType F.

If the F's weren't there then it would report only the highest Interims, that being the 4 line items of I3, Ignoring all the others.

I can do this in MS Access by passing information through queries, but I can't work out to do this in Crystal. Do I need to create some views on the SQL server? I need some of the features of Cystal over access.

Thanks in Advance

Martin King

 

[synapsevampire]

Yeah, Crystal isn't as good at such things as Access, and keep in mind that you can create queries in Access and expose those as the datasource for the Crystal Report.

Here's one method in Crystal:

create a formula called PayType:

if {table.paytype} = "F" then
"ZZZ"
else
{table.paytype}

Now group by the Workorder, and then go to the Report->Selection Formulas and place:

{@PayType} = maximum({@PayType}, {table.workorder})

This will suppress the rows that are not the max for each workorder.

I'd go with using the Access query though, much cleaner.

-k

 

[Luther1978]

Thanks mate, makes sense. I will give it a go and let you know.

 

[Luther1978]

I do believe that worked. Cheers.