Jaspersoft Studio Professional CA JDBC Adapter

As a report developer, you can connect to the transactional or data warehouse schemas 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.
ccppmod143
As a report developer, you can connect to the
Clarity Project and Portfolio Management (PPM)
transactional or data warehouse schemas 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. Any other reporting or database tool is not supported to work with the CA JDBC Adapter.
2
Verify the Installation Requirements
 Before you install the CA JDBC Adapter, complete the following requirements:.
  1. Verify that you have a supported version of Jaspersoft on the server. You can find the Jaspersoft version in the About link in the upper-right corner in CA PPM. See the
    Release Notes
    for the supported version.
  2. Download and install
    Jaspersoft Studio Professional with CA JDBC Adapter
    for your release of CA PPM as listed on the Advanced Reporting and Database Schema Index page.
  3. Extract the CA JDBC Adapter zip file included with the Jaspersoft Studio Professional image that you downloaded in Step 2.
  4. Log in to CA PPM as an administrator.
  5. Click Administration, General Settings, System Options, Advanced Reporting.
  6. Note the following information:
    • Advanced Reporting Server URL
    • Organization ID
  7. Click Advanced Reporting, Manage, Users.
  8. Verify that a report developer user exists. If a report developer user does not exist, contact your administrator to create a user. The report developer user must be created directly in Advanced Reporting (CA PPM/Advanced Reporting/Manage/Users) and not as a resource in CA PPM. 
  9. Assign the ROLE_ADMINISTRATOR role to the report developer user in Advanced Reporting. See 
    Create Report Developer Users in Advanced Reporting
     in Create and Publish Jaspersoft Studio Reports
  10. Review the following compatibility matrix to find the correct software combination for your environment.
Compatibility Matrix for Database, Reporting Environment, and JDBC Adapter
Oracle
JasperReports Server
Jaspersoft Studio Professional
CA JDBC Adapter
Oracle Native JDBC Driver
Oracle 11g
6.2.1
6.2.1
ca_jdbc_adapter_6.2.1.3.jar
ojdbc6-11.2.0.4.jar
Oracle 12c
6.2.1
6.2.1
ca_jdbc_adapter_6.2.1.3.jar
ojdbc7-12.1.0.1.jar
Oracle 11g
6.1.0
6.1.0
ca_jdbc_adapter_6.1.0.10.jar
ojdbc6-11.2.0.4.jar
Oracle 12c
6.1.0
6.1.0
ca_jdbc_adapter_6.1.0.10.jar
ojdbc7-12.1.0.1.jar
Install the CA JDBC Adapter
The installation process is as follows:
  1. Exit Jaspersoft Studio Professional if the application is running.
  2. Create a folder under your local workspace and name it, for example, drivers. If you do not know where your workspace is located, you can verify it from Jaspersoft Studio Professional. Navigate to File and select Switch Workspace. The "Workspace Root Path" indicates the location of your workspace (for example, C:\workspace\Studio). Click Cancel.
  3. Copy the contents of the CA JDBC Adapter zip file:
    • If your supported version of Jaspersoft is 6.4.2, copy the contents of the CA JDBC Adapter zip file to the folder you created in the previous step. The contents should include the following files:
      • CA JDBC Adapter jar file
      • Oracle 12c native JDBC driver jar file under the jdbc folder
    • If your supported version of Jaspersoft is 6.2.1, copy the contents of the ca_jdbc_adapter_6.2.1.3.zip file to the folder you created in the previous step. The contents should include the following jar files:
      • ca_jdbc_adapter_6.2.1.3.jar --> CA JDBC Adapter jar file
      • jdbc\ojdbc6-11.2.0.4.jar --> Oracle 11g native JDBC driver jar file
      • jdbc\ojdbc7-12.1.0.1.jar --> Oracle 12c native JDBC driver jar file
    • If your supported version of Jaspersoft is 6.1.0, copy the contents of the ca_jdbc_adapter_6.1.0.10.zip to the folder you created in the previous step. The contents should include the following jar files:
      • ca_jdbc_adapter_6.1.0.10.jar --> CA JDBC Adapter jar file
      • jdbc\ojdbc6-11.2.0.4.jar --> Oracle 11g native JDBC driver jar file
      • jdbc\ojdbc7-12.1.0.1.jar --> Oracle 12c native JDBC driver jar file
  4. Reopen Jaspersoft Studio Professional.
