IBM Rational ClearQuest Crystal Reports Migration Tool

 

 

 

IBM Rational ClearQuest Crystal Reports Migration Tool 1

Introduction. 1

Prerequisites. 2

Migration tool overview.. 2

Preparing for the migration. 3

Installing and starting  the migration tool 3

Downloading and configuring the Crystal Reports Java Reporting Component 3

Troubleshooting. 3

Upgrading saved report formats. 4

Migrating reports that use mapped fields. 4

Configuring the ClearQuest Reporting Driver 5

Starting the migration tool 5

Navigating the migration tool wizard. 5

Setup ClearQuest Connection. 5

Select Crystal Report for migration. 6

Set ClearQuest folder 6

Set local folder 6

Crystal Reports Migration Result 7

Test Reports. 7

Known problems and limitations. 7

Completing the migration. 8

Migrating reports with warning messages. 8

Migrating reports manually. 8

Customizing dynamic filter parameters. 9

Upgrading CM Libraries. 10

Rational ClearQuest Product Release Notes. 11

 

Introduction

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 SAP 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

The following software components are required for migrating reports with the Crystal Reports Migration Tool.

 

Java Runtime Environment (JRE) 1.5 or higher.  The JRE included with the Rational ClearQuest Client is used by default.  If the Rational ClearQuest Client is not installed on your machine, you can download an IBM JRE separately from http://www.ibm.com/developerworks/java/jdk/.  You might be able to use a JRE supplied by another vendor, but this method has not been verified.

 

Crystal Reports Java Runtime Component (JRC) 11.8.x.  See the section Downloading and configuring the Crystal Reports Java Reporting Component for information about configuring the JRC.  Also refer to IBM tech note 1216371 for information about JRC versions supported by the Crystal Reports Migration Tool.

 

 

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:

 

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

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

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

·        Change the data source of the report format file to use the query created by the migration tool.  Crystal Reports treats the saved ClearQuest query as a JDBC stored procedure.

