Suppress fields to Export to Excel

[CLKCLK]

I have a report that is looking at table.details and showing a starttime or endtime based on criteria in the formula. Here are the formulas.

@startime
WhilePrintingRecords;
DateTimeVar BeginDate;
if
{table.Details.Action ID} = "HD_FRWD_GROUP" then
BeginDate :={table.Details.Date};

@endtime
WhilePrintingRecords;
DateTimeVar EndDate;
if
table.Details.Action ID} = "HD_FRWD_STAFF" then
EndDate := {table.Details.Date};

Here is a copy of the details section:

Startime EndTime Action ID
HD_OPEN
HD_TAKEN
START_CLOCK
2/23/2010 6:42:30 PM HD_FRWD_GROUP
2/24/2010 6:57:28 AM HD_FRWD_STAFF
HD_STATUSCHA
HD_CLOSE

Is there anyway to make the starttime and endtime line up or have the blank spaces suppressed based on Action Id so I can export this to Excel to calculate work hours between the two dates. I tried calculating the work hours in Crystal but I get an error in the formula for @workhours "This Formula Can not be used because it must be evalulated later" when I try using my @starttime and @endtime formulas. Any help would be appreciated.

CLK

 

[lbass]

Why not elminate the other Action IDs in your record selection formula? If there are only these two codes left per group, then you could just use datediff on the minimum and maximum datetimes.

-LB

 

[CLKCLK]

I changed the selection criteria to only include those action Ids'

I need to calculate business hours between the two dates. So I didn't think I could use datediff.

I am running into another problem that I didn't expect. HD_FRWD_STAFF multiple times. Here is the details section

Incident # Startime EndTime Action ID
1375476 2/24/2010 7:20:26 AM HD_FRWD_GROUP
1375476 2/24/2010 7:21:08 AM HD_FRWD_STAFF
1375489 2/24/2010 7:34:18 AM HD_FRWD_GROUP
1375489 2/24/2010 7:36:24 AM HD_FRWD_STAFF
1375494 2/24/2010 8:10:04 AM HD_FRWD_STAFF
1375494 2/24/2010 8:16:33 AM HD_FRWD_STAFF
1375494 2/24/2010 8:31:11 AM HD_FRWD_STAFF
1375494 2/24/2010 8:04:41 AM HD_FRWD_GROUP
1375547 2/24/2010 8:01:28 AM HD_FRWD_GROUP
1375547 2/24/2010 8:02:06 AM HD_FRWD_STAFF

With incident # 1375494 I would want the earlist time 2/24/2010 7:36:24 AM. How would I go about eliminating the times that are greater than that so that I would only have one HD_FRWD_GROUP time and one HD_FRWD_STAFF time like with incident# 1375489, 1375547,1375476.

Thanks
CLK

 

[lbass]

So it doesn't matter whether the endtime is BEFORE the starttime? That doesn't make sense to me.

-LB

 

[CLKCLK]

You are right LB I didn't notice that. So that means I would need the last HD_FRWD_STAFF timestamp, so for Incident 1375494 it would be 2/24/2010 8:31:11 AM. Then I would calculate the business hours between the startime 2/24/2010 8:04:41 AM and the endtime 2/24/2010 8:31:11 AM . I just need to eliminate all the other HD_FRWD_STAFF times.

 

[lbass]

Then you should be able to group on Incident # and then use something like this for the duration:

datediff("s",minimum({@starttime},{table.incident#}), maximum({@endtime},{table.incident#}))


....where:

//{@starttime}:
if {table.actionID} = "HD_FRWD_GROUP" then
{table.datetime}

//{@endtime}:
if {table.actionID} = "HD_FRWD_STAFF" then
{table.datetime}

Not sure what you want if there are more than one GROUP records per incident, so I used minimum.

-LB

 

[CLKCLK]

I am grouping on incident #, but I only get one or the other for the starttime and the endtime. This is from the group footer for Incident#

Incident # Startime EndTime
1,375,476 2/24/2010 7:21:08 AM
1,375,489 2/24/2010 7:36:24 AM
1,375,494 2/24/2010 8:04:41 AM
1,375,547 2/24/2010 8:02:06 AM
1,375,637 2/24/2010 8:42:45 AM
1,375,655 2/24/2010 8:57:00 AM
1,375,674 2/24/2010 9:59:07 AM
1,375,682 2/24/2010 9:47:53 AM

 

[lbass]

I don't know what point you are trying to make or what you are putting in the group footer. If you want to SEE the starttime and endtime in the group footer, you should use my formulas for starttime and endtime and insert summaries on them at the group level. You can add the datediff formula directly to the group footer.

-LB