Beginning in IBM Rational ClearQuest 7.1, a flexible new approach is available for building reports that access ClearQuest data. A reporting driver is provided that partially implements the Java Database Connectivity (JDBC) API and enables various reporting tools to consume ClearQuest data. The reporting driver implementation uses the Rational Change Management API to interface with Rational ClearQuest, thus preserving the ClearQuest security model and providing data-access by third party reporting tools like Business Objects Crystal Reports.

Releases prior to Rational ClearQuest 7.1 support a “data-push” model of reporting in which a ClearQuest client executes a query and invokes Crystal Reports to format the output using a report format that is saved in the ClearQuest repository. The new model introduced in Rational ClearQuest 7.1 is a “data-pull” model that allows the reporting system to connect to ClearQuest databases using the ClearQuest Reporting Driver as a data source to retrieve query results that populate reports.

Using Crystal Reports and the ClearQuest Reporting Driver, new data-pull reports can be created from scratch. Users with existing data-push reports can use the Crystal Reports Migration Tool to assist with conversion of those reports to use the data-pull model.

Prerequisites

Java Runtime Environment 1.5. The JRE included with the Rational ClearQuest for Eclipse client is used by default. If the Rational ClearQuest for Eclipse client is not installed on your machine, the IBM JRE can be separately downloaded from http://www.ibm.com/developerworks/java/jdk/.

Crystal Reports Java Runtime Component (JRC). A licensed version of the JRC is bundled with the Crystal Reports Migration Tool. Read the third party license agreement to understand the terms and conditions of usage for the JRC.

Migration tool overview

Using the data-pull reporting model, all information required for report execution is stored with the report format file, allowing the report to be executed without launching a ClearQuest client. You can use the Crystal Reports Migration Tool to perform the following general tasks to convert data-push reports to the data-pull model:

1. Extract the Crystal Reports format file from the ClearQuest repository and save to the file system

2. Create and save a ClearQuest query that returns all fields required by the Crystal Reports report format.

3. Create a connection URL that allows Crystal Reports to connect to the ClearQuest Reporting driver

4. Change the data source of the report format file to use the query created in step 2. Crystal Reports treats the saved ClearQuest query as a JDBC stored procedure.

5. (Optional) Create an HTML file that can be used to launch the migrated reports using the Report Server for Crystal Reports feature of Rational ClearQuest 7.1.

The migration tool saves a new version of each report to the file system. Reports and report formats saved in the ClearQuest repository are not changed.

Preparing for the migration

Completion of some pre-migration steps may simplify the migration process. Read the following sections, and determine if the recommendations are appropriate for your environment.

Upgrading saved report formats

When migrating reports that use Crystal report formats that were created with versions of Crystal Reports prior to version 10, it is a good idea to upgrade the saved report formats before the migration by using the Crystal Reports 11 report designer. Crystal Reports 11 and the Rational ClearQuest client for Windows must be installed in order to complete the following steps. Performing this pre-migration activity will result in fewer warnings and post-migration tasks. To upgrade a report format:

1. Open the ClearQuest client for Windows.

2. Select a report format that requires upgrade, right-click, and click Edit…

3. To open the Crystal Reports report designer, from the Edit Report Format dialog, click Author Report.

4. To refresh the report, press F5 .

5. The Verify Database dialog opens with the message:
The database file “<file name>” has changed. Proceeding to fix up report!

6. Click OK.

7. The Verify Database dialog again displays with the message:
The database is now up to date

8. To exit Crystal Reports, select File->Exit .

9. The Crystal Reports dialog opens to warn that you are about to overwrite the Crystal Reports file with a newer version. The warning may include a note that the updated report format will not be editable from versions of Crystal Reports prior to version 9.

10. To save the updated report format, click Yes .

11. To save the updated report format to the ClearQuest database, from the ClearQuest Edit Report Format dialog, click OK .

12. Repeat steps 2-11 for other report formats that are used by the reports you want to migrate.

Configuring the ClearQuest Reporting Driver

After migrating ClearQuest reports to the data-pull model, report modification requires that the ClearQuest Reporting Driver be configured for usage with Crystal Reports. See the Rational ClearQuest documentation for information about the driver configuration steps.

