G'day,
Nice to meet you all.

Background
I am working on a report in Crystal Reports XI (w/all update packs) on Win 7 64 bit.

The report uses 1 group based on a members SSN. The group header has text block labels for the columns below.

The detail section has the data in rows. Some users have multiple (Health) coverage types so the rows can repeat.

The group footer holds some text and data and below that a sub-report that is built identically as above but with the members dependents and their coverage. Members can have no dependents, dependents with no coverage or dependents with coverage.

Data comes from my own SQL commands in both the report and sub-report. Data is based on an SSN parameter & a report date parameter. I have 3 SQL commands under the report, one gets a dependent count, one gets a dependent ID and one is all the data on the member. All 3 use the same parameters and all 3 SQL are made up of 3 joined tables. (I have used different combinations of joins but the result is always the same as mentioned below). The sub-report is one SQL with the same parameters and same 3 joined tables but just dependent data.

The sub-report/group footer section should be suppressed when there are no dependents or dependents with no coverage.

Issue
The report works fine with dependents, no dependents/no coverage shows an empty "Table". So I check the suppress box and add a formula, I have tried both:

If DistinctCount ({depCount.DepCount}) <> 0 Then
True
Else
If IsNull({depID.DEPENDENT_ID}) Then
True
Else
False;

originally, or figuring in both cases there is no Dependent ID so I changed it to just:

If IsNull({depID.DEPENDENT_ID})

Both of these work and suppress the section as needed.

However both cause a duplication of data for the member.

Example:
My test member when not suppressed has one row of member data & two blocks of dependent data.

With suppression added, his one row of data becomes 3 rows, apparently the original row plus the original row repeated for each dependent (member plus 2 dependents).

In a worse case the member has 3 rows of data and 5 dependents which becomes a total of 18 rows for the member (member 3 rows plus 3 rows per 5 dependents).

Once again, remove the suppression formula and the member has the correct number of correct rows.

Both with & without suppression, the dependent data always shows correctly. I cannot figure out why suppression would cause a duplication of member data and why it duplicates reflecting the number of dependents.

Any ideas?

Good Call!

depCount is being used, but when I dragged depID over the rows multiplied.

So if I understand you right, I need to change the joins for that command.

Will do.

Hmm, OK, that explains the other issue of why Toad shows the data correctly and CR does not.I wrote the queries in Toad and it works fine, then duplicated in CR, so I never really thought of the Joins as the issue.

Got it!