Selecting first occurance-Date--CR9

[JoyCR9]

In Crystal Reports version 9, I'm working with a call tracking database (SQL/Oracle).

On my detail line (group=call id number), I have the following formula to tell me what time(s) the call was escalated to a vendor (note: it returns accurate data):

if {SERVICE_CALL_EVENT.SVC_CALL_EVENT_TYPE}="ESCALATE_VEND"
then {SERVICE_CALL_EVENT.ACTION_DT}

It will return various data and occurances. For eg:
call 123 may have nothing for vendor escalated and therefore report nothing for this report.

Call 234 may have only one occurance and return 2004-02-03 13:00:00

Call 456 may have four occurances and return four separate date and times.

I want the first occurance, if there is one. How do I get this? When the report is complete, I will have the detail section suppressed and will be working with the group footer information.

Thanks in advance.

 

[bdreed35]

Have you tried creating a summary on the formual in your post?

Create a new summary for that formula, and choose minimum for the summary type.

~Brian

 

[JoyCR9]

When I do a minimum on this, I get a blank result.

 

[bdreed35]

I would modify you formula to something like this:

(I assuming that you date field is a date data type)

if {SERVICE_CALL_EVENT.SVC_CALL_EVENT_TYPE}="ESCALATE_VEND"
then 
    {SERVICE_CALL_EVENT.ACTION_DT}
else
    CDate(3000,1,1);

This avoids having any blank values returned by your formula by putting a value that would be well out of the n ormal range at the high end.

Now create a formula to use instead of the summary I had you create like this:

if minimum({@your_formula},{table.caller_id_number}) <> CDate(3000,1,1) then
    minimum({@your_formula},{table.caller_id_number});

~Brian

 

[JoyCR9]

I don't think I made myself clear.

I have 4 (sometimes more, sometimes less) dates:

2004-02-02 11:59:01
2004-02-03 09:20:45
2004-02-03 15:27:27
2004-02-04 08:00:00

They are each on separate detail lines within a group. I want 2004-02-02 11:59:01 to print on my group footer (for further evaluation).

Thanks and sorry for the confusion.

 

[JoyCR9]

I figured out why minumum doesn't work. The detail section has records with this field being blank.

Minimum is picking up blanks. How do I get it to pick up the first occurance that is not blank?

 

[MVisconte]

Is it blank or null? Removing unwanted records correctly is important. We wind up doing one of two things, depending upon the report requirements:

Filter out un-wanted records:
Add a clause to your WHERE statement, like:

  AND NOT ISNULL({SERVICE_CALL_EVENT.ACTION_DT})

I don't know if this is appropriate for you, because you may have other information you need even if there are no dates.

If you are dealing with blanks, you can also test for/ filter these, but this is usually a text/string field type issue.

Alternately, you can combine the null testing and the conditionals mentioned above into a user formula and Summary/MINIMUM on that:

@DateCheck
If not isnull({SERVICE_CALL_EVENT.ACTION_DT}) then
  {SERVICE_CALL_EVENT.ACTION_DT} 
else
  Date(3000, 1,1)   (or some such illogical date)

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[MVisconte]

Is it blank or null? Removing unwanted records correctly is important. We wind up doing one of two things, depending upon the report requirements:

Filter out un-wanted records:
Add a clause to your WHERE statement, like:

  AND NOT ISNULL({SERVICE_CALL_EVENT.ACTION_DT})

I don't know if this is appropriate for you, because you may have other information you need even if there are no dates.

If you are dealing with blanks, you can also test for/ filter these, but this is usually a text/string field type issue.

Alternately, you can combine the null testing and the conditionals mentioned above into a user formula and Summary/MINIMUM on that:

@DateCheck
If not isnull({SERVICE_CALL_EVENT.ACTION_DT}) then
  {SERVICE_CALL_EVENT.ACTION_DT} 
else
  Date(3000, 1,1)   (or some such illogical date)

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports