Clarity - 15.9.0

PDF
15.9.1 15.9.0 15.8.1 15.8 15.7.1 15.7 15.6.1 15.6 15.5.1 15.5 15.4.1 15.4 15.3 15.2 15.1 14.4 14.3 14.2
English

Install Jaspersoft Studio Professional 7.1 and CA JDBC Adapter

ccppmop159
The CA JDBC Adapter enables report developers to connect to the
Classic PPM
 transactional or data warehouse database to run queries from Jaspersoft Studio Professional without a VPN connection. The adapter supports up to 1,000 rows that are returned in a query when developing and previewing reports. The adapter works with Oracle and Microsoft SQL Server databases.
The CA JDBC Adapter can only be used with Jaspersoft Studio Pro for
Clarity
. Any other reporting or database tool is not supported to work with the CA JDBC Adapter. Verify that you have a supported version of Jaspersoft 7.1.3 (or 7.1) running on your reports server. You can find the Jaspersoft version in the About link in the upper-right corner of all
Clarity
 pages.
2
Download and Install Jaspersoft Studio Professional
Customers using Clarity 15.6 and higher can upgrade to Jaspersoft 7.1.3. Upgrading and patching your Clarity environment including Jaspersoft 7.1.3 is very important. Jaspersoft Server 7.1.3 version includes critical security fixes and it is highly advisable to upgrade to 7.1.3 version. You can continue to use Jaspersoft Studio Professional 7.1 with Jaspersoft 7.1.3. To learn more about the security vulnerabilities which were fixed in Jaspersoft 7.1.3 version, refer to the security advisories issued by Tibco.
The steps for 7.1 and 7.1.3 are similar. Follow these steps:
  1. As a SaaS or on-premise
    Clarity
     administrator with a valid CA Support account, download Jaspersoft Studio Professional 7.1 with optional CA JDBC Adapter.
    You are re-directed to ftp://ftp.ca.com/DVD500000000002062.iso (7.1) or ftp://ftp.ca.com/DVD500000000001062.iso (6.4.2).
  2. (Optional) As an on-premise administrator with a valid CA Support account, you can also download
    Jaspersoft Studio Professional 7.1 with CA JDBC Adapter
    or
    Jaspersoft Studio Professional 6.4.2 with CA JDBC Adapter
     from our CA Support downloads site.
  3. Mount the downloaded ISO image to extract the two ZIP archives:
    • ca_jdbc_adapter_7.x.x_x.x.x.xx
      : This archive contains the optional CA JDBC Adapter.
    • TIBCOJaspersoftStudioPro-7.1.0.zip
      : This archive contains the Jaspersoft Studio Professional installers and license file.
  4. After you extract the
    TIBCOJaspersoftStudioPro.zip
    , run the 32-bit or 64-bit Windows installer or the Mac OSX installer for Jaspersoft Studio Professional. The same license applies to all versions.
  5. Launch Jaspersoft Studio Professional.
  6. Click
    Help
    ,
    License Manager.
  7. Click
    Install new license
     and browse to the location of the
    jasperreports.license
     file included in the downloaded ZIP folder.
  8. Click Open to install it. The following message appears:
    The new license for Jaspersoft Studio Professional was successfully installed.
  9. Click
    OK
    .