Installing and launching the migration tool

The Crystal Reports Migration Tool download file is provided as an archive that must be extracted to the local file system on the machine where the migration tool will run. After extracting the contents of the CRMT-7.1.0.zip archive, the Crystal Reports Migration Tool can be launched from the Windows Explorer by navigating to the \crmt installation directory, and double-clicking the startup.bat file. A command window launches and the migration tool user interface is opens.

Navigating the migration tool wizard

The migration tool wizard allows the user to provide information about the reports to be migrated. This section describes the pages of the Crystal Reports Migration Tool wizard.

Setup ClearQuest Connection

This wizard page contains settings to describe the ClearQuest connection for migrated reports. A description of the settings is below.

My Default ClearQuest Connections: Lists connection profiles defined on the local machine.

Change Management Server: A URL for a ClearQuest CM Server where the target ClearQuest connection profile is defined. For example:

http://myhost:12080/TeamWeb/services/Team

User Name: The ClearQuest user name for the user that performs the migration.

Password: The password for the ClearQuest user that performs the migration.

Schema Repository: The ClearQuest schema repository where target reports are stored.

Database: The ClearQuest database name where target reports are stored.

Select Crystal Report for migration

This wizard page allows the user to choose the ClearQuest reports that will be migrated to the data-pull model.

Add All: Add all reports from all visible folders to the list of reports to be migrated.

Add: Add the selected report to the list of reports to be migrated.

Remove: Remove the selected report from the list of reports to be migrated.

Remove All: Remove all reports from the list of reports to be migrated.

Extract report format only: Save the targeted reports to the local file system, but do not attempt to migrate the connection information. Saved report format files can be manually migrated using the steps described in the Completing the migration section.

Set ClearQuest folder

This wizard page allows the user to select the ClearQuest folder where report queries will be saved. The selected folder must be visible to all users who will run the reports, so the folder should be a subfolder of the Public Queries folder.

Create Folder: Create a new ClearQuest folder. The new folder will be a child of the currently selected folder from the folder viewer.

Set local folder

This wizard page allows the user to specify the file path on the local file system where report format files are saved during migration.

After specifying a valid file path, click Finish to perform the migration.

Crystal Reports Migration Result

This wizard page opens after the migration tool finishes. The page displays the results of each migrated report. For report migrations with results other than “Success”, double-click the target report to view detailed information about the migration warning or failure. The migration tool provides recommended steps for completing the migration of reports that resulted in “Warning” messages.

The migrated reports are saved to the local directory specified by the user. The naming convention for the saved file closely resembles the saved name of the report in the ClearQuest repository.

Test Reports

This section can be used to generate an HTML page for launching migrated reports. In order to use this option, the migrated report files must reside in a directory path that is accessible by the Report Server for Crystal Reports. Therefore, the Report Server for Crystal Reports must be installed on the same machine where the migration is performed, or the migrated report format files must be stored on a shared drive that is accessible to the Report Server for Crystal Reports. The generated HTML files are stored in the folder that was specified in the Set local folder migration step. The HTML file named left.html contains the launch links for the migrated report, and that HTML file can be modified if the migrated reports are moved to a different location.

Run Reports: Click this button to launch a browser that opens a test HTML page.

Include Password: Select this option to include an encrypted password for each of the report launch links. If the password is not included, Crystal Reports will prompt for the username and password combination when a report is launched from the test page.

Known problems and limitations

Report formats to be migrated must be saved using Crystal Reports version 9.0 or higher. Performing the steps from Preparing for the migration ensures that report formats are upgraded to a supported version before the migration is performed.

For cases where migration cannot complete, the migration tool provides an error or warning in the Crystal Reports Migration Result wizard page. You can double-click reports that issue “Warning” results to view instructions for completing the migration of the report.

Typical instructions for migration completion:

1. Open the migrated report with Crystal Reports Designer 11 or higher.

2. Refresh report from Report->Refresh Report Data menu or press F5 shortcut key …

3. Save the report file.

Users may also choose to use the facilities of the Crystal Reports Designer to manually migrate reports. See the Migrating reports manually section for information.

