Install and Configure Advanced Reporting with Jaspersoft

Complete the following steps to set up Advanced Reporting with Jaspersoft for cappm. Configure Jaspersoft to restrict email notifications to trusted domains. Configure a dedicated JasperReports server for scheduled reports.
ccppmop151
Complete the following steps to set up Advanced Reporting with Jaspersoft for
CA PPM
. Configure Jaspersoft to restrict email notifications to trusted domains. Configure a dedicated JasperReports server for scheduled reports.
2
Advanced Reporting Architecture and Components
To help you plan for a new installation or upgrade with Advanced Reporting, refer to the deployment architecture examples in the sizing guide for
CA PPM
15.x. See
Installing and Upgrading
in the English edition of the documentation. The following diagram shows a typical configuration:
This diagram shows a typical three-node Jaspersoft cluster for CA PPM
This diagram shows a typical three-node Jaspersoft cluster for CA PPM
For the best performance, we recommend the following minimum configuration:
  • 20 GB RAM for each JVM
  • 32 GB RAM for the OS
  • One (1) interactive node
  • One (1) scheduler node
For large deployments with over 1,000 simultaneous users, we recommend a three-node Jaspersoft cluster with one interactive node and two scheduler nodes. 
High-Performance Specifications
The following section lists a robust setup for Windows Server 2012 for user-acceptance testing or high-performance environments that place a premium on reporting. The CA PPM 15.x SaaS platform uses similar specifications in a three-node clustered configuration.
PPM App Server
Dual 2.4 GHz Intel Xeon
RAM
: 8 GB
PPM DB Server
MS SQL 2012
8 Core 2.4 GHz Intel Xeon
RAM
: 100 GB
DWH/Jaspersoft Reports Server
MS SQL 2012
6 Core 2.4 GHz Intel Xeon
RAM
: 14 GB
Jaspersoft Tomcat Server
Dual 2.4 GHz Intel Xeon
RAM
: 8 GB (32 GB recommended)
JVM
: 20 GB per node
Review the Prerequisites
  • Verify that 
    CA PPM
     is installed or upgraded. For more information, see
    Installing and Upgrading
  • Download the latest supported JDK and Tomcat software from Oracle and Apache. Set JAVA_HOME and JRE_HOME and ensure that the correct Java 8 and JRE 8 executables are referenced in the PATH environment variable.
    For example: (Windows)
    set PATH=%JAVA_HOME%\bin;%PATH% 
    (Linux)
    export PATH=$PATH:$JAVA_HOME/bin
For the software versions that are compatible with
CA PPM
, see the.
  • Extract the contents of the installer, preferably into the root drive (for example, C:\jsftinstall  or /<directory/jsftinstall ) to avoid the “file name is too long" exceptions during the upgrade process.
  • If this is your second attempt at an installation, before starting the install process, do the following:
Create the JasperReports Server Schema and Set Permissions
Complete the following steps to create a database user for the JasperReports Server.
Oracle
Follow these steps:
  1. Create a Tablespace for the JasperReports Server.
    CREATE TABLESPACE "[JS User]_PPMJAS" DATAFILE '<ORACLE_BASE>\ORADATA\<SID>\PPM_JASPER_TS.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 25M MAXSIZE 10G LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
  2. Create a database user and set the required permissions. 
    CREATE USER [JS User] IDENTIFIED BY [JS Password] DEFAULT TABLESPACE [JS User]_PPMJAS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE, CREATE SESSION TO [JS User]; GRANT UNLIMITED TABLESPACE, QUERY REWRITE TO [JS User];
