SUMMING AT GROUP LEVEL

[wolverinec7]

i have a report that uses the following formula:

datediff("n",maximum({@stop time},{CALL.CALL_NUMBER}), maximum({@start time},{CALL.CALL_NUMBER}))/60

it works out, at record level, the difference between two dates. the report is grouped by call number. is there anyway to sum this formula at this group level?

 

[aaronburro]

I am assuming that by "sum at the group level," you mean "sum for the entire report." If that is the case, then just take out the {CALL.CALL_NUMBER} and that will figure it for the entire report. however, I doubt that is what you actually want.

What exactly is this formula trying to accomplish?

 

[wolverinec7]

for any given call there maybe multiple visits by an engineer, the report at group(2)(sorry i put record before) level works out the time diff between the end of visit 1 and the start of visit 2(by using a running totals on two date fields). its also grouped (1) by contract type and they want it to sum at this group 1 level.

 

[aaronburro]

OK, I'm thinking that your report looks like this, right?

Contract Type
Call Number
Records

And so, you are wanting to sum all of the time between visits?

 

[wolverinec7]

yes, the record level is suppressed. at the call number (group 2) level i have 2 running totals. one gives me the max of visit 1, the other the min of visit 2 date time.
this formlula works however they want a sum at the contract level(group 1) and i can't get it to do it.

 

[aaronburro]

i hate to ask so many questions, but I'm still a bit confused on this. I'm going to call the time between the end of visit1 and the start of visit2 the "down time."

1) Am I to understand that you need to sum all of the down time for a call?
2) Am I to understand that the time is stored in either a range or an array? If its stored in an array, then how many entries are in the array?
3) Am I to assume that the information from visit1 is stored in a different record than visit2?

 

[wolverinec7]

its ok its my poor explanation. below is an example

call no visit no start date stop date diff
123 1 1/1/05 11.00 1/1/05 12.00 -
123 2 2/1/05 12.00 2/1/05 13.00 24 hours

my formula works out the diff between the stop date of the first record and the start date of the second which is held in the same table. obv these are at record level and my form is at group 2 level (call number). however this call and for example 5 others maybe grouped(1) under national accounts and so it needs to sum the 6 formulas which is just a date diff on two running totals

 

[aaronburro]

ok. as I see it right now, then, your request is kind of impossible with your current report's setup. The reason is that Crystal doesn't let you perform a summary on a summary.

Is it possible that you could move the national accounts group below the call number group?

 

[wolverinec7]

no. i think the point is they're trying to see if certain contract types are resolved or visited quicker than others so they have to put all nationals together etc.
thanks anyway.

 

[aaronburro]

Well, the only other thing I can think of will only work if the different accounts have the same callnumber for the calls in question. Unfortunately, since my database doesn't allow me to do this, I can't experiment with it and I can't tell you for sure how it works.

But, the solution would be to create a SQL expression that ignores the national account group and just groups by call number.

what do your {@startime} and {@stoptime} formulas look like?

 

[wolverinec7]

start time =
if {THREAD_EVENT.THREAD_EVENT_VISIT_NUMBER} = 2 then {THREAD_EVENT.START_DATE_TIME}

stop time =
if {THREAD_EVENT.THREAD_EVENT_VISIT_NUMBER} = 1 then {THREAD_EVENT.STOP_DATE_TIME}

 

[aaronburro]

so, there are only 2 possible visits per call?

 

[wolverinec7]

no but they're only interested in the difference between the first and second. the logic being if it takes more than two visits there must be a real problem thats not to do with the processes we have in place. i think they're trying to prove or disprove any bias towards diff types of customers, its the age old service V sales argument.

 

[aaronburro]

nothing personal, but it just sounds like the data entry isn't consistent, you know? I mean, it doesn't make sense that the same call would be handled on two different account numbers.

So, the only solution I can really pose at this point will give you a summation of "down time" for each account. Here's what you do. Create a formula:

{@Revisit Time Difference}:
if (Previous({CALL.CALL_NUMBER}) = {CALL.CALL_NUMBER}) then
(
if (Previous({THREAD_EVENT.THREAD_EVENT_VISIT_NUMBER}) = 1)
then dateDiff("n", Previous({THREAD_EVENT.STOP_DATE_TIME}), {THREAD_EVENT.START_DATE_TIME})/60
else 0
) else 0
------------------------------------------------------------

Put this formula into the details section of the report and suppress it.

Next, right click the formula and click "Insert Summary." Choose the appropriate group for the summary and then make sure the box "Insert summary for all groups" is checked. Click OK and you are good to go.

Like I said, this won't allow you to compare individual calls on different accounts w/ the same call number, but it might be close enough to help out. Otherwise you are gonna have to go the route of SQL expressions, and even then, depending on how your CSR's enter the data, it still might not work.

 

[lbass]

You can't insert summaries on formulas using previous or next either.

You can summarize your formula using a variable:

//{@reset} to be placed in GH#1:
whileprintingrecords;
numbervar sumdiff;
if not inrepeatedgroupheader then
sumdiff := 0;

//{@accum} to be placed in your GH#2 or GF#2 section:
whileprintingrecords;
numbervar sumdiff := sumdiff + {@yourdatediffformula};

//{@display} to be placed in GF#1:
whileprintingrecords;
numbervar sumdiff;

-LB

 

[aaronburro]

You can't insert summaries on formulas using previous or next either.

woops, caught me with my pants down

 

[wolverinec7]

ibass you are awesome that works a treat. cheers.