Crystal Reports

Getting Started. How to create a new report.

From the Welcome To dialog, select New Report.

Choose a Standard from the Report Gallery.

Under the Data tab select SQL/ODBC from the list of ‘data to report on’.

Select the ODBC data source you created earlier, from the list then press OK.

Add your dataset from the ‘Choose SQL Table’ dialog box, then click Add.

Select Done.

Select Next.

Under the Fields tab add some or all fields; then select Next.

Add a sort field, then select Preview Report.

Data from your dataset will be displayed in a default format. You can now adjust the report layout.

ODBC and Crystal Reports – Three Table Joins

Q: I’m trying to do a three table inner join in Crystal reports and receive the error “Syntax error in table list”.

A: Assuming the SQL syntax is valid you will need to add an OuterJoin key to your registry. This will allow Crystal Reports to generate the correct syntax for our ODBC driver.

1. Edit your registry. Click your Start Button, then Run. Enter “regedit” and click OK.
   Go to the following key:
2. HKEY_CURRENT_USER/Software/Seagate Software/Crystal Reports/DatabaseOptions/OuterJoin
3. No OuterJoin key. Click on DatabaseOptions. On the Menu bar, click on Edit, then New, then Key. Enter “OuterJoin” (without the quotes) for the value.
   Click on OuterJoin. On the Menu bar, click Edit, then New, then String Value. Type in “SQL2outerjoin” (without the quotes) for the key value then press the Enter key.
   With “SQL2outerjoin” highlighted, on the Menu bar click Edit, then Modify. In the Value data field enter “3kodbc” (without the quotes) then click OK.

You will now be able to do a three table (or more) inner join in Crystal Reports. Unfortunately this fix breaks the outerjoin functionality. In order to perform an outerjoin you will need to edit the SQL code and add the following highlighted code ({oj }) to your Select statement:

SELECT

TRACK_DETAIL.”PROBLEM_ID”, TRACK_DETAIL.”COMPANY”, TRACK_DETAIL.”MASTER_ID”,

MASTER_DETAIL.”MASTER_ID”, MASTER_DETAIL.”MASTER_DESC”

FROM

{oj “TRACX”.”TRACK_DETAIL” TRACK_DETAIL LEFT OUTER JOIN “TRACX”.”MASTER_DETAIL”

MASTER_DETAIL ON TRACK_DETAIL.”MASTER_ID” = MASTER_DETAIL.”MASTER_ID”}