Shading a group based on record criteria 1

[stancer]

I have an Crystal Report (v8.5) reading an Access 2000 database, table name = tbl_DSPTIME.
The table contains data pertaining to fire emergency calls, which we use to determine if we are meeting standards on-scene times. Each EVENT may contain one or more records depending on the number of units dispatched to the fire. Each unit responding to the EVENT has data for DSPTIME, ENRTIME and ONSTIME. There may be as many as 8 units dispatched to an EVENT.

In addition to the table fields, the report contains duration times calculated from the DSP, ENR and ONSTIME fields - TURNOUT(ENRTIME - DSPTIME), DRIVE(ONSTIME - ENRTIME) AND TOTAL(ONSTIME - DSPTIME)

Example1 - (No unit makes it to the scene within 4 minutes or less (TOTAL):

EVENT UNIT DSPTIME ENRTIME ONSTIME TURNOUT DRIVE TOTAL
0101206 E1 11:31:10AM 11:33:00AM 11:38:44AM 1:50 5:44 7:34
0101206 E3 11:31:10AM 11:32:43AM 11:39:54AM 1:33 7:11 8:44
0101206 TK1 11:31:10AM 11:33:12AM 11:42:33AM 2:02 9:21 11:23


Example - (At least one unit makes it to the scene within 4 minutes or less (TOTAL):

EVENT UNIT DSPTIME ENRTIME ONSTIME TURNOUT DRIVE TOTAL
1040172 E5 12:02:30AM 12:03:57AM 12:04:08AM 1:27 0:11 1:38
1040172 E4 12:00:05AM 12:01:32AM 12:05:52AM 1:27 2:20 5:47
1040172 TK1 12:02:41AM 12:04:18AM 12:08:58AM 1:37 2:40 8:17

The report is grouped by EVENT (sorted asc), and has a secondary sort on TOTAL - the first record in each grouped EVENT will be the lowest TOTAL time.

I want to be able to color the entire group of records if none of the units arrives (TOTAL) within 4 minutes or less. Conversely, if any one or more units does arrive within the allotted 4 minutes, then the group of records would not be colored. I have achieved partial success at a record level by coloring the first record if the TOTAL time is more than 4 minutes, but it's not a very elegant solution. In addition, I would like to produce a count of the groups that get colored, i.e. no unit arrives within the 4 minutes.

I'm stumped... can any of you gurus help? I can provide more explanation if I have been too vague.
Thanks in advance
-Stan-

 

[lbass]

Create a formula {@<4mins}:

if {@total} < 4:00 then 1 else 0

In format section->group header->color->E-2:

if sum({@total},{event}) = 0 then crRed else crWhite

Use this same formula for format section->details->color-> E-2.

To count the events for which no units arrive within 4 minutes, create a running total:

Select {event} and count as the summary, and evaluate on change of the formula:

sum({@<4mins},{event}) = 0

Reset never.

-LB

 

[stancer]

Thanks LB - I'll give your suggestion a try and get back to this thread with the results

-Stan-

 

[stancer]

Hey LB!
The group coloring works exactly as I needed! Where all the response times to an incident are greater than 4 minutes, all the incidents in an EVENT group shade correctly. Thank you.

As for the count, as implemented, it counts all the records in a group, whereas I need it to count the group as one. Whether 1 or 8 units respond to an EVENT, if the response time(s) is greater than 4 min., it gets counted once. Basically, I need to count the non-compliant groups.
Hopefully, that clarifies things a bit.

-Stan-

 

[lbass]

Sorry, it should have been distinctcount. I'm assuming that your group is on an event ID or unique event name. Since each record within the group will have the same eventID, count gives you a count of each record. Distinctcount will return only a count of one within each group meeting the criteria.

Please let me know if you run into any more snags...

-LB

 

[stancer]

DistinctCount is just the ticket! Thank you!! Saved endless hours of trial and error (don't have a Help file available for reasons only our IT Dept. knows).

By the way, there is a 4 day training coming up in May, put on by CrystalUser.com (I desperately want to attend some type of training) - know anything about these courses? Worth attending or would the money be spent more wisely elsewhere? The courses cost $850 for two days, 2 days of Intro and two more days for Advanced. That's a whole heap of money...

 

[lbass]

I'm glad that worked for you. I'm not familiar with CrystalUser.com, so can't really respond. I've learned mostly from practice, this site, a users group I belong to (Yay, CRUGsters!), and some from George Peck's book Seagate Crystal Reports 8: The Complete Reference. You could also check out Ken Hamady's site for his newsletter, formula tips, and trainings. Other contributor's here might also have useful sites or newsletters, like IdoMillet, Chelseatech, Howard Hammerman, and others.

-LB