Sort by formula feild in Group footer.

[dz0k52]

Hi I am using Crystal 8.5 with an Oracle 10 database. I have a report which uses a formula based on the results of other formulas. I am trying to sort or order the report based on the results of the formula but I cannot see it in the TOP N/Sort Groups expert. I will try and explain what I have.
The formula I want to sort by is this:
@Availability:calculation of machine availabilityThis formula is placed in The group footer of each Workorder.eqnum group.

if {@Mean time between failure} <> 0 then
{@Mean time between failure}/ ({@Mean time between failure}+ {@Avg. Time to Repair} )*100
else 0

This calculation uses the following formulas:
@Mean time between failure :average amount of time between workordersThis is also placed in the group footer of workorder.eqnum

WhilePrintingRecords;
if {@sum workorders} <> 0 then
numbervar sumhrs /{@sum workorders}
else 0

@Avg. Time to Repair :mean time to repairThis is also placed in the group footer of workorder.eqnum
(By the way this field is the only choice I have in the TOpn/Sort Group expert)

Average ({@Repair Time}, {WORKORDER.EQNUM})

Both of these formulas are also in each detail line but the final calculation is done at the group level.
I will also include the other formulas:

@ Accumulative Hours-placed in details accumulated hours between repairs
WhilePrintingRecords;
numbervar hrs:={@Time between last failure};
numbervar sumhrs:= sumhrs +hrs;

@Repair Time:-placed in group footer repair time of record
if ({WORKORDER.ACTFINISH} - ({WORKORDER.ACTSTART}))* 24 < .05 then 0
else ({WORKORDER.ACTFINISH} - ({WORKORDER.ACTSTART}))* 24

@sum workorders: accumulated workorder which meet criteria Placed in group footer

WhilePrintingRecords;
numbervar countwo:=tonumber({@Count Workorders});
numbervar tothrs:= tothrs + countwo;

@Count Workorders :-placed in details and suppressed count of workorder which meet criteria
if {@Time between last failure} > 0
then 1

@Time between last failure :-placed in details and suppressed time between last workorder
IF previous({WORKORDER.WORKTYPE}) = 'EM'
and ({WORKORDER.ACTSTART} >= previous({WORKORDER.ACTFINISH}))then
((DateDiff ("n", previous({WORKORDER.ACTFINISH}),{WORKORDER.ACTSTART} ))/60)

I hope its not too cumbersome and if more information is needed, please let me know.
Thanks for taking the time to look.

 

[lbass]

I don't believe you will be able to do this. To use topN/group sort, you need to be able to insert a summary on a field/formula. Ordinarily in situations like yours, you could have used SQL expressions (or commands in higher versions) to return the necessary summaries. You could then have used a formula to calculate using these summaries and then inserted a maximum on the formula after placing it in the detail section. TopN/group sort would have then been available. BUT, the problem is that your final formula above shows that your calculations are based on a comparison between records, using the previous() function. Because of this, I cannot think of a way you can do this.

-LB

 

[dz0k52]

The "previous" function has caused me grief with other formulas. I am having trouble displaying this result in a chart also. I am assuming its also because of the same reason. Do later versions of Crystal address this issue?

 

[synapsevampire]

No, later versions don't address this.

I would consider writing a Stored Procedure or View(s) to return the data in a more normalized fashion for the report.

-k

 

[dz0k52]

I am not familiar with using a stored procedure in report writing. Is there a thread(s) or a resource you could recommend? The scope of this seems to be quite complicated. I am selecting on two parameters including a date range and am reporting on averages of many records (including information about "previous" records).

 

[dz0k52]

I have come up with a way to manipulate the data which is more “normalized”. Although the result is not accurate, the sort order is about 99% accurate. If I can get this field to work I will display my original formula but sort by and suppress this normalized formula. Here is what I have:
The field I now want to sort by is this:
@new availability: (availability of machine)

if {@new mtbf calculation} <> 0 then
{@new mtbf calculation}/({@new mtbf calculation}+ {@average of repair time} )*100
else 0 text

@ new mtbf calculation: (Simplified Mean Time Between Failure Calculation)

if Sum ({@simple wo count}, {WORKORDER.EQNUM}) <> 0 then
{@date range calculation} /Sum ({@simple wo count}, {WORKORDER.EQNUM})
else 0

@average of repair time:

Sum ({@Repair Time}, {WORKORDER.EQNUM})/Count ({WORKORDER.WONUM}, {WORKORDER.EQNUM})

@simple wo count: (Could also use summary function)

if {WORKORDER.WONUM} = {WORKORDER.WONUM} then 1

@date range calculation: Calculates number of hours in Parameter selection

tonumber (DateDiff ("h", minimum({?Report Date}),maximum({?Report Date}) ))

I can see every field in the Top N function except the “@date range calculation”, which leads me to believe that it is the field that may be keeping me from seeing “@new availability” in the list. Is there any way of “normalizing” this field to allow it to be used in the TOP N/Sort function?