Group Footer Date Formula

[CLKCLK]

Hello-

I am trying to expand on a report I already have to show how much time Tier2 works on a ticket. There are two action_ids that can happen HD_Close (this means they closed the ticket) & WO_Open (this means they escalated the ticket to Tier3. I have the formula working to get the time the problem is when I try to place the formula in the group footer. I am grouping on Incident #. The old report was using Max@endtime and that was fine but for this report that won't work. Here is an example from the details

Incident # StartTime EndTime Action ID
1387862 3/9/2010 7:10:40 AM HD_FRWD_STAFF
1387862 3/9/2010 7:28:10 AM WO_OPEN
1387862 3/9/2010 12:21:30 PM HD_CLOSE
1388016 3/9/2010 10:17:39 AM HD_FRWD_STAFF
1388016 3/9/2010 10:41:08 AM HD_CLOSE


On Incident # 1387862 I want the endtime formula in the report footer to be 3/9/2010 7:28:10 AM the time the WO_Open action happened.

On Incident # 1388016 I want the endtime formula in the report footer to be 3/9/2010 10:41:08 AM the time the ticket was closed (HD_CLOSE).

Any suggestions would be appreciated.

CLK

 

[fisheromacse]

What sort of criteria in you table/data must be met to distinguish which time you want?

I am not in front of crystal right now, so please do not trust my syntax, but the following may be helpful...

You could use a formula in the details that tested for WO_Open and then depending on the results displayed the correct endtime.

So, a formula for the details section....
{@WO_Open_Test}
IF {table.ActionID} = "WO_Open"
then 1
else 0;

{@Var_cltime}
timevar cltime;
IF {table.ActionID} = "HD_Close"
then cltime := EndTime
else cltime := "";

{@Var_wotime}
timevar wotime;
IF {table.ActionID} = "WO_Open"
then wotime := EndTime
else wotime := "";



And a formula for the footer section....

{@WO_Open_Dispaly}
timevar wotime;
timevar cltime;
IF SUM({WO_Open_Test},{Incident #})>0
then wotime
else cltime;

 

[CLKCLK]

I am getting an error in the cltime & wotime formulas
else cltime := "";
else wotime := "";

error message = A Time is Required here

Any suggestions?

 

[fisheromacse]

remove the else "" from the formulas.

sorry, wasn't thinking about time values.

 

[CLKCLK]

I just removed the then part of the formula and that removed the error.

The WO_OPEN_DISPLAY is not working correctly it is only displaying two different datetimes for all records. I am not sure where the datetimes are coming from. In these examples it is showing 3/9/2010 8:55:35 PM

Sample of detial section

Incident # EndTime Action_ID WO_OPEN_DISPLAY
1387862 3/9/2010 7:10:40 AM HD_FRWD_STAFF 3/9/2010 8:55:35 PM
1387862 3/9/2010 12:21:30 PM HD_CLOSE 3/9/2010 8:55:35 PM
1387862 3/9/2010 7:28:10 AM WO_OPEN 3/9/2010 8:55:35 PM
1387872 3/9/2010 7:29:31 AM HD_FRWD_STAFF 3/9/2010 8:55:35 PM
1387872 3/10/2010 9:23:31 AM HD_CLOSE 3/9/2010 8:55:35 PM
1387872 3/9/2010 8:23:52 AM WO_OPEN 3/9/2010 8:55:35 PM


So in the above example for start time I want the HD_FRWD_STAFF datetime which I have working in the @starttime formula. For the endtime I want the WO_OPEN datetime if there is not a WO_OPEN action_ID then I want the HD_CLOSE datetime if a WO has not been opened or the ticket has not been closed then I will use the current datetime.

This one is very confusing to me. You can see all the correct datetimes in the details section I just can not get them to display correctly in the Incident # group footer.

 

[CLKCLK]

I removed the else not the then...

 

[fisheromacse]

If you are grouping on Incident and have the formula in the group footer, it will only show the last value calculated for the variable. I changed the else lines, see what sort of difference that makes. The WO_Open_DISPLAY should not be in the details but in the group footer. It feels like i am forgetting/missing something, but am not focusing on what it may be, sorry!

See if the below makes it better or worse...

{@Var_cltime}
timevar cltime;
IF {table.ActionID} = "HD_Close"
then cltime := EndTime
else cltime := cltime;

{@Var_wotime}
timevar wotime;
IF {table.ActionID} = "WO_Open"
then wotime := EndTime
else wotime := wotime;


Also, you willl likely need to include a reset for your group header.

 

[lbass]

I didn't look at this thread carefully, but the variable formulas must start with "whileprintingrecords" to work, because they are being read across records.

-LB

 

[CLKCLK]

Thanks fisheromacse & lbass the combo of the two worked.

I don't know what you mean about including a reset in the group footer. I don't know if I need it, but if you can explain how to do it I would appreciate it.

CLK

 

[CLKCLK]

There is something that is not working. If the Action_ID is Not WO_OPEN OR HD_CLOSE that means that it is currently being worked so I want to display the currentdatetime.

Should I make another Variable for CurTime if Action_ID Not in WO_OPEN, HD_OPEN?