Include all Detail Records in Group Conditionally 1

[Rooski]

I am running Crystal V10 using an Oracle database.

What I am trying to do is include all detail records in a group based on the value of a field that may be present in only one of the detail records. Think of this as the opposite of suppression. I found many threads regarding how to suppress records through Group Selection, but could not find anything relating to the opposite.

Below is a table of sample data representing the situation. I have linked an Incident table to a Persons Involved table built on a one-to-many relationship using a left outer join.

Incident # Name Involvement Offense Code
1 John Suspect
1 Sara Suspect
1 Mark Suspect 120
1 Dirk Witness

2 Tim Suspect
2 Ben Victim 130

3 Jerry Suspect
3 Sandy Victim

I would like to select all detail records in an incident based on the presence of an offense code in any one of the records. In the scenario above, a report, for example, should list all information found under incidents 1 and 2. Incident 3 and its associated detail records, however, should not be included at all as there is no offense code present for any of the incident's detail records.

I have tried several combinations of record selection and group selection to no avail. The problem essentially, I believe, is that I need to retrieve detail records after they have been by-passed based on them not having an offense code. Any help would be appreciated.

Thanks, Rooski

 

[lbass]

Group selection is the answer. Insert a group on incident# and then create a formula {@hascode}like this (assuming the offense code is a string):

if isnull({table.offensecode}) or
trim({table.offensecode}) = "" then
0 else 1

If it is a number, use:

if isnull({table.offensecode}) or
{table.offensecode} = 0 then
0 else 1

Then use a group selection formula like this:

