Display All rows in outerjoin

[sammybee77]

Hi All,

I'm having a few problems I have a report that is outer joined, I want to do a select on the table which is outer joined but still display all the matches. E.g.

Table1 has an id that = 12. Table2 has several rows that have a link on the id 12 and the results show a list of countries e.g

id country
12 england
12 Germany
12 France

I'm trying to select where Table 2 = England, but still show all the other countries. When I do this the result won't show all just England. Any ideas?

Many thanks

Sam

 

[kenhamady]

If you put in a rule that says you only want England - you are only going to get England. If you want all countries to show, why are you filtering on England? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[kenhamady]

Or do you mean where one row of the group is England, show the whole group? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[sammybee77]

I want to show all the results if one row = England

 

[kenhamady]

Group your records by ID
Create a formula that is:
if Country = "England" than 1 else 0

Then use a GROUP selection formula of :

Sum ( {@formula} , {Country} ) > 0


Use running totals for your Grand Totals, or they will be inflated. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[sammybee77]

Hi Ken,

I don't want to sum the information but display the country names, also, the user picks the country they want to display and by doing so they want to be able to see what other countries are held against the record. Any ideas?

Many thanks

Sam

 

[lalato]

I should have said:

Sum ( {@formula} , {ID} ) > 0

The "England" could be a parameter field value.

 

[kenhamady]

To clarify - the sum is to see if any of the records in the ID group are "England". That tells CR which groups to include. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com