Download and Install CA JDBC Adapter for Jaspersoft Studio Professional
  1. As a SaaS or on-premise
    Clarity
     administrator with a valid CA Support account, download Jaspersoft Studio Professional 7.1 or 6.4.2 with optional CA JDBC Adapter.
    You are re-directed to ftp://ftp.ca.com/DVD500000000002062.iso (7.1) or ftp://ftp.ca.com/DVD500000000001062.iso (6.4.2).
  2. (Optional) As an on-premise administrator with a valid CA Support account, you can also download
    Jaspersoft Studio Professional 7.1 or 6.4.2 with CA JDBC Adapter
     from our support site.
  3. Mount the downloaded ISO image to extract the two ZIP archives:
    • ca_jdbc_adapter_7.1.0.3.zip
      or
      ca_jdbc_adapter_6.4.2.2.zip
      : This archive contains the optional CA JDBC Adapter.
    • TIBCOJaspersoftStudioPro-7.1.0.zip
      or
      TIBCOJaspersoftStudioPro-6.4.2.zip
      : This archive contains the Jaspersoft Studio Professional installers and license file.
  4. Extract
    ca_jdbc_adapter.zip
     to a local folder.
  5. Log in to
    Classic PPM
     as an administrator.
  6. Click
    Administration
    ,
    General Settings
    ,
    System Options
    ,
    Advanced Reporting
     and verify the following information:
    • Advanced Reporting Server URL
    • Organization ID
  7. Click
    Advanced Reporting
    ,
    Manage
    ,
    Users
     and verify that a report developer user exists. The report developer user must be created directly in Advanced Reporting and not as a resource in
    Classic PPM
    . If a report developer user does not exist, contact your administrator to create a user. See Create an Advanced Reporting Developer User.
  8. Assign the ROLE_ADMINISTRATOR role to the report developer user in Advanced Reporting.
  9. Identify your local workspace in Jaspersoft Studio Professional. Click
    File
    ,
    Switch Workspace
    . The
    Workspace Root Path
     indicates the location of your workspace (for example,
    C:\workspace\Studio
    ).
  10. Click
    Cancel
    . Exit Jaspersoft Studio Professional if the application is running.
  11. Create a
    drivers
     folder under your local workspace (you can change the name). You identified your local workspace in the previous two steps.
  12. Copy the contents inside the ca_jdbc_adapter_.zip archive to the
    drivers
     folder that you created in the previous step. The following JAR files are included:
    • ca_jdbc_adapter_7.1.0.3.jar
       or
      ca_jdbc_adapter_6.4.2.2.jar
      : This file is the CA JDBC Adapter JAR file.
    • jdbc\ojdbc8-12.2.0.1.0.jar
      : This file is the native Oracle 11g and 12c JDBC driver JAR file. You can use the same JDBC driver file for Oracle with Microsoft SQL Server databases.
  13. Open Jaspersoft Studio Professional and continue with the next procedure.