·        (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..

 

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

You might be able to simplify the report migration by completing the processes described in this section that are applicable in your environment

Installing and starting  the migration tool

The Crystal Report Migration Tool is available as an archive (CRMT-<id>.zip) which can be downloaded and extracted to the file system on the machine where the migration tool will run before starting the migration process. You must also install and configure the required Crystal Reports Java Reporting Component before you begin the migration.

Downloading and configuring the Crystal Reports Java Reporting Component

Before starting the Crystal Reports Migration Tool, you must first configure the runtime files from the Crystal Reports Java Reporting Component.  To download the JRC, complete the following steps:

 

  1. Navigate to the site http://www.sdn.sap.com/irj/sdn/crystalreports-java
  2. Click the link Download Crystal Reports for Eclipse 1.0
  3. Click the License Agreement link to review the license agreement
  4. Click the Crystal Reports for Java runtime engine link to download the JRC archive

 

After downloading the JRC archive, you must run a configuration script to copy required JRC runtime files to the location required by the Crystal Reports Migration Tool.  To run the configuration script, complete the following steps:

  1. Open a command prompt and navigate to the \crmt installation directory
  2. Type the following command and press Enter to complete the configuration.  Feedback messages are displayed in the command prompt during command execution.
    configureJRC <JRC archive file path>

 

Troubleshooting

By default, the configureJRC.bat script uses an archive extraction program that gets installed to the file path %RATIONAL_COMMON%/infozip/unzip during installation of ClearQuest server components.  If the unzip program is not present on the file system prior to running configureJRC, the following message displays:

“The system cannot find the path specified.” 

 

If you do not have unzip.exe installed on your system, you can copy it from the following Rational ClearQuest Installation Manager repository path and extract to the file system.

<repository base>/native/infozip_7.1.1000.0000-7-1-1-D091027.zip

If unzip.exe is installed on your system, complete the following steps to update configureJRC.bat.

  1. Edit the configureJRC.bat script
  2. Change the setting of the EXTRACT_PROGRAM to the file path for the unzip.exe on your system
  3. Save the updated configureJRC.bat
  4. Rerun the configureJRC script

 

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.  Performing this pre-migration activity will result in fewer warnings and post-migration tasks.  To upgrade a report format:

 

1.      Install the Rational ClearQuest client for Windows.

2.      Install Crystal Reports version 11

3.      Open the ClearQuest client for Windows.

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

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

6.      To refresh the report, press F5 .

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

8.      Click OK.

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

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


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.

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

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

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

 

Migrating reports that use mapped fields

If a database field name is changed in the ClearQuest schema, Crystal Reports data-push reports that reference the old database field name are not resolved at runtime by the Report Server for Crystal Reports.  This issue is described in tech note http://www.ibm.com/support/docview.wss?uid=swg21419660

The issue also occurs when using the Crystal Reports Migration Tool, and either solution from technote 1419660 can be used to resolve the problem.  Option 2 from the technote involves modifying a server configuration file named crconfiguration.properties.  To apply the mappings from crconfiguration.properties during migration, copy the file to the \crmt directory before launching the migration tool.  Failure to apply the required mappings during migration results in a generated ClearQuest report query that does not include the mapped fields.  If necessary, the migration tool can be rerun after defining appropriate mapping rules in crconfiguration.properties.

 

 

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 Information Center for information about the driver configuration steps.

 

 

Starting the migration tool

After completing the steps in the Preparing for the migration section, the Crystal Reports Migration Tool can be started from the Windows Explorer.

 

  • Navigate to the \crmt installation directory, then double-click the startup.bat to start the migration tool interface..

 

 

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 provides information about the ClearQuest connection used for migrating re-ports and captures values required for using the connection.

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.  This setting is represented as a combo box in the user interface.  The names for accessible production databases can be selected from a list.  Test database names are not displayed in the list and must be typed into the edit box.

 

Select Crystal Report for migration

This wizard page allows the user to select reports and migration settings for migrating reports 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. 

 

Preserve folder structure:  Save the ClearQuest folder structure to the file system during migration.  This option is checked by default.  Remove this check box if you want to migrate report files to a single directory using the ClearQuest database identifier to construct unique report file names.

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 in the test HTML page.  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.  Be mindful of your organization’s security practices when using this option.

 

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. 

 

·        If the a report cannot be migrated successfully, the Crystal Reports Migration tool generates an error or warning on the migration result wizard page. You can double-click on a report with a warning status for information about completing the migra-tion. For details, see Migrating reports with warning messages.

 

·        The Crystal Reports Migration Tool detects and migrates only those reports that are visible to the user who runs the tool.  For example, the user named Administrator cannot migrate reports saved in the Personal Queries folder of the user named User_1.  Copying reports, report formats, and report queries to be migrated to a Public Queries subfolder prior to running the Crystal Reports Migration Tool allows all authenticated users to migrate those reports.

 

·        The Crystal Reports Migration Tool logs migration error information to a file named crmt.log. 

Completing the migration

After the migration tool wizard process completes, additional work might be necessary to complete the migration.  Migrating reports with warning messages provides information about using the Crystal Reports Designer to complete the report migration for reports that generated warning messages in the Crystal Reports Reporting tool. Migrating reports manually provides information about using the Crystal Reports Designer facilities to manually migrate reports.

. 

 

Migrating reports with warning messages

In cases where migration cannot complete, the migration tool displays an error or warning for the report 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.

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, use the option Extract reports only to save the report format files to the file system without migrating.  With this option, the queries required by the migrated report are saved, but not mapped into the migrated report.

 

  • Complete the following steps for each report to be migrated.

 

  1. Open the target report format file using the Crystal Reports Designer 11 R2.
  2. 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>]

Example:  jdbc:cq:8.0.0/SAMPL@http://myhost:12080/TeamWeb/services/Team

The cm_server_url is optional.  It is used for specifying connections to database profiles that are hosted on a change management server.

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

  1. To open a dialog for mapping to the new connection, click Database->Set Datasource Location. 
  2. 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.
  3. 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.

  4. To replace the current report data source with the ClearQuest query data source, click Update.
  5. If necessary, the Map Fields dialog opens to allow mapping of fields that Crystal Reports could not map automatically.
  6. After all fields are mapped using the Map Fields dialog, click OK.
  7. 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 options for parameter fields to improve the usability of your data-pull reports.  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.1.08 Change Management API libraries to interface with ClearQuest databases.  If you are attempting to migrate reports on a Change Management Server that is older than version 7.1.1.08, you must replace the CM API jar files used by the Crystal Reports Migration Tool with versions that are compatible with the target Change Management server.  After a failed migration, check the crmt.log for a message like this one:

CRVSV1014E Client is too new to use this server. Maximum version accepted: <version>

 

If the message appears in crmt.log, you should replace the CM libraries with the ones in your installation.  Instructions for updating the CM libraries from your ClearQuest installation are provided below.

 

  • From a Command Prompt, navigate to the directory \crmt\lib, and run these commands:

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 7.1 product, see http://www.ibm.com/support/search.wss?&q=clearquest+RN7.1

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