CR9 - 'date' in group field as string, need report footer average

[JoyCR9]

My post is similar but not exact to

http://www.tek-tips.com/viewthread.cfm?SQID=773091&SPID=767&newpid=767&page=1

I have the following formula that finds the difference between two DateTime fields and converts it to XX:XX:XX:XX format (string):

//Convert Total Seconds to DD:HH:MM:SS
//Remove comment tags to format in
//Days X, Hours X, Minutes X, Seconds X
//and add comment tags or delete DD:HH:MM:SS formating.

NumberVar TotalSec := datediff("s",NthLargest (1, {@open_time}, {SERVICE_CALL.SERVICE_CALL_ID}), ({@min_vendor1}));
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

//comment or delete the following to format it
//with the text instead of looking like 12:05:21:05
ToText ( Days, '00', 0,'') + ':'+
ToText( Hours, '00', 0,'') + ':'+
ToText ( Minutes,'00', 0,'') + ':'+
ToText( Seconds,'00', 0,'')

//'Day(s) ' + Totext ( Days, '00', 0,'') + ' ' +
//'Hour(s) ' + Totext ( Hours, '00', 0,'') + ' ' +
//'Minute(s) ' + Totext ( Minutes,'00', 0,'') + ' ' +
//'Second(s) ' + Totext ( Seconds,'00', 0,'')

It works great! I end up with group footers listing similar output:

00:00:15:00
00:21:15:00
00:00:15:45 ... and so on

What I want/need is a way to average this output. In my 3 line eg above I'd end up with 00:07:15:15 in my report footer.

 

[bdreed35]

I would create 4 formulas to place in your details section and then suppress them.

I am not sure what you formula is called in the post above but I will call it @TimeDiff. Make sure you replace those references with the real name.
------------------------------
@Days
EvaluateAfter (@TimeDiff);
NumberVar Days;
------------------------------
@Hours
EvaluateAfter (@TimeDiff);
NumberVar Hours;
------------------------------
@Minutes
EvaluateAfter (@TimeDiff);
NumberVar Minutes;
------------------------------
@Seconds
EvaluateAfter (@TimeDiff);
NumberVar Seconds;
------------------------------

Now, create a fifth formula for the Report Footer that does the overall averaging, and the displaying in the correct format.
------------------------------
@DisplayAverageTime

ToText(Average ({@Days}),"00") + ":" + ToText(Average ({@Hours}),"00") + ":" + ToText(Average ({@Minutes}),"00") + ":" + ToText(Average ({@seconds}),"00")


------------------------------

~Brian

 

[JoyCR9]

Thanks Brian. I haven't plugged away at your suggestion yet, but have a quick question before I do.

My data evaluates "opened time" to "escalate time". There may be more than one escalate time and my formulas pull the minimum escalate time for call id (group is call id).

Will your 4 formulas work in my scenario (theorically)?

Joy

 

[bdreed35]

The 4 formulas, @Days, @Hours, @Minutes, and @Seconds, are using the variables that you defined in your formula that you posted.

I made an assumption that you wanted to average the output of your formula. Based upon the information that you provided, it should work.

One thing I did leave out was that the 4 formulas should be placed in the same section as your formula above. The fifthe formula should reside in the Report Footer.

~Brian

 

[JoyCR9]

Arggg! So close, yet so far.

Your suggestion of the 4 formulas gives me numerical fields vs strings. I see the usefulness of that. I plugged them into my GF section.

However, didn't we know that was coming, the fifth formula gives me a warning/error when you check it:

@Days ----> this field cannot be summarized.


Almost feels like back to square one again.

 

[bdreed35]

Ok, lets make an alteration to the @Days formula.

------------------------------
@Days
EvaluateAfter (@TimeDiff);
Val(Left(@TimeDiff,2));
------------------------------

Leave the other 3 alone.

See if the display formula works now.

~Brian

 

[JoyCR9]

Brian,

Prior to your last suggestion with the Val info, I was getting result that were numerical (ie: drag my mouse over top of the field and it says @Days(Number)).

To me, a (Number) field should be capable of summarizing. Am I wrong?

The @Days formula in my data all reports zero (correctly). Therefore I don't see any change with your suggestion.

The best formula to mess with is either @Hours or @Minutes. However, my programming skills are not that great and I'm not sure what needs to be altered in @Hours or @Minutes with your latest suggestion (tried a few things and erroneously got all zero output).

Thanks,
Joy

 

[bdreed35]

Ok, I got it now, I hope . I apologize for the extra effort.

