Question: Outer Joins in MS SQL-Server when using Sub-Reports of Crystal Reports

Question: Outer Joins in MS SQL-Server when using Sub-Reports of Crystal Reports

Post by Marcus See » Wed, 20 Mar 2002 00:49:11



This question mainly concerns Crystal Reports 8.5 but the group is no
longer archived by Google, and hey, I am using a MS SQL-Server
database, so it's not totally off-topic on this ng either, is it?

OK, here's the problem. I am using Crystal Reports to design a report
that contains sub-reports. Lets say the sub-report is going to show a
list of accounts. These accounts may contain a set of arbitrary data,
but then again, they may not; hence the outer join. Lets say the
arbitrary data contains a value. A value is nothing without a
currency, and therefore the arbitrary data contains a currency ID. The
SWIFT code however is to be found in yet another table. The following
statement would be a valid SQL statement that gives me the desired
result:

SELECT ACCOUNT.NAME, ARBITRARY_DATA.VALUE, CURRENCY.SWIFT
FROM ACCOUNT LEFT OUTER JOIN ARBITRARY_DATA ON
 ACCOUNT.ACCOUNT_ID = ARBITRARY_DATA.ACCOUNT_ID
 LEFT OUTER JOIN CURRENCY ON
 ARBITRARY_DATA.CURRENCY_ID = CURRENCY.CURRENCY_ID

This is not what I get in Crystal Reports. In CR I get

SELECT ACCOUNT.NAME, ARBITRARY_DATA.VALUE, CURRENCY.SWIFT
FROM ACCOUNT, ARBITRARY_DATA, CURRENCY
WHERE
 ACCOUNT.ACCOUNT_ID *= ARBITRARY_DATA.ACCOUNT_ID AND
 ARBITRARY_DATA.CURRENCY_ID *= CURRENCY.CURRENCY_ID

This is not a valid statement. Not would it be a valid statement if I
were to design a inner join relation between ARBITRARY_DATA and
CURRENCY.

I can manually change the query that Crystal Reports sends to the
database, but then I lose all information about the parameters, which
means I cannot manually change the query and still get the desired
result.

My question is if anyone knows a solution that does not involve
creating a godawful lot of database views (many reports will require
many views...), but rather getting CR to generate valid statements
even for my problem. Your help would be very much appreciated.

Regards,

Marcus Seeck.

 
 
 

Question: Outer Joins in MS SQL-Server when using Sub-Reports of Crystal Reports

Post by BP Margoli » Wed, 20 Mar 2002 08:32:53


Marcus,

I believe it is a known limitation of Crystal Reports that it doesn't yet
support ANSI standard JOIN syntax, however perhaps you can re-code the query
to achieve the same effect. Something like:

SELECT ACCOUNT.NAME,
       (SELECT ARBITRARY_DATA.VALUE
        FROM ARBITRARY_DATA
        WHERE ARBITRARY_DATA.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID) AS VALUE,
       (SELECT CURRENCY.SWIFT
        FROM CURRENCY, ARBITRARY_DATA
        WHERE CURRENCY.CURRENCY_ID = ARBITRARY_DATA.CURRENCY_ID
        AND   ARBITRARY_DATA.ACCOUNT_ID = ACCOUNT.ACCOUNT_ID) AS SWIFT
FROM ACCOUNT

Not being particularly knowledgeable in Crystal Reports, I don't know if it
will accept this reformulation of your query, but it's worth a shot   :-)

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> This question mainly concerns Crystal Reports 8.5 but the group is no
> longer archived by Google, and hey, I am using a MS SQL-Server
> database, so it's not totally off-topic on this ng either, is it?

> OK, here's the problem. I am using Crystal Reports to design a report
> that contains sub-reports. Lets say the sub-report is going to show a
> list of accounts. These accounts may contain a set of arbitrary data,
> but then again, they may not; hence the outer join. Lets say the
> arbitrary data contains a value. A value is nothing without a
> currency, and therefore the arbitrary data contains a currency ID. The
> SWIFT code however is to be found in yet another table. The following
> statement would be a valid SQL statement that gives me the desired
> result:

