Display records in subreport with no record in main report, per group 1

[patti2]

I have searched previous posts and FAQs, but have not been able to find this problem, so I apologize if there is one out there that I've missed.

I'm using CR 8.5, DB2 db via ODBC.

We produce customer statements that combine multiple accounts on each statement. I've got two tables: COMBLEAD for the primary account and customer information for that account, and COMBSEC for the secondary accounts on the statement with their customer information. These two tables are linked by {lead_acct}.

I need to identify customers that are listed in the secondary account table but not in the primary table, for each lead acct.

Example:

COMBLEAD
lead_acct cust_name
123 John
123 Mary
123 Joe

COMBSEC
lead_acct sec_acct cust_name
123 456 Susan
123 456 Mary

I'd like my results to show all customers in COMBLEAD, and only Susan from COMBSEC, since she has no relationship to the lead account.

I've tried a couple of different ways, and right now I've got a main report displaying the lead account and customer information, grouped by {lead_acct}; and a subreport displaying the secondary account information, also grouped by {lead_acct}.

Is there any way to only display the customers in the subreport that have no record in the main report, evaluated for each group? Maybe a subreport isn't the way to go?

Thanks in advance for any help.

 

[lbass]

In the main report, create formulas like this:

//{@reset} to be placed in the group header for lead_acct:
whileprintingrecords;
shared stringvar x;
if not inrepeatedgroupheader then
x := "";

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
shared stringvar x := x + {comblead.custname}+",";

In the subreport (which should be linked on lead_acct and placed in the group footer, go into the section expert->details->suppress->x+2 and enter:

whileprintingrecords;
shared stringvar x;
{combsec.custname} in x

-LB

 

[patti2]

Thanks, lbass - worked like a charm! Except I used cust_id in my variable because it was shorter than cust_name. (Cust_id is a 7-char alpha-numeric field.) Because the length of the variable increases by 8 characters for each record per group, I had to eliminate (through group selection) those that were > 31 records per group because of the 254-char limitation on string fields. We will deal with these manually.

Now I have another question: Is there a way, either through record selection or suppression, to not display records in the main report if all the records in the subreport have been suppressed? I'd like the report to just show the exceptions.

 

[lbass]

IDs are preferable, but I didn't you had one to work with.

The issue is you need the main report to process first in order to create the variable that suppresses the subreport records. I guess you could add a second subreport below the first one. Let's say sub1 is in GF1. Save the main report and insert a newly named version of it as a sub in GF2. Link it on the group field. Then in sub1 create a formula and place it in the detail section:

whileprintingrecords;
shared stringvar x;
shared numbervar cnt;
if not({combsec.custname} in x) then
cnt := cnt + 1;

In the section expert for sub2, add this formula to any displayed sections (GH, details, GF):

whileprintingrecords;
shared numbervar cnt;
cnt = 0

In the group header of the main report, add this formula:

whileprintingrecords;
shared numbervar cnt;
if not inrepeatedgroupheader then
cnt := 0;

Then suppress the main report sections. If you want the two subs to appear side by side, format GF1 where sub1 is located to "underlay following sections".

Note also that you could collect your IDs in two or more arrays. Please see thread767-1610024 for a method of doing that.

-LB