Report Design: Outer Join Subreport Link

Print Page | Close Window

Outer Join Subreport Link

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12291
Printed Date: 28 Apr 2024 at 5:01pm

Topic: Outer Join Subreport Link

Posted By: marz
Subject: Outer Join Subreport Link
Date Posted: 10 Feb 2011 at 8:05pm

I have a main report table mainA linked with a subreport table subA where mainA.key1 = subA.key1

In the subreport I have a subA joined with subB on subA.field1 = subB.field1

I need to return all rows in subA but only rows in subB where subB.field2 = mainB.field2

If create an additional link between the main report and subreport mainB.field2 = subB.field 2 I no longer get all rows from subA.

I know how to accomplish it in straight SQL but how do I do it in Crystal?

Thanks!

Replies:

Posted By: lockwelle
Date Posted: 11 Feb 2011 at 3:07am

In CR, you are only allowed 1 level of subreport, ie a subreport cannot call a subreport.

Usually, I create a stored proc, and each row is a 'super' row. By this, I mean that each row contains ALL the information that would ever be displayed for that value. Then I tailor the display to only show what is needed, when/where it is supposed to.

The down side, is that sometimes the aggregate functions will overreport the value (as there are more rows in the data than on the screen), but there are ways to get around that using either/both SQL and CR.

Posted By: hilfy
Date Posted: 11 Feb 2011 at 4:02am

How are you linking between SubA and SubB? There may be a way to do this, but I need more info.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: marz
Date Posted: 11 Feb 2011 at 4:15am

left outer join on subA.field1 = subB.field1

Posted By: hilfy
Date Posted: 11 Feb 2011 at 5:04am

Ok, here's what I would do. Keep both links to the subreport. Edit the selection formula in the subreport to look something like this:

{subA.Key1} = {?pm-mainA.Key1} and

(IsNull({subB.Field2}) or

{subB.Field2} = {?pm-mainB.Field2})

Note the parentheses that I've highlighted in red - you have to check for null first and you MUST have the parens where I've put them or else this won't work correctly.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: marz
Date Posted: 11 Feb 2011 at 6:36am

Hmmm I agree with that thought but it still isn't giving me all rows in subB and I'm positive I got it right. And just to make sure I tried all the different join options between subA and subB. Any thoughts?

Posted By: DBlank
Date Posted: 11 Feb 2011 at 6:40am

this will still exclude rows where there was a joined subB.Field2 that is <> ?pm-mainB.Field2

Posted By: marz
Date Posted: 11 Feb 2011 at 6:57am

Yeah I just look at the SQL and it wouldn't work that way.

Posted By: marz
Date Posted: 11 Feb 2011 at 7:03am

Here's the SQL from the subreport. I need to change what's underlined

SELECT .....

FROM ((("sysdba"."vABS_CUSTOMER" "CUSTOMER"

INNER JOIN "sysdba"."vABS_CUSTOMERPREPCHARGE" "CUSTOMERPREPCHARGE" ON "CUSTOMER"."CUSTOMER_ID"="CUSTOMERPREPCHARGE"."CUSTOMER_ID")

INNER JOIN "sysdba"."vABS_REASONCODE_DESC" "REASONCODE_DESC" ON "CUSTOMERPREPCHARGE"."REASONCODE_ID"="REASONCODE_DESC"."REASONCODE_ID")

INNER JOIN "sysdba"."vABS_REASONCODE" "REASONCODE" ON "CUSTOMERPREPCHARGE"."REASONCODE_ID"="REASONCODE"."REASONCODE_ID")

LEFT OUTER JOIN "sysdba"."CONTRACT_RATES" "CONTRACT_RATES_slx" ON "REASONCODE"."CODE"="CONTRACT_RATES_slx"."CODE"

WHERE "CUSTOMERPREPCHARGE"."STARTDATE"<CONVERT(DATETIME, '2011-02-12 00:00:00', 120)

AND "CUSTOMER"."CUSTOMER_ID"={?Pm-CUSTOMER_DelTo.CUSTOMER_ID}

AND "CONTRACT_RATES_slx"."ACCOUNTID"={?Pm-ACCOUNT.ACCOUNTID}

If I could just move that underlined section to be part of the Join things would be resolved. Is that possible?....

SELECT ...