Some records in sub-report not showing up in main report. 1

[prevost999]

I have 1 main report linked with 1 sub-report, with a field called Item Number.

Scenario:
For 1 group in the main report, there are 10 records, each
with a unique item number.
In the subreport there are 12 records, each with unique item numbers, in the same group.

The problem is, when I link the sub-report to the main
report by item number, it only results in 10 records, and
misses the two records in the subreport that have no matching link(item #) in the main report. ***I want to include these records in the main report.*** (It's like I want to create a Left Outer Join between the main report and subreport, via the subreport link.) Is there anyway to accomplish this?

 

[kenhamady]

First, are you sure that you need a subreport? You could link the tables directly and then use the outer join.

Also, if you reverse the reports, then you will get all 12 records in the main report, and the 10 matches from the sub. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[prevost999]

kenhamady: I can't link the tables, that's why I'm doing a
subreport. The records contain the same info,
but are of a different nature. And I can't
reverse the roles of the
reports, because the same thing will happen
the other way around. If this is not possible
through subreports, I am
thinking of combining the records into one
table. Or would a stored procedure work maybe?

 

[kenhamady]

I don't see a way to use subreports to simulate outer joins.

If you can combine the records into one table, I think that you will simplify your reporting. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[prevost999]

kenhamady:
I got it to work. Ironically, I used your advice from
a different thread in my quest for the answer. I used
a UNION clause to combine 2 queries, then embedded the
entire SQL statement in a stored procdure. Not only did
it work, it made the report run much faster. Thank you
for your help!!!