Excluding Records

[Juliedavidson]

I have to do some report where we want to show the case names of cases where there were certain injuries. The problem is there can be multipe injuries and multipe claimants within a case and I do not want to see or count the case if there are injuries other than the one I am interested in. Any help would be appreciated.

 

[synapsevampire]

Use the record selection criteria to define which injuries thatyou're interested in, as in:

{table.injuries} in ["Hangnail, "Hungover"]

This will limit the rows to only those fields.

If you want a better explanation, try posting example data and expected output.

-k

 

[Juliedavidson]

My problem is that there is one case name and multiple claimants and multipe injuries per case. I want to exclude the entire case if there is even one injury that is excluded. I tried to to an if-then-else but it will include all claimants that does not have the excluded injury. Same with using the record selection critera.

Example:

Caseinfo.CaseName Claimant.ClaimantName Injury.Injurytype
Boyer, Allen Boyer, Allen Stroke
Heart Failure
Dennis, Clint Hypertension

Davidson, Fred Davidson, Fred Heart Failure
Stroke
Hypertension

Horn, Eddie Horn, Eddie Stroke
Horn, Jennie Unknown

I want to exclude any case where "Unknown" is an injury, even if there is a Stroke.

 

[lyanch]

Use a subselect in your where clause.

Caseinfo.Id not in (select c.id from Caseinfo c, injuries i where injuries.InjuryType = 'Unknown' and c.id = Caseinfo.id)

You will need to fix the syntax for your db and this has to manually be added to the SQL statement through database->show SQL.

Lisa

 

[lbass]

Or you could group on {Caseinfo.CaseName} and then create a formula {@unknown}:

if {Injury.InjuryType} = "Unknown" then 1 else 0

Place this in your details section.

If your goal is just to exclude cases with any injury identified as "unknown", then place this formula in your group select statement:

Sum({@unknown},{Caseinfo.CaseName}) = 0

You will then need to use running totals for calculations with the remaining data, since the excluded cases would still enter into the more usual summaries, e.g., counts, etc.

If your goal is to both exlude cases with "unknown" as an injury and to select cases with a specific injury, try this formula {@injury} in the details section:

if {Injury.InjuryType} = "Unknown" then 1 else
if {Injury.InjuryType} = "Stroke" then 1000 else 0

Then in your group select, use:

remainder(sum({@injury},{Caseinfo.Casename}),1000)= 0

This would select casenames which have "stroke" but not "unknown." You could also create a parameter {?injury} and plug that into your formula instead of "stroke" so you could check the data for selected injuries of interest.

-LB