Suppress entire record (with several subreports) 1

[jcaserti]

I am a novice using CR10/Oracle 9i database.

My main report pulls from 3 oracle tables, pers/address/timeframe. I have several (13) subreports. The subreports pull from one table, user_defined and are linked to the main report using id_pers and id_tmfr. The department using this report enters all descriptive information into the user_defined tables so there are many rows of data for each person.

I want to suppress an entire record if a value = "Scholarship" in the user_defined table. If I suppress this in the select expert, only the one row will be suppressed, not the entire record.

Thank you for any assistance you can provide.

JC

 

[lbass]

I think you are using "record" to refer to a "person's record or account." Ordinarily, one would use "record" to mean one detail row. If you are trying to eliminate a person and all associated records from display when "scholarship" appears in one or more of those records, you can do the following. First group on {table.personID} and then create a formula {@Schol}:

if {table.value} = "Scholarship" then 1 else 0

Then go to report->edit selection formula->GROUP and enter:

sum({@Schol},{table.personID}) = 0

-LB

 

[kskid]

If you want to filter the records at record selection time, then add this to the end of Database->Show SQL Query

AND NOT EXISTS
(
 SELECT *
   FROM "USER_DEFINED" UD
  WHERE UD."VALUE" = 'Scholarship'
    AND UD."ID_PERS" = USER_DEFINED."ID_PERS"
)

Cheers,
-LW

 

[jcaserti]

Thank you for your suggestions. But I am still having a problem.

When I enter the formula as suggested, I do not get any "Scholarship" records. However, I get multiple rows in each of the 3 detail sections of my reprot. These detail sections pertain to the 3 possible sessions a person may be enrolled in.

My report is as follows:

PH: PERS.NAME, , PERS.ID_PERS, ADDR.ADDRESS, TMFR.PROGRAM, TMFR.ID_TMFR
Group by PERS.ID_PERS
DETAIL A: Subreport 1a, Subreport 1b and subreport lc
Each subreport has 2 fields. USER_DEFINED.COLUMN_NAME USER_DEFINED.COLUMN_VALUE with selection based on the Session1 COLUMN_NAME, linking to main report using ID.PERS and ID.TMFR.

DETAIL B: Same as above, selction based on Session2

DETAIL C: Same as above, selction based on Session3

I was hoping I wouldn't have to re-create the entire report which I would do using the code much like LW suggests.


JC

 

[lbass]

It's unclear what you think should be displayed. If you only want the subreports to run once per person, then place them in a {pers.id_pers} group header or footer, instead of in the details section.

-LB

 

[jcaserti]

YES!!! Thank you. I moved my subreports from 3 detail sections to 3 group headers, used the formulas you provided and I got just what I wanted.


THANK YOU VERY MUCH

JC