Create a CA JDBC Adapter 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 Project and Portfolio Management (PPM)
transactional and data warehouse schemas, you must create an adapter for each. You can only reference one adapter at a time per report.
  1. In Jaspersoft Studio Professional, navigate to the Repository Explorer view and right click Data Adapters. Select Create Data Adapter to open the Data Adapter Wizard. 
  2. Select "Database JDBC Connection" from the list of Data Adapters and click Next.
  3. On the Database Location tab, provide the following information:
    • Name
      : The name of the data adapter (for example, CA PPM TXN Development, CA PPM DWH Development, CA PPM TXN Test, CA PPM DWH Test)
    • JDBC Driver
      : com.jaspersoft.jdbc.driver.JasperDriver
      This option is not available from the drop-down list. Type the JBDC driver exactly as specified above, or copy/paste.  
    • JDBC Url
      : Follow the format:
      jdbc:[email protected]
      <ADVANCED_REPORTING_SERVER_URL>
      ;datasource=
      <BEAN_DATA_SOURCE_REPOSITORY_PATH>
      ;pagesize=
      <PAGE_SIZE>
      <ADVANCED_REPORTING_SERVER_URL>
      The complete Advanced Reporting Server URL available in System Options-Advanced Reporting (CA PPM/Administration/General Settings/System Options-Advanced Reporting
      ).
      <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.
      CA PPM Bean Data Source Example:
      jdbc:[email protected]://cappm1234.ondemand.ca.com/reportservice;datasource=/ca_ppm/data_sources/CA_PPM_BEAN;pagesize=500
      CA PPM Data Warehouse Bean Data Source Example:
      jdbc:[email protected]://cappm1234.ondemand.ca.com/reportservice;datasource=/ca_ppm/data_sources/CA_PPM_DWH_BEAN;pagesize=500
      <PAGE_SIZE>
      The number of records that the adapter fetches at a 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 fetches 500 records at a time only 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, which is set at 1000 rows. The page size is a fetch mechanism to transmit records over the network and is transparent to the report developer. 
    • 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.
  4. Navigate to the Driver Classpath tab and click Add.
  5. Click the folder where you copied the jar files and add the files to the classpath. Two Oracle native JDBC driver jar files are included in the zip file. Use the JDBC driver compatible with the Oracle version of the CA PPM transactional and data warehouse schemas. The Jar Files Path window should display the files that you added.
    For
    Clarity Project and Portfolio Management (PPM)
    SaaS customers, use the Oracle 11g JDBC driver (ojdbc6-11.2.0.4.jar). This JDBC driver is compatible with the Oracle version in a SaaS environment. For On Premise customers, contact your database administrator.
    If your supported version of Jaspersoft is 6.4.2, use the same JDBC driver for Oracle or Microsoft SQL Server.
    If your supported version of Jaspersoft is 6.2.1, for Oracle 11g, the Jar Files Path window should display the following files after adding the files:
    <PATH>\ca_jdbc_adapter_6.2.1.3.jar
    <PATH>\jdbc\ojdbc6-11.2.0.4.jar
    Oracle 11g Example:
    C:\workspace\Studio\drivers\ca_jdbc_adapter_6.2.1.3.jar
    C:\workspace\Studio\drivers\jdbc\ojdbc6-11.2.0.4.jar
    For Oracle 12c
    , the Jar Files Path window should display the following files after adding the files:
    <PATH>\ca_jdbc_adapter_6.2.1.3.jar
    <PATH>\jdbc\ojdbc7-12.1.0.1.jar
    Oracle 12c Example:
    C:\workspace\Studio\drivers\ca_jdbc_adapter_6.2.1.3.jar
    C:\workspace\Studio\drivers\jdbc\ojdbc7-12.1.0.1.jar
    For Microsoft SQL Server, use the Oracle 11g JDBC driver (ojdbc6-11.2.0.4.jar)
    If your supported version of Jaspersoft is 6.1.0, for Oracle 11g, the Jar Files Path window should display the following files after adding the files
    <PATH>\ca_jdbc_adapter_6.1.0.10.jar
    <PATH>\jdbc\ojdbc6-11.2.0.4.jar
    Oracle 11g Example:
    C:\workspace\Studio\drivers\ca_jdbc_adapter_6.1.0.10.jar
    C:\workspace\Studio\drivers\jdbc\ojdbc6-11.2.0.4.jar
    For Oracle 12c
    , the Jar Files Path window should display the following files after adding the files:
    <PATH>\ca_jdbc_adapter_6.1.0.10.jar
    <PATH>\jdbc\ojdbc7-12.1.0.1.jar
    Oracle 12c Example:
    C:\workspace\Studio\drivers\ca_jdbc_adapter_6.1.0.10.jar
    C:\workspace\Studio\drivers\jdbc\ojdbc7-12.1.0.1.jar
    For Microsoft SQL Server
    , use the Oracle 11g JDBC driver (ojdbc6-11.2.0.4.jar)
  6. Click Test to verify that the adapter is set up properly.
  7. Click Finish to save the data adapter.
  8. Select the data adapter - from either the Dataset and Query Dialog or Preview - when developing reports in Jaspersoft Studio Professional.