SQL Server
Follow these steps:
  1. Create a database (for example, PPM_JASPERSOFT).
  2. Alter database ppm_jaspersoft to set arithabort on.
  3. Alter database ppm_jaspersoft to set ansi_nulls on. 
  4. Alter database ppm_jaspersoft to set quoted_identifier on.
    IF (SELECT (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) = 10 BEGIN exec sp_dbcmptlevel 'PPM_JASPERSOFT', @new_cmptlevel=100 END IF (SELECT (CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), CHARINDEX('.', CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(20)), 1) - 1) AS INTEGER))) = 11 BEGIN exec sp_dbcmptlevel 'PPM_JASPERSOFT', @new_cmptlevel=110 END IF NOT EXISTS (SELECT 'yes' FROM syslogins WHERE LOWER(name) = LOWER('PPM_JASPERSOFT') ) BEGIN exec sp_addlogin 'PPM_JASPERSOFT','my_password','PPM_JASPERSOFT','us_english' END
  5. (SQL Server 2005) Set the isolation level to 
    read committed snapshot
    Make sure that the user can access the system views (such as syslockinfo, sysindexes).
    begin exec ('alter database PPM_JASPERSOFT set read_committed_snapshot on') exec ('grant VIEW SERVER STATE to PPM_JASPERSOFT') exec ('ALTER LOGIN PPM_JASPERSOFT WITH CHECK_POLICY=OFF') end
    USE PPM_JASPERSOFT
    The schema owner is referenced in the code when executing functions in SQL Server and is hard-coded to PPM_JASPERSOFT. The login name can be different.
    -- Schema owner is referenced in code when executing functions in MSSQL, -- so we intentionally hard-code it here to PPM_JASPERSOFT. Login name can differ. exec sp_grantdbaccess 'PPM_JASPERSOFT','PPM_JASPERSOFT' exec sp_defaultdb 'PPM_JASPERSOFT', 'PPM_JASPERSOFT' exec sp_addrolemember 'db_owner', 'PPM_JASPERSOFT'