sum({@hascode},{table.incident#}) > 0

-LB

 

[Rooski]

Thanks for the reply. Your suggestion worked quite well. In fact, before I had submitted my question, I had included your very same concept in my report except my group selection formula lacked {table.incident#}.

Having gotten this far, what I am really after is summarizing these incidents in the following manner:

In addition to an Involvement code, each person has a Race code (e.g. Black, White, Hispanic, Other). Using the selection criteria you provided, I need to generate the total number of incidents in which Blacks are involved, Whites are involved, Hispanics are involved, and Others are involved but only for Involvement types of Suspect or Arrested. Incidents with multiple suspects or arrestees of different races should be handled as follows:

3 whites and 1 black would result in the incident begin added to the white bucket once and the black bucket once. In other words, no matter how many times a race appears in the incident, it is only counted once for that incident.

Is this even possible in the realm of Crystal? Maybe with a subreport? Again, any help would be greatly appreciated. In the meantime, I will continue to experiment.

Thanks, Rooski

 

[lbass]

You can just add running totals, one per race. Set it up so that it does a distinctcount of {table.incident#}, evaluate using a formula:

{table.race} = 'Black'

Reset never.

Repeat for other race codes. Place the running totals in the report footer. They will automatically count only the displayed incidents. This will work as long as your are ONLY using group selection and are not suppressing any records.

-LB

 

[Rooski]

Hi LB,

Thank you for the help. You were right, as long as I didn't supress any records in the running total formulas, counts were produced. As soon as I changed the formulas to count only records in the groups where the person was a SUSPECT or an ARRESTEE, then the counts became zeroes. Unfortunately, as mentioned in an earlier post, those are the counts I need. And I now need them in two different ways. a) Same as before, no matter how many times a race is represented for a SUSPECT or ARRESTEE status in the group, it would result in the incident begin added to its respective race counter only once. b) New counting method would result in the race counters being augmented by the number of times each race is represented for SUSPECT and ARRESTEE in group.

For example, in the expanded sample data below, only incidents 1 and 2 would be included in the count since they both have an offense code. Incident 3 would not be included in the count at all since it lacks an offense code. Under counting method a, the black counter would equal 1 (for SUSPECT Tim in incident 2) and the white counter would equal 1 (once for collective SUSPECTS John, Sara, Mark in incident 1). Under counting method b, the black counter would equal 1 (SUSPECT Tim in incident 2) and the white counter would equal 3 (once each for SUSPECTS John, Sara, Mark in incident 1).

Incident # Name Involvement Race Offense Code
1 John Suspect White
1 Sara Suspect White
1 Mark Suspect White 120
1 Dirk Witness Black

2 Tim Suspect Black
2 Ben Victim White 130

3 Jerry Suspect White
3 Sandy Victim White


Sorry about piling on and I will understand if you don't reply.

Thanks, Rooski

 

[lbass]

Can you show a sample of what your report should look like when complete? Is it just the summary information? There might be a better approach.

-LB

 

[Rooski]

To simplify things, I produced a sample report for just white and black. Eventually, I would like to produce the report grouped by patrol division of which there are six.


Incidents % of Total Incidents
Total 1000
Black-Involved Incidents 450 45%
White-Involved Incidents 500 50%


Again, the counters should only be augmented when the involved person in an incident is a SUSPECT or an ARRESTEE. This would not be that difficult if an offense code was attached to every person in the person table. But it is not. If an offense code is present, it may show up only once and it could be attached to any person in the incident and their involvement status could range from Suspect, Witness, Arrestee, Victim, Field Contact, Other, Reporting Party. Again, I'm just after Suspects and Arrestees for any incident in which an offense code is present.

Let me ask something else as a potential way of getting around this problem. The master incident record has the highest-level offense code associated with the incident in the form of a character string, thus it is present in every row of the linked tables. I know I could select records based on this value, but there are 250 of these very specific descriptions of which I need 200. I would have to add each of the needed 200 offense descriptions to an In Range command by typing them into the record selection formula.

But maybe not. These offense descriptions and their three-character offense codes (like the 120 in the sample data) also exist in a stand-alone code table. Groups of them them are commonly associated with one three-character code. For instance, the following individual offense descriptions are all associated with code 120: Robbery Armed, Robbery Bank, Robbery Business, Robbery Purse Snatching, Robbery Residence, Robbery Strong-Arm.

Is it possible in Crystal Record Selection to either look up these values in their native table during the selection process or somehow dynamically load these values into the In Range statement? This would give me the most flexibility as I could then produce reports broken down by specific offense types by race.

Sorry for being long-winded (again).

Thanks, Rooski

 

[lbass]

What exactly did you mean by this:

As soon as I changed the formulas to count only records in the groups where the person was a SUSPECT or an ARRESTEE, then the counts became zeroes.

Did you change the evaluation formula in the running total set up? That still should have worked:

{table.racecode} = "White" and
{table.involvement in ["Suspect", "Arrestee"]

You would use a distinctcount for a) and a count for b). if you want the analysis per group, you would reset the running totals on change of group. Otherwise, reset never. Running totals must be in footer sections.

There is a simpler approach to your report which would eliminate incidents with no offense code. Assuming the offense code is associated with the PersonsInvolved table and that there is a field in this same table used to link to the Incidents table, create a SQL expression {%maxcode}like this:

(
select max("Offense Code")
from PersonsInvolved A
where A."Incident #" = PersonsInvolved."Incident #"
)

Use this in your record selection formula:

not isnull({%maxcode})

Then you only have the groups you want to work with. Then create a formula like this {@summaryformula}:

if {personsinvolved.involvement} in ["Suspect","Arrestee"] then
{personsinvolved.incident#} else
tonumber({@null}) //remove tonumber if uniqueID is a string

...where {@null} is a new formula that you open and close without entering anything.

Insert a crosstab that uses race as the row and patrol division as the column field. Do a distinctcount of {@summaryformula} and/or a count. If you like this approach, I will show you how to add in the percentage.

-LB

 

[Rooski]

Hi LB,

I apologize for not getting back sooner, but I had a higher priority project cross my desk.

First, to answer your question about:

As soon as I changed the formulas to count only records in the groups where the person was a SUSPECT or an ARRESTEE, then the counts became zeroes.

That was my error. I had changed the evaluation formula in the running totals, but did so incorrectly. They are now working fine and I can generate counts using both methods a) and b).

Secondly, I have just tried setting up an SQL expression (which I have never done before) based on your model and received an error. Here is the expression:

(
select max("INPER6_VIEW"."Related_Offense")
from PersonsInvolved A
where A."INMAST_VIEW"."Report_No" = PersonsInvolved."INPER6_VIEW"."Report_No"
)

And here is the error:

Error in SQL Expression:
Query engine error. Table or view does not exist.

No matter how I tried manipulating the statement, I never got past this compile error. Obviously, it can't find a table. The INMAST table is linked to the INPER table.

Thanks for any help.

Rooski

 

[lbass]

(
select max("Related_Offense")
from "INPER6_VIEW" A
where A."Report_No" = "INPER6_VIEW"."Report_No"
)

I meant for you to replace "PersonsInvolved" with the actual table name. If that is "INPER6_VIEW" then you should be able to use the above exactly as is. You don't need to reference the other table for this purpose.

-LB

 

[Rooski]

Hi LB,

Once again, I am running into the same compile error message as yesterday. I first added the code exactly as you indicated:

(
select max("Related_Offense")
from "INPER6_VIEW" A
where A."Report_No" = "INPER6_VIEW"."Report_No"
)

I then changed max("Related_Offense") to max("INPER6_VIEW"."Related_Offense") because that is what is loaded into the formula when you click on the field name "Related_Offense" under the INPER6_VIEW field tree. Neither that, nor any of the other permutations I tried, which involved everything from adding and deleting quote marks to inserting and removing spaces, worked.

I then decided to look at the data source information available under "Set Datasource Location..." for these two tables (strictly speaking, they are views) the only tables in the report. Based on what I found there, it would appear that the table (view) names used in the sql expression are correct:

INMAST_VIEW
Properties
Table Name: INMAST_VIEW
Table Type: Views
Owner: TIBURON
File Name:
Overridden Qualified Table Name:

INPER6_VIEW
Properties
Table Name: INPER6_VIEW
Table Type: Views
Owner: TIBURON
File Name:
Overridden Qualified Table Name:


Thanks, Rooski

 

[lbass]

No, you shouldn't specify the table in the max()--unless you are using CR2008, which you aren't. Try adding the owner:

(
select max("Related_Offense")
from "TIBURON"."INPER6_VIEW" A
where A."Report_No" = "TIBURON"."INPER6_VIEW"."Report_No"
)

I just tested this with an Oracle view and got the error without the owner added, but it worked once I added it. I don't get this error with a regular table though.

-LB

 

[Rooski]

Hi LB,

Adding the owner to the sql expression made all the difference. I was then able to make everything else work using your other formulas and produce a crosstab. Once I got that far, I knew how to make the percentage appear in the crosstab by simply adding the {@summaryformula} to the crosstab again and making the count show as a percentage.

Thanks for all your help and especially patience. The Group logic alone warrants as star much less everything else you provided.

Lastly, just curious. Why did the sql expression have to employ the use of alias tables?

Thanks, Rooski

 

[lbass]

It's just a way of creating a faux group within the expression.

-LB