Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Suppression question 1

Status
Not open for further replies.

ziggs

Technical User
Sep 21, 2000
195
US
I have a report that gives these lines as an example to better explain my question.

CALL PERSON DISPATCHED ARRIVE TIME DIFF
001 SMITH 04:00:00 04:10:00 00:10:00
001 JONES 04:00:00 04:15:00 00:15:00
002 EDGE 15:00:00 15:17:00 00:17:00
002 OKUBO 15:00:00 15:20:00 00:20:00
002 SMITH 15:00:00 15:35:00 00:35:00
003 JOHNS 19:30:00 19:38:00 00:08:00
004 SMITH 20:45:00 21:10:00 00:25:00

Now, here's what the above report does. For each call more than one person may respond. I'm trying to suppress this further so that I only see the lowest response time for each call. For example, call 001 should only display SMITH and NOT JONES. Now, my report does sort by arrive time within each call so that the lowest time diff for each call displays first.

So, here's what I did. I took the CALL field and Suppressed if duplicated and it looks like this:


CALL PERSON DISPATCHED ARRIVE TIME DIFF
001 SMITH 04:00:00 04:10:00 00:10:00
JONES 04:00:00 04:15:00 00:15:00
002 EDGE 15:00:00 15:17:00 00:17:00
OKUBO 15:00:00 15:20:00 00:20:00
SMITH 15:00:00 15:35:00 00:35:00
003 JOHNS 19:30:00 19:38:00 00:08:00
004 SMITH 20:45:00 21:10:00 00:25:00

Currently, I just export to excel, sort by CALL, and then delete the rows without the CALL. Then, my report is complete.

However, is there a way to have Crystal do what I'm manually doing in excel. TIA
 
There is a Previous(field) function.

Use something like this in the Suppress formula in Details section(or Group section if the lines appear there):

Previous({CALL field}) = {CALL field}

To access the Suppress formula RMC on the section and choose FormatSection.

Obviously, it will then suppress if the Previous CALL number is equal to that of the current CALL number.

Cheers
JIM



Jim Brock


 
Thanks, but I missed an element in my question. I used your suggestion and tweaked it a bit. I selected every field in my report, went to format field, checked the suppress button and entered this formula, Previous({call}) = {call}

Works great.

However, here's the missing element. I can easily create a formula to give me an average based on timediff. Unfortunately, it will add in the suppressed lines also, and I need to avoid that.

I tried to create a running total that would give an average of all {timediff} > 0, but couldn't figure that either.



 
I think I understand what you are trying to do?

Perhaps you could change the sort order to have the arrive time at the end. This time suppress if

Next({CALL}) = {CALL}

There is an Average(fld, Conditionalfield) function.

Place a formula that will look something like this:


Average({TIME DIFF}, {CALL})

This should give you an average of the Time Diff field for a particular CALL.

Hope that works....

JIM

Jim Brock


 
I tried:

Average({@timediff}, {CALL})

but I get the following error message:

The summary / running total field could not be created.

Thanks for trying to help me. I appreciate it
 
You don't want conditional suppression, you want a summary report:

1) Group on the call field
2) sort on the time.
3) Place all of the fields that should print on the group header section instead of details.
4) Suppress the group footer and detail sections. Ken Hamady
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)
 
Ken, thanks for the tip but I still have a problem. The grouping does create a list of only the ones I want to see. However, when I try to run the average in the {@timediff} I still get the same results before I performed the grouping.

To be a bit more clear, I only introduced this part of my question in my 2nd post, before grouping I receive 234 matches. After grouping I receive the 82 that I want. However, I receive the average of 234 matches. I'm trying to figure out how to get the average for 82 matches.

Thanks
 
Missed that part, but you can still do it.

The suppression does not affect the records in the report, so it doesn't affect the totals. You will need to do a running total average of that field, using only the first value of each group.

If you have V7 or V8, use Insert Running total. AVerage your field withe the evaluate on change of group (which picks up the first record).

If you have an older version, see the faq on how to do a running total using the "3 formula" technique. create one for a sum, one for a count, and calc your average that way. Ken Hamady
Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top