Latest News

the latest news from our team

Crystal Reports

Getting Started. How to create a new report.

crystalrpts1

From the Welcome To dialog, select New Report.
crystalrpts2

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.
ODBC_h03

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

Select Done.
crystalrpts6

Select Next.
ODBC_h08

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

Add a sort field, then select Preview Report.
crystalrpts9

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”}

Leave a Reply

Your email address will not be published. Required fields are marked *