> SELECT ACCOUNT.NAME, ARBITRARY_DATA.VALUE, CURRENCY.SWIFT
> FROM ACCOUNT LEFT OUTER JOIN ARBITRARY_DATA ON
>  ACCOUNT.ACCOUNT_ID = ARBITRARY_DATA.ACCOUNT_ID
>  LEFT OUTER JOIN CURRENCY ON
>  ARBITRARY_DATA.CURRENCY_ID = CURRENCY.CURRENCY_ID

> This is not what I get in Crystal Reports. In CR I get

> SELECT ACCOUNT.NAME, ARBITRARY_DATA.VALUE, CURRENCY.SWIFT
> FROM ACCOUNT, ARBITRARY_DATA, CURRENCY
> WHERE
>  ACCOUNT.ACCOUNT_ID *= ARBITRARY_DATA.ACCOUNT_ID AND
>  ARBITRARY_DATA.CURRENCY_ID *= CURRENCY.CURRENCY_ID

> This is not a valid statement. Not would it be a valid statement if I
> were to design a inner join relation between ARBITRARY_DATA and
> CURRENCY.

> I can manually change the query that Crystal Reports sends to the
> database, but then I lose all information about the parameters, which
> means I cannot manually change the query and still get the desired
> result.

> My question is if anyone knows a solution that does not involve
> creating a godawful lot of database views (many reports will require
> many views...), but rather getting CR to generate valid statements
> even for my problem. Your help would be very much appreciated.

> Regards,

> Marcus Seeck.


 
 
 

1. Crystal Reports/SQL 2000 - Reporting from a Stored Procedure with multiple selects (for a sub-report)

I'm having a few problems, that I'm hoping someone can help out with (I
apologize for the cross-posting, but I wasn't exactly sure which newgroup
this should go into).

I've got a VB 6 application to which I'm adding a new report, for which I'm
going to use Crystal 8.

The report itself is actually a report with a sub-report. To generate the
data, I created a stored procedure (that takes a single optional parameter)
that would tabulate the data as needed, and then would return two separate
SELECT statements, the first for the main report, the second for the
sub-report.

So my problem is on several fronts:

1) How do I set up the VB code and/or the Crystal RPT file to use a Stored
Procedure as the source, including the logic for either not passing a
parameter (as a default), or for passing data that the user had selected
(coming from another form in VB)?
2) How do I set up the RPT file to differentiate between the two SELECT
statements coming from the Stored Proc?

Note: Originally my plan was to have a stored procedure (called by VB) that
would create a table of the data, and then have two views that the RPT would
reference. This way I could create the data based on whether (or not) the
user needed to have it send a parameter. Unfortunately, the reason I had to
abandon this logic, is if two users tried to access the same report at the
same time (especially if the two users passed different parameters to the
stored procedure), there would  be a conflict, since the table is a regular
table. I couldn't use a temp (#) table, as I don't believe it'd be able to
find the table going between the stored procedure that created/populated it,
and the VIEW that Crystal would call.

Any ideas?

Thanks!
-Scott

2. SQL Server-driven FAX solution

3. REports and sub-reports in Crystal Reports

4. INFO ABOUT INFORMIX C-ISAM DATABLADE

5. Crystal Reports - Main/Sub Reports

6. SQL Server Implementations

7. Delphi 2.0 and Crystal Reports Version 5 (Regarding Sub-reports)

8. Difference in speed

9. WA-SEATTLE-88419--DBA Skills-MS SQL Server-MS ACCESS-Crystal Reports-SYBASE-UNIX

10. Help: Set datasource with subreports using crystal reports

11. Using crystal Reports Web to report on SQL database requires password

12. Questions for Crystal Report Guru re: Subreports

13. MS SQL Outer Joins VS Sybase Outer Joins