Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: First record matching a criteria Post Reply Post New Topic
Author Message
stijntienen
Newbie
Newbie
Avatar

Joined: 07 Jun 2008
Location: Belgium
Online Status: Offline
Posts: 9
Quote stijntienen Replybullet Topic: First record matching a criteria
    Posted: 11 Aug 2008 at 4:39am
Hello,
I would like to find a solution for following issue:
I have a table with following  data
Time stamp                 Status
0900                          Off Duty
0901                           Available
0902                           On Call
0905                           Available.
 
I would like to find the Time stamp of the first time status "Available" appears.
I made this formula but it is nog correct because it evaluates all records and the last one is not matching "Available".
 
if {AgentActivityLog.StatusKey} = "available" then
    time(minimum({AgentActivityLog.StatusDateTime},{@SV_Group StatusDate}))
else
    time(maximum({AgentActivityLog.StatusDateTime},{@SV_Group StatusDate}))
 
Thanks for the help.
stijn
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 12 Aug 2008 at 12:45pm
Is there a group a level above the timestamp?  If so and if the list of statuses isn't huge, you might be able to do something like this:
 
1.  Create a running total for each status.  This will be a count of the timestamp.  Under Evaluate, select Use a formula, click on the formula button and enter something like this:
 
{table.status_field} = 'Off Duty'
 
The value in the formula will be based on which status the count is for.  Under Reset, select On Change Of Group and select the group at the level right above the timestamp/status data.
 
2.  For the section where you're displaying the data, set a suppression formula that looks something like this:
 
({table.Status_field} = 'Off Duty' and {#Off Duty Count} > 1) or
({table.Status_field} = 'Available' and {#Available Count} > 1) or
...<continue for each status>
 
Note where I put the parentheses - this is VERY important because the formula won't evaluate correctly otherwise.
 
-Dell
IP IP Logged
stijntienen
Newbie
Newbie
Avatar

Joined: 07 Jun 2008
Location: Belgium
Online Status: Offline
Posts: 9
Quote stijntienen Replybullet Posted: 12 Aug 2008 at 11:39pm
Hello Hilfy,
Thanks a lot for your support.  This works well, but it is hiding / showing statuses in the details section.  I would like to create a formula which I can put for example in the report footer, showing the timestamp a specific call center agent was in "available" state that day.  Can you please assist some more, I'm lost.  In SQL I would need to use a findfirst command and show the value.
Thanks,
S
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 13 Aug 2008 at 7:14am
If it's going to go in the Report Footer, you'll probably need to do it in a subreport.  There's really no other way of doing this kind of summary at the beginning or end of a report.  In that case, you can use the technique I outlined in the subreport to get the information you're looking for.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.015 seconds.