Documentation Home
Connectors and APIs Manual
Download this Manual
PDF (US Ltr) - 4.5Mb
PDF (A4) - 4.5Mb


Connectors and APIs Manual  /  ...  /  Using Connector/ODBC with Crystal Reports

5.6.6 Using Connector/ODBC with Crystal Reports

Crystal Reports can use an ODBC DSN to connect to a database from which you to extract data and information for reporting purposes.

Note

There is a known issue with certain versions of Crystal Reports where the application is unable to open and browse tables and fields through an ODBC connection. Before using Crystal Reports with MySQL, please ensure that you have update to the latest version, including any outstanding service packs and hotfixes. For more information on this issue, see the Business) Objects Knowledgebase for more information.

For example, to create a simple crosstab report within Crystal Reports XI, follow these steps:

  1. Create a DSN using the Data Sources (ODBC) tool. You can either specify a complete database, including user name and password, or you can build a basic DSN and use Crystal Reports to set the user name and password.

    For the purposes of this example, a DSN that provides a connection to an instance of the MySQL Sakila sample database has been created.

  2. Open Crystal Reports and create a new project, or an open an existing reporting project into which you want to insert data from your MySQL data source.

  3. Start the Cross-Tab Report Wizard, either by clicking the option on the Start Page. Expand the Create New Connection folder, then expand the ODBC (RDO) folder to obtain a list of ODBC data sources.

    You will be asked to select a data source.

    Figure 5.35 Cross-Tab Report Creation Wizard

    Content is described in the surrounding text.

  4. When you first expand the ODBC (RDO) folder you will be presented the Data Source Selection screen. From here you can select either a pre-configured DSN, open a file-based DSN or enter and manual connection string. For this example, the pre-configured Sakila DSN will be used.

    If the DSN contains a user name/password combination, or you want to use different authentication credentials, click Next to enter the user name and password that you want to use. Otherwise, click Finish to continue the data source selection wizard.

    Figure 5.36 ODBC (RDO) Data Source Selection Wizard

    Three options include "Select Data Source" that displays a list of selectable pre-configured Data Source Names, "Find File DSN" with a select box to choose a file, and "Enter Connection String" to manually enter a connection string.

  5. You will be returned the Cross-Tab Report Creation Wizard. You now need to select the database and tables that you want to include in your report. For our example, we will expand the selected Sakila database. Click the city table and use the > button to add the table to the report. Then repeat the action with the country table. Alternatively you can select multiple tables and add them to the report.

    Finally, you can select the parent Sakila resource and add of the tables to the report.

    Once you have selected the tables you want to include, click Next to continue.

    Figure 5.37 Cross-Tab Report Creation Wizard with Example ODBC (RDO) Data

    Content is described in the surrounding text.

  6. Crystal Reports will now read the table definitions and automatically identify the links between the tables. The identification of links between tables enables Crystal Reports to automatically lookup and summarize information based on all the tables in the database according to your query. If Crystal Reports is unable to perform the linking itself, you can manually create the links between fields in the tables you have selected.

    Click Next to continue the process.

    Figure 5.38 Cross-Tab Report Creation Wizard: Table Links

    Example shows two linked tables named "city" and "country". The left pane shows the two table objects as boxes with column names with a line linking tables city to country. The right pane includes options to "Auto-Arrange", "Auto-Link" by name or key, "Order Links", "Clear Links", "Delete Link", "Link Options", and "Index Legend".

  7. You can now select the columns and rows that to include within the Cross-Tab report. Drag and drop or use the > buttons to add fields to each area of the report. In the example shown, we will report on cities, organized by country, incorporating a count of the number of cities within each country. If you want to browse the data, select a field and click the Browse Data... button.

    Click Next to create a graph of the results. Since we are not creating a graph from this data, click Finish to generate the report.

    Figure 5.39 Cross-Tab Report Creation Wizard: Cross-Tab Selection Dialog

    Content is described in the surrounding text.

  8. The finished report will be shown, a sample of the output from the Sakila sample database is shown below.

    Figure 5.40 Cross-Tab Report Creation Wizard: Final Report

    The generated final report example includes three columns, from left-to-right: Country, City, and Total. Each country includes an associated total, and a total of all countries is displayed on top in the right-most column.

Once the ODBC connection has been opened within Crystal Reports, you can browse and add any fields within the available tables into your reports.