General question Concerning SubReports

[MegOBytes]

CR XI
JDE User Business Views

I a trying to integrate more Crystal into our reporting environment and have run accross a situation where i need to link 2 tables. I have read that Crystals sub report feature works well when there is a many to many join between tables. here is my situation:

Main report - A/P detail file: - Doc #, Doc Type, Line #, Amount
Sub report - G/L detail file: - Doc #, Doc Type, Line #. Amount

A. In some cases there is a 1 to 1 relationship between the tables.

AP: Doc# 123, line 1
GL: Doc# 123, line 1

B. Some times its many to 1 (1 AP reord to multiple GL reords).

AP: Doc# 123, line 1
GL: Doc# 123, Line 1
line 2
line 3

C. and some times there are multiple AP records matched to multiple GL records. not sure if this is considered many to many or one to one.

AP: Doc# 123, Line 1
line 2
GL: Doc# 123, Line 1
Line 2

When i tried creating a subreport i have problems with the many to many side (example C). I get all of the subreport detail for every line in the main report (the AP record).

Do i need separate subreports for each join type? Is sub reporting the proper solution here?

RJ Piekos

 

[Turkbear]

Hi,
If you link the 2 tables by the DOC# and create a group on that field ( from the leftmost table and use a Left-Outer join, especially if any AP DOC#s do not appear in GL but you want lines from the AP table to show anyway), you can place the Lines from both tables in the detail section ( or even have 2 details , 1 for AP and 1 for GL derived lines)...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[MegOBytes]

TurkBear:
Thanks for the info. I have tried that scenario but it will fail when i encounter situation 'C' listed in my initial post. in that situation i get 4 lines (should only be 2):
apdoc123 (line 1) = gldoc123 for line 1
apdoc123 (line 1) = gldoc123 for line 2
apdoc123 (line 2) = gldoc123 for line 1
apdoc123 (line 2) = gldoc123 for line 2

since i am only matching on doc number i get the duplicates.

If i add line number to the match criteria i will not pick up any extra records on the GL side (scenario 'B')

Meg




Thanks

Meg