Create a CA JDBC Adapter Connection in Jaspersoft Studio Professional
Perform the following steps for each schema and environment that you reference in developing reports. If you develop reports against the
Clarity
 database and data warehouse schemas, you must create separate adapters for each one. A report can only reference one adapter at a time.
  1. In Jaspersoft Studio Professional, navigate to the
    Repository Explorer
     view and right-click
    Data Adapters
    .
  2. Select
    Create Data Adapter
     to open the Data Adapter Wizard.
  3. Select
    Database JDBC Connectio
    n from the list of Data Adapters and click
    Next
    .
  4. On the
    Database Location
     tab, complete the following fields:
    • Name
      : The name of the data adapter (for example,
      Clarity
       TXN Development,
      Clarity
       DWH Development,
      Clarity
       TXN Test,
      Clarity
       DWH Test)
    • JDBC Driver
      :
      com.jaspersoft.jdbc.driver.JasperDriver
      This option is not available from the drop-down list. Enter the JBDC driver exactly as specified or use
      Copy
       and
      Paste
      . 
    • JDBC URL
      : Follow this format:
        jdbc:[email protected]
      <
      Clarity
       SERVER URL>/reportservice
      ;datasource=
      <BEAN_DATA_SOURCE_REPOSITORY_PATH>
      ;pagesize=
      <PAGE_SIZE>
      <
      Clarity
       SERVER URL>/reportservice;
      Specify your
      Clarity
       server URL for the application (including a colon and the port number if applicable) followed by a forward slash and the string
      reportservice
       followed by a semi-colon.
      <BEAN_DATA_SOURCE_REPOSITORY_PATH>
      The path in the organization repository where the bean data source is located. The path is relative to the organization.
      <PAGE_SIZE>
      The number of records that the adapter fetches at one time from the server. The maximum limit is a server setting of 500. You can specify a page size value in the range of 100-500. If you specify a value greater than 500, the data adapter still fetches 500 records at a time because the server setting takes precedence. This page size setting does not control the number of rows that are returned in a query. Another setting on the server determines the maximum rows that are returned in a query (the default is 1,000 rows). The page size is a fetch mechanism to transmit records over the network and is transparent to the report developer.
      Clarity
       Bean Data Source Example:
         jdbc:[email protected]://ppm-server-1234.ondemand.ca.com/reportservice;datasource=/ca_ppm/data_sources/CA_PPM_BEAN;pagesize=500
      Clarity
       Data Warehouse Bean Data Source Example:
         jdbc:[email protected]://ppm-server-1234.ondemand.ca.com/reportservice;datasource=/ca_ppm/data_sources/CA_PPM_DWH_BEAN;pagesize=500
    • Username
      :
      <USERNAME>
      |
      <ORGANIZATION_ID>
      The report developer user created in Advanced Reporting and the Organization ID available in System Options-Advanced Reporting.
      Example:
       report_developer|cappm1234_dev
    • Password
      :
      <PASSWORD>
      The report developer user password in Advanced Reporting.
      image displaying the report developer username and password created in Advanced Reporting
  5. Click the
    Driver Classpath
     tab and click
    Add
    . Select the folder where you copied the JAR files and add the files to the classpath.
    This image shows sample JAR file paths on the Driver Classpath tab.
  6. Click
    Test
     to verify the adapter configuration.
    A
    Successful
     message appears.
  7. Click
    Finish
     to save the data adapter settings.
If you upgrade using the same workspace, update the drivers manually. Drivers are not automatically updated as part of an upgrade.
In some tests, after upgrading to Jaspersoft 7.1 and Jaspersoft Studio 7.1, clicking the
Test
 connection button showed
Successful
 but in reality it failed with a 404 error until we duplicated the connection or created a new connection. Only then did it start working as expected. This behavior was the case with the Jaspersoft Adapters as well. You can ignore this problem because it likely means the Oracle schema data warehouse user was locked. However, post-upgrade, you must create a new connection or duplicate it. 
image2018-11-20_14-9-29.png
See the Jaspersoft Upgrade Guide. This guide explains how to clear work folders, temp folders, and browser cache files.
Preview Data with the Data Adapter
After creating a data adapter connection, you can select it from the Dataset and Query Dialog or Preview when developing reports in Jaspersoft Studio.
Follow these steps:
  1. In Jaspersoft Studio, select
    File
    ,
    New
    ,
    Jasper Report
    .
    The New Report Wizard displays the report templates page.
  2. Select a template (for example, Blank A4) and click
    Next
    .
    The New Report Wizard displays the report file page.
  3. Select a local folder to save the report and provide a file name with the
    .jrxml
     extension. Avoid spaces and special characters when creating the JRXML file.
  4. Click
    Next
    .
    The New Report Wizard displays the data source page.
  5. Select the "One Empty Record - Empty rows" data adapter for now. You can change it later.
  6. Click
    Next
     and then click Finish.
    Jaspersoft Studio generates the report and opens it in the Design area.
  7. Click the Dataset and Query editor dialog in the upper-right corner of the Design area as shown in the following image to build and test the report query.
    image showing the Dataset and Query editor dialog
  8. In the Dataset and Query Dialog, select the
    Clarity
     DWH Development data adapter that you created earlier.
    The "Getting metadata for
    Clarity
     DWH Development Please wait ..." message appears. Wait until the metadata is populated with a list of the database schema names. The database schema that you have access to is indicated as "CURRENT".
  9. Expand the schema name that shows as "CURRENT" to see the available tables and views as shown in the following image.
    You can now create your SQL queries or build them using the drag-and-drop query builder. Refer to the Jaspersoft Studio documentation for more information about the query builder.
  10. Once the query is ready, you can map the columns of the result set to fields of the report. Click Read Fields.
    The query executes and the metadata is read. If no errors occur, for each column that is selected, a field is added to the field names list with the proper class types.
    image showing the mapping of the columns of the result set to fields of the report
  11. Click the
    Data Preview
     tab to verify the data that the query returns the expected data.
    image showing the Data Preview tab where you verify that the query returns the expected data.
