Grouping Problem

[kskid]

Crystal 8.5 Professional (8.5.0.217)
oRACLE 8I
Oracle ODBC Driver


I have the following tables linked

Arrest -> Arrest_Charges -> Charge_Codes
-> Names

Arrest.nameid = Names.nameid and
Arrest.arrestid = arrest_charges.arrestID and
arrest_charges.statute = Charge_codes.statute



Each individual arrested may have one or many statutes.

Each statute (over 1500) is group into 30 categories based
of federal severity guidelines.

The categories start with

01 (Murder)
02 (Rape)
03 (Robbery)
04 (Aggravated Assault)

... and ending with

28 (Vagrancy)
29 (Curfew & Loitering)
30 (Runaway)

Unfortunately, we don't have a table of categoies so I created a formula, @category, that assigns each statute to a given category.

Objective:
Within each category, provide totals by age, sex and race demographics

Rules:
Each arrestee can only be counted once based on the most severe crime (or category)

Approach:
I grouped by arrestID and inserted a minimum on category. This gave me the right category to make my individual count by arrestee.

Problem:
How do I group the individual counts into each category?

Age Race Ethn Sex
< 18 18 19 20.... Wh Bl Ind As Hisp M F
Murder 0 0 0 1.... 1 1 0 0 0 2 0
Rape 0 0 1 0.... 1 0 0 0 0 1 0
Robbery 0 0 0 0.... 0 1 0 0 0 1 0

I tried grouping by Category but that caused an individual to be counted with every charge.

GH1- Category
GH2- ArrestID
Det- Charges

For example, if a person was charged with robbery and aggravated assault, then I am getting a count for both. I am getting a GH2 for the same person in both categories. In addition, the min(category) is the same as GH1. I just need to count the robbery since it is a higher classification than aggravated assault.

I deleted the category group (GH1) and went into Report -> Top N/Sort Group Expert and sorted min of category in descending sequence (yes, descending because when I did it in ascending sequence, the report GH1 came out in descending sequence. Can someone explain the logic in that?).

Anyway, the arrests are now sorted in the proper crime category sequence.

This is where I am stuck. What do I do next to get the individual arrest info into a higher summary? I was toying with the idea of using the charge Code table as my main report with the arrests as a subreport and linking the categories. Wouldn't that mean running the same arrests 30 times to get the data passed?

Is there something I can do in SQL expression? I am a total newbie in this area.

Any suggestions would be appreciated.

 

[jdemmi]

Why not use running totals? You can reset the count per GROUP

Use running total, type of summary = count, evaluate = use a formula, reset on change of group.

Would that work for you?

 

[kskid]

The problem is the GH1 is ArrestID and I really need it grouped by @categories. As I mentioned previously, grouping by @categories and then by arrestID will foul up my counts even with running totals. That is, I will have the same person in more than one category depending on the number of charges.

 

[kskid]

Well, I used the subreport method

The main report contains the Charge_code table that is grouped by category. The subreport contains the arrest, arrest charges and charge code table and used the same category formula.

Although the subreport is grouped by arrest ID, I inserted a minimum on @category to get the highest classification.


I inserted running totals for the sex, age group, race and ethnicity (46 of them to be exact) and placed them in the detail and report footer of the subreport. All sections except the report footer were suppressed.

I inserted the subreport in the group header of the main report and passed the group category name. In the subreport I used the group select formula to match the minimum category with the mainreport group category.