Displaying values in sub-report B hat are not in sub-report A

[tekniks]

Hi,

I have 2 sub-reports A & B. B is placed below A(CR 8.5).
Sub-report A returns Material ID's from the database.
I have to display only those Material ID's in sub-report B which are not displayed in sub-report A.
For example:
Database Returns following Mat ID's for sub-report A
18052717
19689514
19713318
20178515
21483613
21965928
22346415
24467619
Database Returns following Mat ID's for sub-report B
21483613
21965928
22346415
25348971
21936547

The sub-report B should only display values 25348971,
21936547.
Can anybody tell an easy solution for this or does it has to be implemented thro' arrays and formulas and even if arrays or formulas then how?

Thanks for your help.

TEK

 

[synapsevampire]

Please post the record selection formulas for the subreports, where the subreports are in the main report, and how they're linked to the main report.

Showing the resultant data which is wrong doesn't help much.

The overall solution is to use the reverse criteria for subreport B, whcih I'd try to work out if I knew what it was.

Also keep in mind that subreports are sloooow, so try to avoid them.

-k

 

[tekniks]

Sub report A:
Is in GH1a - grouped on : Cl_rqst_btch_link_st.batch_id
Linked to Main Report on : Cl_rqst_btch_link_st.batch_id
and the record selection is --

{MM_GENEALOGY_LG.BATCH_ID} = {?Pm-CL_RQST_BTCH_LINK_ST.BATCH_ID}and
{MM_MATERIAL_SP.MATERIAL_TYPE} in ['ACTIVE','EXCIPIENT','UTILITY']

and the SQL Query generated is:

SELECT DISTINCT
MM_GENEALOGY_LG."BATCH_ID", MM_GENEALOGY_LG."MATERIAL_ID",
MM_GENEALOGY_LG_3."MATERIAL_ID",
MM_MATERIAL_SP."MATERIAL_DESC", MM_MATERIAL_SP."MATERIAL_TYPE", MM_MATERIAL_SP."CAS_NUMBER"
FROM
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG,
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG_1,
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG_2,
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG_3,
"POMS"."MM_MATERIAL_SP" MM_MATERIAL_SP
WHERE
MM_GENEALOGY_LG."LOT_ID" = MM_GENEALOGY_LG_1."BATCH_ID" AND
MM_GENEALOGY_LG_1."LOT_ID" = MM_GENEALOGY_LG_2."BATCH_ID" AND
MM_GENEALOGY_LG_2."LOT_ID" = MM_GENEALOGY_LG_3."BATCH_ID" AND
MM_GENEALOGY_LG_3."MATERIAL_ID" = MM_MATERIAL_SP."MATERIAL_ID" AND
MM_GENEALOGY_LG."BATCH_ID" = 'H05553' AND
(MM_MATERIAL_SP."MATERIAL_TYPE" = 'UTILITY' OR
MM_MATERIAL_SP."MATERIAL_TYPE" = 'EXCIPIENT' OR
MM_MATERIAL_SP."MATERIAL_TYPE" = 'ACTIVE')
ORDER BY
MM_GENEALOGY_LG_3."MATERIAL_ID" ASC,
MM_GENEALOGY_LG."MATERIAL_ID" ASC


I have 3 alias of the table MM_GENEALOGY_LG in this report.

Sub report B:
Is in GH1b - grouped on : Cl_rqst_btch_link_st.batch_id
Linked to Main Report on : Cl_rqst_btch_link_st.batch_id
and the record selection is --

{MM_GENEALOGY_LG.BATCH_ID} = {?Pm-CL_RQST_BTCH_LINK_ST.BATCH_ID}and
{MM_MATERIAL_SP.MATERIAL_TYPE} in ['ACTIVE','EXCIPIENT','UTILITY']

and the SQL Query generated is:

SELECT DISTINCT
MM_GENEALOGY_LG."BATCH_ID", MM_GENEALOGY_LG."MATERIAL_ID",
MM_GENEALOGY_LG_2."MATERIAL_ID",
MM_MATERIAL_SP."MATERIAL_DESC", MM_MATERIAL_SP."MATERIAL_TYPE", MM_MATERIAL_SP."CAS_NUMBER"
FROM
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG,
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG_1,
"POMS"."MM_GENEALOGY_LG" MM_GENEALOGY_LG_2,
"POMS"."MM_MATERIAL_SP" MM_MATERIAL_SP
WHERE
MM_GENEALOGY_LG."LOT_ID" = MM_GENEALOGY_LG_1."BATCH_ID" AND
MM_GENEALOGY_LG_1."LOT_ID" = MM_GENEALOGY_LG_2."BATCH_ID" AND
MM_GENEALOGY_LG_2."MATERIAL_ID" = MM_MATERIAL_SP."MATERIAL_ID" AND
(MM_MATERIAL_SP."MATERIAL_TYPE" = 'UTILITY' OR
MM_MATERIAL_SP."MATERIAL_TYPE" = 'EXCIPIENT' OR
MM_MATERIAL_SP."MATERIAL_TYPE" = 'ACTIVE') AND
MM_GENEALOGY_LG."BATCH_ID" = 'H05553'
ORDER BY
MM_GENEALOGY_LG_2."MATERIAL_ID" ASC,
MM_GENEALOGY_LG."MATERIAL_ID" ASC


I have 2 alias of the table MM_GENEALOGY_LG in this report.