Install Jaspersoft Studio Professional and CA JDBC Adapter
The CA JDBC Adapter enables report developers to connect to the
Classic PPMtransactional 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 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.8 or 7.1.3 running on your reports server. You can find the Jaspersoft version in the About link in the upper-right corner of all
The CA JDBC Adapter for Jaspersoft 7.8 only supports the PostgreSQL database.
Download and Install Jaspersoft Studio Professional
The steps for Jaspersoft Studio 7.9 and 7.1 are similar.
Follow these steps:
- As aClaritySaaS administrator, you can download Jaspersoft Studio Professional 7.9 with CA JDBC Adapter from the FTP site.
- If you have a valid Broadcom Support account, you can also downloadJaspersoft Studio Professional 7.9 with CA JDBC AdapterorJaspersoft Studio Professional 7.1 with CA JDBC Adapterfrom our Broadcom Support downloads site.
- 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.x.x.zip: This archive contains the Jaspersoft Studio Professional installers and license file.
- After you extract theTIBCOJaspersoftStudioPro.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.
- Launch Jaspersoft Studio Professional.
- ClickHelp,License Manager.
- ClickInstall new licenseand browse to the location of thejasperreports.licensefile included in the downloaded ZIP folder.
- Click Open to install it. The following message appears:The new license for Jaspersoft Studio Professional was successfully installed.
Configure Jaspersoft Studio to Support Bundled JDK TrustStore (Jaspersoft Studio 7.9 TCPS Only)
Jaspersoft Server 7.8.0 now supports TCPS for the Oracle 19c database. If you plan to use the Bundled JDK option, while configuring TCPS, you need to perform the following steps to use Jaspersoft Studio 7.9. These steps are critical because when the database administrators install Oracle on TCPS, they generate a private key. This key needs to be imported into Java.
- Navigate to the Java bin folder. A sample command is given below.cd C:\Program Files\TIBCO\Jaspersoft Studio Professional-7.9.0\features\jre.win32.win32.x86_64.feature_1.8.0.u151\jre\bin
- Use the following command to export the certificate.keytool.exe -exportcert -keystore <keystore path> -storepass <keystore password> -storetype <keystore type> -alias <keystore alias> -file <location to export certificate/certificate name>A sample command is given below.keytool.exe -exportcert -keystore <C:\truststore.p12> -storepass <Dummy#1234> -storetype <PKCS12> -alias <clrt-i65702.dummy.test.net_testroot> -file <C:\oracle78test.cer>
- Use the following command to import the certificate into the CA Certs directory.keytool.exe -importcert -keystore <location of cacerts> -storepass <password of cacerts keystore> -alias <keystore alias> -file <location to exported certificate/certificate name>A sample command is given below:keytool.exe -importcert -keystore <C:\Program Files\TIBCO\Jaspersoft Studio Professional-7.9.0\features\jre.win32.win32.x86_64.feature_1.8.0.u151\jre\lib\security\cacerts> -storepass <changeit> -alias <clrt-i65702.dummy.test.net_testroot> -file <C:\oracle78test.cer>
- When prompted whether you trust this certificate, type yes and press Enter. You have now added the certificate into Java.
Download and Install CA JDBC Adapter for Jaspersoft Studio Professional
- If you have a valid Broadcom Support account, you can also downloadJaspersoft Studio Professional 7.9 with CA JDBC AdapterorJaspersoft Studio Professional 7.1 with CA JDBC Adapterfrom the Broadcom Support downloads site.
- Mount the downloaded ISO image to extract the two ZIP archives:
- ca_jdbc_adapter_7.x.x.x.ziporca_jdbc_adapter_x.x.x.x.zip: This archive contains the optional CA JDBC Adapter.
- TIBCOJaspersoftStudioPro-7.x.x.ziporTIBCOJaspersoftStudioPro-x.x.x.zip: This archive contains the Jaspersoft Studio Professional installers and license file.
- Extractca_jdbc_adapter.zipto a local folder.
- Log in toClassic PPMas an administrator.
- ClickAdministration,General Settings,System Options,Advanced Reportingand verify the following information:
- Advanced Reporting Server URL
- Organization ID
- ClickAdvanced Reporting,Manage,Usersand verify that a report developer user exists. The report developer user must be created directly in Advanced Reporting and not as a resource inClassic PPM. If a report developer user does not exist, contact your administrator to create a user. See Create an Advanced Reporting Developer User.
- Assign the ROLE_ADMINISTRATOR role to the report developer user in Advanced Reporting.
- Identify your local workspace in Jaspersoft Studio Professional. ClickFile,Switch Workspace. TheWorkspace Root Pathindicates the location of your workspace (for example,C:\workspace\Studio).
- ClickCancel. Exit Jaspersoft Studio Professional if the application is running.
- Create adriversfolder under your local workspace (you can change the name). You identified your local workspace in the previous two steps.
- Copy the contents inside the ca_jdbc_adapter_.zip archive to thedriversfolder that you created in the previous step. The following JAR files are included:
- ca_jdbc_adapter_7.x.x.x.jar: This file is the CA JDBC Adapter JAR file.
- jdbc\postgreSQL-42.2.6.jar: This file is the native PostgreSQL JDBC driver JAR file.
- 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
Claritydatabase and data warehouse schemas, you must create separate adapters for each one. A report can only reference one adapter at a time.
- In Jaspersoft Studio Professional, navigate to theRepository Explorerview and right-clickData Adapters.
- SelectCreate Data Adapterto open the Data Adapter Wizard.
- SelectDatabase JDBC Connectionfrom the list of Data Adapters and clickNext.
- On theDatabase Locationtab, complete the following fields:
- Name: The name of the data adapter (for example,ClarityTXN Development,ClarityDWH Development,ClarityTXN Test,ClarityDWH Test)
- JDBC Driver:com.jaspersoft.jdbc.driver.JasperDriverThis option is not available from the drop-down list. Enter the JBDC driver exactly as specified or useCopyandPaste.
- JDBC URL: Follow this format:jdbc:[email protected]<;datasource=ClaritySERVER URL>/reportservice<BEAN_DATA_SOURCE_REPOSITORY_PATH>;pagesize=<PAGE_SIZE><ClaritySERVER URL>/reportservice;Specify yourClarityserver URL for the application (including a colon and the port number if applicable) followed by a forward slash and the stringreportservicefollowed 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.ClarityBean Data Source Example:jdbc:[email protected]://ppm-server-1234.ondemand.ca.com/reportservice;datasource=/ca_ppm/data_sources/CA_PPM_BEAN;pagesize=500ClarityData 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.
- Click theDriver Classpathtab and clickAdd. Select the folder where you copied the JAR files and add the files to the classpath.
- ClickTestto verify the adapter configuration.ASuccessfulmessage appears.
- ClickFinishto 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.8 and Jaspersoft Studio 7.9, clicking the
Testconnection button showed
Successfulbut 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.
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:
- In Jaspersoft Studio, selectFile,New,Jasper Report.The New Report Wizard displays the report templates page.
- Select a template (for example, Blank A4) and clickNext.The New Report Wizard displays the report file page.
- Select a local folder to save the report and provide a file name with the.jrxmlextension. Avoid spaces and special characters when creating the JRXML file.
- ClickNext.The New Report Wizard displays the data source page.
- Select the "One Empty Record - Empty rows" data adapter for now. You can change it later.
- ClickNextand then click Finish.Jaspersoft Studio generates the report and opens it in the Design area.
- 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.
- In the Dataset and Query Dialog, select theClarityDWH Development data adapter that you created earlier.The "Getting metadata forClarityDWH 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".
- 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.
- 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.
- Click theData Previewtab to verify the data 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 tojava.sql.Clob,java.sql.NClob, andjava.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 correspondingjava.sql.xclass type.
- When working with Oracle database SYSTIMESTAMP columns in a report query, you must modify the Class Type for each field tojava.sql.Timestamp. The Read Fields button available on the Dataset and Query Dialog usesoracle.sql.TIMESTAMPTZclass type as the default for SYSTIMESTAMP columns. After creating fields using the Read Fields button, replace this class type with thejava.sql.Timestampclass type.
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 in 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.