Install the JasperReports Server
Complete the following steps to install the JasperReports Server.
Follow these steps:
  1. Download the Jaspersoft zip file:
    1. Log in to CA Support Online and navigate to the
      CA PPM
       product support page.
    2. Under Quick Access, click Download Center. Search and locate the latest download file (for example, 
      ca_ppm_jaspersoft_6.2.1_5.2.1.54.zip
      or
      ca_ppm_jaspersoft_6.4.2_5.4.2.15.zip
      ). The file is often part of an ISO image file that you can download and mount or burn to a DVD.
  2. Save the zip file to a local directory.
  3. Unzip the contents of the file to a new folder (for example,
    /opt/jasperreports
    in Linux or
    C:\Jasperreports
    for Windows). 
    Unzip the installer into the root directory to avoid the
    file name is too long
    exception (for example, C:\jaspersoft or /fs0/).
  4. Open a command prompt and run the following command: 
    Linux
    :
      chmod u+x install.sh
      install.sh
    Windows:
      install.bat
  5. Select the Installation Type.
    • New
      This option installs the new JasperReports Server version that you downloaded in an earlier step.
  6. Enter the Apache Tomcat directory (the location where the JasperReports Server is deployed and Tomcat is installed). Install the JasperReports Server in the JasperReports Server Tomcat folder.
  7. Enter the following information:
    • Enter Jaspersoft web app name
      . Specifies the folder name where the JasperReports Server is deployed. For example, if reportservice is the JasperReports Server web context, the URL to access the JasperReports Server is
      http://<domain_name>:<portnumber>/reportservice
      .
    • Enter Jaspersoft web app port
      . Specifies the port on which the JaspersoftReports Server runs (for example, 8080).
  8. (Optional) Enter the following information to configure the mail settings: 
    • Do you want to configure Email (y/n)?
      Select Y to configure the mail server.
    • Enter Mail Server Hostname
      Specifies the hostname of the mail server (for example, mail.localhost.com).
    • Enter Mail Server Port
      Specifies the port number of the mail server (for example, 25).
    • Enter Sender Email Protocol
      Specifies the protocol that the mail server supports. The JaspersoftReports Server supports smtp and smtps (for example, exchange).
    • Enter Sender Email Username
      Specifies the user name of the mail sender (for example, test).
    • Enter Sender Email Password
      Specifies the password of the mail sender (for example, password123).
    • Enter Sender Email Address
      Specifies the mail address of the sender (for example, [email protected]). For more information, see JasperReports Server Sender Email Address.
  9. Select the Installation Mode.
    • Standalone (for standalone servers) or Cluster (for Cluster-based deployments)
      • Primary Node
      • Additional Node (Web-app deployment only): Deploys the web app only using the Primary Node Database/Schema.
        If you are deploying an additional node, provide the database details for the Primary Node.
  10. Do you need a dedicated Jaspersoft instance as a report scheduler instance?
    Yes
    . Choose this option if you do not want to have a separate scheduler instance.
    No
    . Choose this option if you want a separate scheduler instance.
    Creation of a separate scheduler node is only applicable if you are installing Jaspersoft in the Cluster installation mode. You only see this option when you select Cluster in the previous step.
  11. Do you want to make the current instance as report scheduler only instance?
    Yes
    . Choose this option to have the installer perform the necessary configuration changes and make the current instance a scheduler instance.
    No
    . Choose this option for instances that are part of the load balancer. The installer performs the necessary configuration changes and makes the current instance a non-scheduler instance.
    The JasperReports Server can be deployed as a standalone application or in a cluster for high concurrency. Clustered JasperReports Server instances will use thread-safe access to their private repository database. As a result, any number of JasperReports Server instances can share the same repository and present the same environment to users. When the JasperReports Server is deployed in
    cluster
    , we advise you to dedicate one or more JasperReports Server instances in the cluster to process the execution of scheduled reports. The dedicated instances will share the same repository. However, they will not be part of the load balancer to serve web requests such as executing reports through the user interface and exploring data through ad hoc views. For a
    standalone deployment
    where only a single instance of the JasperReports Server is available, the instance will process report scheduling. The instance will also serve normal web requests such as report execution through the user interface and exploring data through ad hoc views.
    You must select the appropriate option during the installation to have the installer make the instance either a scheduler or non-scheduler instance. You only see this option when you select Yes in the previous step. As previously noted, all of the scheduler nodes should be removed from the load balancer configuration. When you do this, the web requests are not served by scheduler nodes. The only purpose of scheduler nodes is to run scheduled reports.
  12. Select the Database Server Type and provide the required information in the following table. Verify the properties that you entered and wait until the installation completes.
    Database
    Prompt
    Value
    MSSQL
    SQL Server Hostname
    Enter the Hostname of the server where SQL Server is installed (for example, HOSTNAME).
    Do you want to use named instance?
    Choose 
    Yes
     if you are deploying on a MS SQLSERVER Named Instance. Choose
    No
    if you are deploying on a default port instance.
    Choose
    Yes
    to install on a MS SQLSERVER Named Instance.
    -Enter MSSQL Server Instance Name
    Enter the named instance configured on MS SQLServer.
    -Jaspersoft Database Name
    Enter the JasperReports Server database name (default=PPM_JASPERSOFT).
    -Jaspersoft Database Username
    Enter the JasperReports Server user name (for example, PPM_JASPERSOFT).
    -Jaspersoft Database Password
    Enter the JasperReports Server password. Only applicable for Oracle.
    Choose 
    No
     to install on a MS SQLSERVER default instance with a static port.
    -MSSQL Server Port Number
    Enter the database port on which SQL Server runs (for example, 1433).
    -Jaspersoft Database Name
    Enter the JasperReports Server database name (default=PPM_JASPERSOFT).
    -Jaspersoft Database Username
    Enter the JasperReports Server user name (for example, PPM_JASPERSOFT).
    -Jaspersoft Database Password
    Enter the JasperReports Server password. Only applicable for Oracle.
    Oracle (Standalone)
    Oracle Database Hostname
    Hostname of the server where Oracle is installed (for example, localhost).
    Oracle Database Port Number
    Database port on which Oracle is running (for example, 1521).
    Jaspersoft Database Username
    JasperReports Server database user name (for example, PPM_JASPERSOFT).
    Jaspersoft Database Password
    JasperReports Server password.
    Oracle version
    11.2.0.4 or higher
    12.1.0.2 or higher
    Oracle SID Name
    Oracle SID (for example, <ORACLESID>)
    Oracle Cluster (RAC)
    Oracle Database Hostname
    Hostname of the server where Oracle is installed (for example, localhost).
    Oracle Database Port Number
    Database port on which Oracle is running (for example, 1521).
    Jaspersoft Database Username
    JasperReports Server database user name (for example, PPM_JASPERSOFT).
    Jaspersoft Database Password
    JasperReports Server password.
    Oracle version
    11.2.0.4 or higher
    12.1.0.2 or higher
    Oracle Service Name
    Oracle service name (for example, <SERVICENAME>).
