Is it possible to...

[SarahS81]

Force records in the main report to appear in the subreport or vice versa? ie can I somehow use a left outer join between the two?..

or does anyone have a suggestion of ways to work around this please?

 

[Katy44]

You can link a field in the subreport to the main report - is this what you mean?

 

[synapsevampire]

Perhaps if you share requirements and environment someone can help.

You can share variables between main reports and subreports, but it's generally more efficient to use a subquery.

How you might do so is dependent upon the version of Crystal and the database.

Here's an example using the Northwind example database on SQL Server, where the main report uses the Customers table only and this SQL Expression is counting the orders:

(SELECT count(Orders."CustomerID")
FROM
"Northwind"."dbo"."Orders" Orders
where Customers."CustomerID" = Orders."CustomerID" )

Note that if you check the Database->Show SQL Query you'll see that the query is nested, and very efficient.

BTW, nothing prevents you from using the table in the main report and doing a LO join there.

-k

 

[SarahS81]

Sorry - I have not written an SQL Statement in Crystal before. If I was writing the same report in Microsoft Access I would create one query and in a second query I would link it one of my database tables with a left outer join (showing all records from the database table, not the nested query).

I was hoping this could be achieved with subreports although looks like I have two choices:

1. Write a SQL statement nesting the first query
2. Link my tables with a left outer join in Crystal and use a criteria similar to (isnull{table1.field1}) or {table1.field1}="A" to place criteria on the table on the right hand side of my join..

Is this correct? Any suggestions for some advanced reading on data access information such as this (for CR9) please?

Thanks for your help guys - much appreciated!

 

[synapsevampire]

The latter may work for you, and will net reasonable SQL if it does, but still we're guessing as to what you're trying to accomplish without howing what's in the main and subreport.

You can link a main report to a subreport, and show the subreport results, which acts like a LO join, but it's inefficient.

You can join the tables using a LO join, as you described, but make sure that you're getting the proper results.

Write the subreport in Crystal. Copy and paste the SQL into a SQL Expression in the main report (wrapped by parens), and add in the where {subreporttable.field} = {mainreport.field} to the SQL Expression.

The latter would likely be used for aggregates though, not for returning multiple rows.

-k