Completing the migration

After the migration tool completes, additional customization may be needed to complete the migration. This section contains information about using the Crystal Reports Designer to complete the report migration to the data-pull model.

Migrating reports manually

If necessary, migration of Crystal Reports to the data-pull format can be done manually using the facilities available in the Crystal Reports report designer.

Install Crystal Reports 11 release 2, and configure for usage with the ClearQuest Reporting Driver. See the IBM Rational ClearQuest product documentation for instructions.

Use the Crystal Reports Migration tool to extract targeted Crystal Reports format files from the ClearQuest repository. When using the Crystal Reports Migration Tool, the option Extract reports only should be used to save the report format files to the file system without migrating. The queries required by the migrated report are saved, but not mapped into the migrated report.

For each report to be migrated, use the following steps:

Open the target report format file using the Crystal Reports Designer 11 R2.
Create a new JDBC connection using the ClearQuest Reporting Driver:

a. To open the Database Expert, click Database->Database Expert

b. To open the JDBC(JNDI) connection dialog, click Create New Connection->JDBC (JNDI)->Make New Connection

c. Specify the settings for the ClearQuest Reporting Driver:

§ Connection URL: jdbc:cq:<dbset>/<database>@<cm_server_url>

§ Database Classname: com.ibm.rational.clearquest.jdbc.CQDriver

§ To advance to the Connection Information page, click Next.

§ Enter User ID and Password.

d. To save the new connection, click Finish.

e. To close the Database Expert dialog, click OK.

To open a dialog for mapping to the new connection, click Database->Set Datasource Location.
From the Current Data Source viewer, select the current ClearQuest data table. The data source name differs depending on the version of ClearQuest used to create the report format. The name is either _CRPT_TMP0001 or ClearQuest.
From the Replace with viewer, expand the tree list, and select the saved ClearQuest query to be used for the migrated report.
NOTE: The name of the saved query is the same or similar to the report file name.
To replace the current report data source with the ClearQuest query data source, click Update.
If necessary, the Map Fields dialog opens to allow mapping of fields that Crystal Reports could not map automatically.
After all fields are mapped using the Map Fields dialog, click OK.
Click Close.

After updating the data source, click F5 to refresh the report data for the report.

Customizing dynamic filter parameters

Reports that use dynamic query filters require special attention when creating data-pull reports. For ClearQuest queries, dynamic filters require that both an operator and an operand be specified. In Crystal Reports, the filter values are represented by parameter fields. The ClearQuest Reporting driver appends the suffix “_Operator” to the parameter field that stores filter operators and the suffix “_Parameter” to the parameter field that stores the filter values.

Valid query filter operators are listed below:

HAS_NO_SUBSTRING
HAS_SUBSTRING
IS_EQUAL
IS_GREATER_THAN
IS_GREATER_THAN_OR_EQUAL
IS_IN_SET
IS_LESS_THAN
IS_LESS_THAN_OR_EQUAL
IS_NOT_BETWEEN
IS_NOT_EQUAL
IS_NOT_IN_SET
IS_NOT_NULL
IS_NULL

Crystal Reports provides a variety of customizations for parameter fields to make your data-pull reports more user friendly. See the Crystal Reports Online Help for information about parameter fields.

Upgrading CM Libraries

The Crystal Reports Migration Tool uses the Rational ClearQuest 7.1 Change Management API libraries to interface with ClearQuest databases. If you are using a version of ClearQuest that is higher than 7.1, it may be necessary or desirable to upgrade the CM API jar files used by the Crystal Reports Migration tool. Instructions for updating the CM libraries from your ClearQuest installation are provided below.

From a Command Prompt, navigate to the directory \cmrt\lib, and execute the commands below:

copy "%CLEARQUEST_HOME%\cqjni.jar" .

copy "%CLEARQUEST_HOME%\stpcq.jar" .

copy "%RATIONAL_COMMON%\stp*.jar" .

Rational ClearQuest Product Release Notes

For information about known problems and limitations in the Rational ClearQuest product, see http://www-01.ibm.com/support/search.wss?&q=clearquest+RN7.1.