Example
The following image is an example of the Database Location tab of a data adapter accessing the Data Warehouse database:
This image is an example of the Database Location tab of a data adapter accessing the Data Warehouse database
The following image is an example of the Driver Classpath tab of the same data adapter as shown in the previous example:
This image is an example of the Driver Classpath tab of the same data adapter as shown in the earlier example
Preview Data with the Data Adapter
After configuring the CA JDBC adapter, you can select it in the Dataset and Query editor dialog to build and test your report query against a database. You can also use the CA JDBC adapter to preview reports.
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. 
    Dataset and Query editor dialog
  8. In the Dataset and Query Dialog, select the CA PPM DWH Development data adapter that you previously created. See Create a CA JDBC Adapter in Jaspersoft Studio Professional for details.
    The "Getting metadata for CA PPM DWH Development Please wait ..." message displays. 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.
    The image shows the CURRENT schema with the available tables and views.
  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 list of field names with the proper class type.
    The image shows the field names that are added with proper class types.
  11. Click the Data Preview tab to verify the data that the query returns.
    The image shows the data returned by the query in the Data Preview tab.
Notes
The CA JDBC Adapter supports SQL language. The adapter does not support PLSQL-based report development.
  • When working with Oracle database and CLOB, NCLOB, or BLOB columns in the report query, you must modify the Field's Class Type 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 by the corresponding "java.sql." class type that is described above.
  • When working with Oracle database and SYSTIMESTAMP columns in the report query, you must modify the Fields' Class Type 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 by the "java.sql.Timestamp" class type.
Troubleshooting
  • Error/Message:
    java.sql.SQLException: User is Unauthorized/Forbidden. Please check login credentials.
    Explanation:
    This message displays 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, or password that is provided to the data adapter are correct. Also, verify if the user name exists in Advanced Reporting for the environment you are trying to connect.
  • Error/Message:
    Dataset and Query Dialog showing Getting metadata for <data adapter> Please wait...  for a long period of time.
    Explanation:
    The Dataset and Query Dialog is searching for the metadata information 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.
  • Error/Message:
    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.
    Explanation:
    This message displays 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. 
  • Error/Message:
    java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection.
    Explanation:
    This message displays 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.
  • Error/Message:
    java.lang.NullPointerException in the Data Preview tab of the Dataset and Query Dialog
    Explanation:
    This message usually displays in the following situations:
    • When an invalid class type is defined to one or more fields
    • When 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.