If than Counts

[TEM3]

Using Crystal Reports 8.5 and Oracle Tables.....

Using two tables, one that contains Cases (each record being a case). The other table contains assignments. Can be several per case (all nicely linked with Case Key).

I was asked to do a report for any cases that were created during the calendar year 2004 and that had assignments to either of two sections. The totals needed are:

2004 Cases with assignments to only section # 1.
2004 Cases with assignments to only section # 2.
2004 Cases with agginments to both sections.
The total of the above.

I was easily able to filter for the data I wanted and group by Case and then Assignment. But I had to do a hand count of 80 pages of Cases to get a total of the three conditions I was asked to total. What a drag!

How can I set up my Crystal Report to do that work for me? I know I need to have it go through the filtered records for the Case table and then look at the Assignment table to see if there exist assignments for Section #1 and/or assignments for Section # 2 and total the three possible conditions.

Seems simple, but I don't know how to start......

 

[TEM3]

OK, back to my desire to include cases that might have assignments to three or even four sections.

Since no case would ever have more than 20 assignments, perhaps I can use the following breakdown:

if {ALL_ASSIGNMENTS.Section} = "CRIM" then 1 else
if {All_ASSIGNMENTS.Section} = "TOX" then 500 else
if {All_ASSIGNMENTS.Section} = "DRUG" then 1000 else
if {ALL_ASSIGNMENTS.Section} = "DNA" then 10000 else 0

Now, being Math challenged, how can I change this formula to work with the above, if possible:

sum({@assignments},{table.caseID}) >= 10001 and
remainder(sum({@assignments},{table.caseID}),10000) <> 0