At the beginning of your original formula add whilereadingrecords;

Use the original @Days formula that I gave you rather than the one in my last post.

The @DisplayAverageTime should stay the same and now work.

I think that you original formula was evaluating in a later pass than we needed it to be in order to do a summary on it. By adding "whilereadingrecords", we force it execute the formula earlier so that the summary averages could be done.

~Brian

 

[JoyCR9]

Latest @Days formula:

WhileReadingRecords ({@convert_vendor});
NumberVar Days;

When I do the error check I get the message:

Too many arguements have been given to this function @convert_vendor

I'm not sure what to take out of @convert_vendor to have less arguments.

 

[bdreed35]

The formulas should be the same as my original post:
------------------------------
@Days
EvaluateAfter ({@convert_vendor});
NumberVar Days;
------------------------------
@Hours
EvaluateAfter ({@convert_vendor});
NumberVar Hours;
------------------------------
@Minutes
EvaluateAfter ({@convert_vendor});
NumberVar Minutes;
------------------------------
@Seconds
EvaluateAfter ({@convert_vendor});
NumberVar Seconds;
------------------------------
@DisplayAverageTime

ToText(Average ({@Days}),"00") + ":" + ToText(Average ({@Hours}),"00") + ":" + ToText(Average ({@Minutes}),"00") + ":" + ToText(Average ({@seconds}),"00")
------------------------------

The only new thing would be to add a new line at the beginning of your {@convert_vendor} formula. On that new line, add "WhilePrintingRecords;".

EvaluateAfter is the only evaluation time function that gets an arguement passed to it.

If you use WhileReadingRecords, don't put any parenthesis or other values after it except a semi colon.


~Brian

 

[JoyCR9]

Brian,

I still get this field cannot be summarized on @Days.

In @convert_vendor there is a reference to @min_vendor1:

WhilePrintingRecords;
NumberVar TotalSec := datediff("s",NthLargest (1, {@open_time}, {SERVICE_CALL.SERVICE_CALL_ID}), ({@min_vendor1}));
NumberVar Days := Truncate (TotalSec / 86400);
NumberVar Hours := Truncate (Remainder ( TotalSec,86400) / 3600);
NumberVar Minutes := Truncate (Remainder ( TotalSec,3600) / 60);
NumberVar Seconds := Remainder ( TotalSec , 60);

//comment or delete the following to format it
//with the text instead of looking like 12:05:21:05
ToText ( Days, '00', 0,'') + ':'+
ToText( Hours, '00', 0,'') + ':'+
ToText ( Minutes,'00', 0,'') + ':'+
ToText( Seconds,'00', 0,'')

////'Day(s) ' + Totext ( Days, '00', 0,'') + ' ' +
////'Hour(s) ' + Totext ( Hours, '00', 0,'') + ' ' +
////'Minute(s) ' + Totext ( Minutes,'00', 0,'') + ' ' +
////'Second(s) ' + Totext ( Seconds,'00', 0,'')

@min_vendor1 is:
if minimum({@escalate_vendor1},{SERVICE_CALL.SERVICE_CALL_ID}) <> CDate(3000,1,1) then
minimum({@escalate_vendor1},{SERVICE_CALL.SERVICE_CALL_ID});

I did some reading and saw some rules with summarizing:
Most formulas are ‘before the hump’ formulas, and can be used for totals. A formula only
has to wait for the second pass if it:
1) Uses a summary function like Sum() or Maximum()
2) Uses the functions Next() or Previous()
3) Uses a variables that is shared with subreports
4) Starts with the function WhilePrintingRecords
5) Uses a running total
6) Uses another formula that includes one of these 5 features. There are several restrictions on what you can do with second pass formulas. As you already know, you can't use them with total functions, which also means that you can't use them in Cross-tabs or Charts. They are also ineligible for grouping, sorting, or being put
into the record selection formula. If you need to summarize a formula that uses one of these features, you will have to create a running total with variables.


Would this explain why I can't summarize the @Days/hours/minutes/seconds formulas?

 

[bdreed35]

I think the NthLargest function is causing the problem. It forces evaluation at print time, which would prevent a summary on the contents of the formula. If you can avoid using that, it will probably help.

There may also be an issue with either the {@open_time} or the {@min_vendor1} formulas. Try looking for print time functions in there as well.

~Brian

 

[JoyCR9]

Brian,

This is such a convoluted mess.

I thank you for your help and will basically start from scratch. I've stumbled across more issues that hinder getting results. Looks like it's faster to do things manually!

Joy