Troubleshoot Errors with the Jaspersoft Studio CA JDBC Adapter
  • The CA JDBC Adapter supports SQL language. The adapter does not support PLSQL-based report development.
  • When working with Oracle database CLOB, NCLOB, or BLOB columns in a report query, you must modify the Class Type for each field to
    java.sql.Clob
    ,
    java.sql.NClob
    , and
    java.sql.Blob
    , respectively.
    The Read Fields button available on the Dataset and Query Dialog uses oracle.sql.CLOB, oracle.sql.NCLOB, and oracle.sql.BLOB class types as the default for CLOB, NCLOB, or BLOB columns. After creating fields using the Read Fields button, replace these class types with their corresponding
    java.sql.x
     class type.
  • When working with Oracle database SYSTIMESTAMP columns in a report query, you must modify the Class Type for each field to
    java.sql.Timestamp
    . The Read Fields button available on the Dataset and Query Dialog uses
    oracle.sql.TIMESTAMPTZ
     class type as the default for SYSTIMESTAMP columns. After creating fields using the Read Fields button, replace this class type with the
    java.sql.Timestamp
     class type.
Error Messages:
java.sql.SQLException: User is Unauthorized/Forbidden. Please check login credentials.
This message appears when the user name, Organization ID, or password that is provided to the data adapter are incorrect or the user name does not exist.
Resolution:
 Make sure the user name, Organization ID, and password are correct. Verify that the user name exists in Advanced Reporting for the environment you are trying to connect.
Getting metadata for <data adapter> Please wait...
The Dataset and Query Dialog is searching for the metadata corresponding to the data adapter currently associated to the report. For some reason, the metadata information cannot be retrieved (for example, server not running, network connection issues ). The dialog becomes unresponsive.
Resolution:
 Contact your system administrator to verify if the system is not running or if there is a network issue. If you have another server, you can work around the issue by selecting another data adapter. Click OK to close the Dataset and Query Dialog, verifying that the desired data adapter is selected. Reopen the Dataset and Query Dialog. The dialog points to the data adapter option previously selected.
Operation cancelled. Please note that aborting the operation could cause possible resource/memory leaks, especially when working database connections. If you experience problems, please try restarting Jaspersoft Studio.
This message appears when the Dataset and Query Dialog is still searching for, or retrieving, the metadata information corresponding to the data adapter currently associated to the report and you select another data adapter from the pull down list. The operation is canceled and often the Dataset and Query Dialog does not refresh the metadata information from the latest data adapter selection.
Resolution:
 Click OK to close the Dataset and Query Dialog, verifying that the desired data adapter is selected. Reopen the Dataset and Query Dialog. The dialog points to the data adapter option previously selected. Restart Jaspersoft Studio if you experience degradation on its performance.
java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection.
This message appears if for some reason the Advanced Reporting server you are trying to connect is not available.
Resolution:
 Contact your system administrator to verify if the system is not running or if there is a network issue.
java.lang.NullPointerException in the Data Preview tab of the Dataset and Query Dialog
This message appears in the following situations:
    • An invalid class type is defined to one or more fields
    • The report query is referencing parameters that are using Groovy built-in functions and the report language is not defined as Groovy.
Resolution:
 Verify that all fields have valid class types associated to them. If you are using Groovy built-in functions, verify that the report language is Groovy. In some cases, you may need to close and reopen Jaspersoft Studio.