Verify the JasperReports Server Installation
Complete the following steps to verify that the installation was successful.
Follow these steps:
  1. Start the JasperReports Server if it has not already started. 
  2. Go to the Jaspersoft Tomcat bin directory (<Jaspersoft-Tomcat-Root>/bin), and run the following command:
    Linux:
    chmod u+x ./startup.sh ./startup.sh
    Windows:
    startup.bat
  3. Open 
    http://<domain_name>:<portnumber>/<jasperwebcontext>/
    .
  4. Verify that you can open the JasperReports Server login page.
  5. Log in to the JasperReports Server using the superuser credentials.
  6. Review the
    install.log
    for any errors. This file is available in the folder where you unzipped the contents of the JasperReports Server installation file.
JasperReports Server Post-Installation Recommendations
  • If you use Oracle, run the following query after the JasperReports Server installation to improve the overall performance of the system.
exec DBMS_STATS.GATHER_SCHEMA_STATS (ownname => USER, degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => 100, cascade => true, method_opt=> 'FOR ALL COLUMNS SIZE 1');
  We recommend that you run this query frequently.
  • The Output to Host File System option saves the report output to a specified folder on the JasperReports Server host server. This option is disabled by default. For information about enabling this option, see the Jaspersoft documentation.
  • The governor limit on the JasperReports Server database connection has a timeout set to 15 minutes (900000 milliseconds). Report queries running longer than 15 minutes close and an error appears indicating the timeout. You can change the setting in the following location:
    webapps/<jasperwebcontext>/WEB-INF/classes/jasperreports.properties
    net.sf.jasperreports.governor.timeout.enabled= true
    net.sf.jasperreports.governor.timeout=900000
    : For better performance and to relieve stress on the database, do not change this setting.
  • The governor limit on the JasperReports Server pages is set to 500. This limit applies to all output formats. You can change this setting in the following location: 
    <tomcatDirectory>/webapps/<jasperwebcontext>/WEB-INF/classes/jasperreport.properties
    net.sf.jasperreports.governor.max.pages.enabled=true 
    net.sf.jasperreports.governor.max.pages=500
    Setting this limit requires a restart of the JasperReports Server.
JasperReports Server Post-Installation Configuration
After you complete and verify the JasperReports Server installation, complete the following steps to configure the JasperReports Server to work with 
CA PPM
. The Advanced Reporting content includes domains for use with the data warehouse. 
Configure the JasperReports Server Properties
Set the server properties for the JasperReports Server in
CA PPM
System Administration (CSA).
Follow these steps:
  1. Log in to CSA.
    The default login URL for CSA depends on the application server that is running 
    CA PPM
    :
    • Apache Tomcat
      http://<
      hostname
      >:8090/niku/app
  2. Click
    Servers
    in the left navigation pane, and click the name of the local server in the list.
  3. With the
    Properties
    tab selected, click the
    Reporting
    subtab.
  4. Complete the 
    Report Server: Jaspersoft
     section:
    • Vendor: 
      Specifies the report vendor. For this section, only Jaspersoft is available.
    • Status: 
      Indicates whether the JasperReports Server is available. If the server status is unavailable either because it is down, the URL is incorrect, or there is a network issue, provide a valid Web URL for the JasperReports Server.
    • Web URL: 
      Specifies the web URL for the JasperReports Server (for example,
      http://<jaspersoft-hostname>:8080/<jasperwebcontext>
      ).
    • User: 
      Specifies the JasperReports Server user name who performs system tasks that require administration access. The default user is ppmjasperadmin. You can change the default user name before you import Advanced Reporting content. The import creates the user.
      Do not
      change the user name after you import the Advanced Reporting content.
    • Database ID: 
      Not applicable.
    • Organization Name: 
      Specifies the organization name (tenant name) that is created on the JasperReports Server.
    • Organization ID: 
      Specifies the organization ID (tenant ID) that is created on the JasperReports Server.
    Note:
     If the
    Report Server: Jaspersoft
    section is not available, configure the following tag in the 
    <clarityInstallFolder>/properties.xml
    file:
    <reportServer id="jaspersoft" webUrl="http://<serverName>:<portnumber>/<jaspersoftwebcontext>" context="/<jaspersoftwebcontext>" home="" serviceUrl="http://<serverName>:<portnumber>" volumeName="" username="ppmJasperAdmin" vendor="jaspersoft" databaseId="Niku" jndiName="jdbc/clarity" dwJndiName="jdbc/dwh" orgName="<orgname>" orgId="<orgID>" />
  5. Click
    Save
    .
