Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Outer Join Subreport Link Post Reply Post New Topic
Author Message
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Topic: Outer Join Subreport Link
     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!


Edited by marz - 10 Feb 2011 at 8:05pm
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet 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.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Posted: 11 Feb 2011 at 4:15am
left outer join on subA.field1 = subB.field1
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet 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?

Edited by marz - 11 Feb 2011 at 6:36am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 11 Feb 2011 at 6:40am
this will still exclude rows where there was a joined subB.Field2 that is <> ?pm-mainB.Field2
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet Posted: 11 Feb 2011 at 6:57am
Yeah I just look at the SQL and it wouldn't work that way.
IP IP Logged
marz
Newbie
Newbie


Joined: 10 Feb 2011
Online Status: Offline
Posts: 12
Quote marz Replybullet 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 ...

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"

AND "CONTRACT_RATES_slx"."ACCOUNTID"={?Pm-ACCOUNT.ACCOUNTID}
 
WHERE "CUSTOMERPREPCHARGE"."STARTDATE"<CONVERT(DATETIME, '2011-02-12 00:00:00', 120)

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



Edited by marz - 11 Feb 2011 at 7:04am
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.