Minimum datetime is blank 1

[dodgie72]

I am using Crystal Reports XI and need some help with Minimum values:-

I have a table that reports the date/time if a job has been put on hold and off hold, this is grouped by the date

Description @on hold (formula) @off hold (formula)
01/08/2012
001 - Picking Kits
007 - Build on bench 06
027 - Finish build
028 - Build on hold 01/08/2012 15:31
029 - Build off hold 01/08/2012 16:45
027 - Finish build
028 - Build on hold 01/08/2012 16:47
02/08/2012
029 - Build off hold 02/08/2012 08:25
027 - Finish build
028 - Build on hold 02/08/2012 09:37
029 - Build off hold 02/08/2012 10:54
027 - Finish build
039 - Build complete
040 - Visual inspection alert
041 - Build inspected
046 - Build soak alert


on hold formula:-
if {WF_TEMPLATE_ACTIVITY.DESCRIPTION} = "028 - Build on hold" then {@date/time}

off hold formula:-
on hold formula:-
if {WF_TEMPLATE_ACTIVITY.DESCRIPTION} = "029 - Build off hold" then {@date/time}

When I use Maximum in a formula in the group footer it works OK but then I use the Minimum in a formula in the group footer is returns nothing:-
Minimum({@off hold},{@date/time})

I there anyone that can help?

 

[MCuthill]

dodgie72,

I am thinking that a running total evaluation of sorts should work for what you are looking to do. This will require a couple of formula fields.

First, a reset formula to clear the results of any potential prior day analysis. To complete this, a Reset Formula is needed in the {@Date/Time} Group Header. (I don't think dates can be set as null, but an obviously invalid date should be able to work in it's place as a trigger -- I opted for Jan 1 1900).
{@VariableReset_MinimumOffHold}

WhilePrintingRecords;
Shared DateTimeVar MinimumOffHold=:Date(1900,01,01);

Next, an evaluation on each detail line needs to be completed as per your original criteria -- If the code is for "Off Hold", and the variable currently holds either the trigger date or a date greater than the current record, set the current record date/time to be the new value for the variable).
{@VariableSet_MinimumOffHold}

WhilePrintingRecords;
Shared DateTimeVar MinimumOffHold;

IF {WF_TEMPLATE_ACTIVITY.DESCRIPTION} = "029 - Build off hold" THEN 
(
   IF MinimumOffHold = Date(1900,01,01) OR MinimumDateHold>{@Date/Time} THEN
   (
      MinimumOffHold:={@DateTime}
   )
)

Lastly, a formula to display the results in the Group Footer of the {@Date/Time} group.
{@VariableDisplay_MinimumOffHold}

WhilePrintingRecords;
Shared DateTimeVar MinimumOffHold;

My apologies, as I do not have Crystal Reports in front of me today to quickly up your scenario, so there may be slight changes needed to the above -- but it should get you going in the right direction.

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[dodgie72]

Hi Mike

Your a star, this worked a treat.

Thanks

Roger

 

[lbass]

Another way of doing this is:

//Offhold:
if isnull({WF_TEMPLATE_ACTIVITY.DESCRIPTION}) or
{WF_TEMPLATE_ACTIVITY.DESCRIPTION} <> "029 - Build off hold" then
date(9999,9,9) else
{@date/time}

Then insert a minimum on this. You can format the minimum to be suppressed if it equals date(9999,9,9).

-LB