Generate the Key Store File
To authenticate 
CA PPM
 and the JasperReports Server, generate a trusted key store for each organization.
If you are upgrading your product release, use the following procedure to regenerate the keys and copy the key store files.
Follow these steps:
  1. Go to <clarity_home>/bin.
  2. Run the following command to generate a key store from the CSA:
    admin jaspersoft keystore
     
    To specify the key and password, enter the following command: 
    admin jaspersoft generate [-key <key> -password <password>]
    These commands generate the following files in <install>/config/ and <install>/META-INF/reporting/store. 
    • <orgname>.jks (key store file)
    • <orgname>.properties (information about the key)
  3. Copy these files to <Tomcat>/webapps/<jasperwebcontext>/WEB-INF/config where Jaspersoft is installed. 
    Example: <Tomcat>/webapps/reportservice/WEB-INF/config
    For clustered environments, copy these files in the same location on each of the nodes in the cluster.
  4. Start the Tomcat service where the JasperReports Server is deployed.
    The Advanced Reporting content cannot be imported without starting the reporting service.
  5. (Optional) Repeat the steps to regenerate the key or change the organization.
  6. Complete the previous steps successfully to ensure that import of the Advanced Reporting content does not fail.
Execute the Installation Commands
Before you run the installation commands, verify that you have completed the following tasks:
  • Data Warehouse status is set to Available. To confirm, refer to the Status field in the Data Warehouse section of CSA.
  • The Data Warehouse database link is created. To confirm, refer to the PPM Database Link field in the Data Warehouse section of CSA.
  • The Organization Name and Web URL are defined. To confirm, refer to the Organization Name and Web URL fields in the Reporting section of CSA.
Follow these steps:
  1. Open a command prompt and navigate to the 
    CA PPM
     runtime bin directory.
  2. Run the following command: 
    admin content csk
    The installation process begins and information displays on the screen. The information is captured and stored in the admin.log file that is located in the logs directory. The output files are stored in the logs/content/xog/csk directory.
  3. Run the following command using the superuser credentials to import the Advanced Reporting content: 
    admin content-jaspersoft csk -userName superuser -password <password>
  4. Review the screen output and the logs for important information after the installation completes.
Configure Users for Advanced Reporting
To set up Advanced Reporting with Jaspersoft, configure users for Advanced Reporting.
Follow these steps:
  1. Assign the following access rights as required to the Advanced Reporting users:
    • Advanced Reporting - Navigate. 
      Allows a user to navigate to Advanced Reporting and use the tools as a Jaspersoft user.
    • Advanced Reporting - Administer. 
      Allows a user to navigate to Advanced Reporting and use the tools as a Jaspersoft administrator.
    • Advanced Reporting - Ad Hoc Create. 
      Allows a user to navigate to the Advanced Reporting page and create Ad Hoc Views. The user is assigned the following Jaspersoft roles: ROLE_USER and ROLE_ADHOC_DESIGNER.
    • Advanced Reporting - Dashboard Create. 
      Allows a user to navigate to the Advanced Reporting page and create dashboards. The user is assigned the following Jaspersoft roles: ROLE_USER and ROLE_DASHBOARD_DESIGNER.
    • Advanced Reporting - Data Source Create. 
      Allows a user to navigate to the Advanced Reporting page and create data sources. The user is assigned the following Jaspersoft roles: ROLE_USER and ROLE_DATASOURCE_DESIGNER.
    • Advanced Reporting - Domain Create. 
      Allows a user to navigate to the Advanced Reporting page and create domains. The user is assigned the following Jaspersoft roles: ROLE_USER and ROLE_DOMAIN_DESIGNER.
    • Advanced Reporting - Report Create. 
      Allows a user to navigate to the Advanced Reporting page and create reports. The user is assigned the following Jaspersoft roles: ROLE_USER and ROLE_REPORT_DESIGNER.
  2. Run the Create and Update Jaspersoft Users job to synchronize the 
    CA PPM
     users with assigned access rights to their corresponding Jaspersoft users and roles.
The PMO Accelerator add-in includes additional roles (CSK roles) that set permissions for repository items (such as reports and domains). To configure users for CSK roles, see Advanced Reporting Roles and Permissions.
Restart the 
CA PPM
Services
Follow these steps:
  1. Log in to CSA.
  2. Under
    Home
    , click
    All Services
    .
  3. Select the check boxes next to the
    CA PPM
    Application (app) and
    CA PPM
    Background (bg) services.
  4. Click
    Start
    .
Run the Data Warehouse Jobs
Always run the jobs in order as listed.
Follow these steps:
  1. Run the Create and Update Jaspersoft Users job
    : This job synchronizes the 
    CA PPM
     users with assigned access rights to their corresponding Jaspersoft users and roles.
  2. Run the Time Slicing job
    : This job splits time varying data into a relational format.
  3. Run the Load Data Warehouse job
    : This job populates the data warehouse with data from the 
    CA PPM
     database. This job extracts the data from the database, transforms the data, and loads the data into the data warehouse. This job also updates the domains.
    1. This ETL job is initially disabled. Enable this job before you run it.
    2. Open the properties for any custom object or attribute that you want to include and select the
      Include in the Data Warehouse
      option. This option must be selected for custom objects or attributes to be populated in the data warehouse.
  4. Run the Load Data Warehouse Access Rights job:
    This job loads the access privileges for resources and investments from the database to the data warehouse. This job is initially disabled. Enable this job before you run it.
  • After installation, to populate the data warehouse, always run the Load Data Warehouse job with the Full Load option selected. The first run of this job can take a substantial amount of time.
  • The following events require you to rerun the Load Data Warehouse job:
    • Deleting an attribute or clearing the Include in the Data Warehouse option for the attribute in CA PPM Studio.
    • Changing the data type of an attribute
  • The following events require you to rerun the Load Data Warehouse job with the Full Load option selected:
    • Adding a language to the data warehouse
    • Changing a timeslice date to include a larger timeframe.
When the Load Data Warehouse and Load Data Warehouse Access Rights jobs run, they write standard messages to the jobs logs and detailed messages to the data warehouse logs. The default data warehouse log files are bg-dwh.log and bg-ca.log. To review the detailed data warehouse log messages, navigate to the data warehouse logs (under the standard Jobs logs) in
CA PPM
System Administration. You can enable more logging by changing the Kettle Log Level. Click Servers, Logs, Edit Configuration. Use the detailed messages to monitor job progress and to analyze data warehouse load failures.
Restart the JasperReports Server Tomcat Services
Complete the following steps to stop or start JasperReports Server.
Follow these steps:
  1. Navigate to the <tomcathome>/bin directory.
  2. To stop, run one of the following files:
    • shutdown.sh (Linux)
    • shutdown.bat (Windows)
  3. To restart, run one of the following files:
    • startup.sh (Linux)
    • startup.bat (Windows)
Uninstall JasperReports Server
Follow these steps:
  1. Stop the Tomcat service where the JasperReports Server is deployed.
  2. Rename the existing 
    <jasperwebcontext>
     folder.
  3. Back up the JasperReports Server database.
  4. Delete the JasperReports Server database from the database server.