Create Excel reports

This topic provides an example of how to create an Excel report step by step. This example report lists all PPM resources, with their full name, manager full name, department, start date, end date, and category.

In this topic:

Create a report type in PPM workbench

Before you can create an Excel report, you must first create a report type for your Excel report in the PPM workbench.

You can directly import the report type from Samples/1 – Your First Report/excelReport_ReportType.zip or create the report type in PPM workbench.

To import the report type:

1. Copy the Samples/1 – Your First Report/excelReport_ReportType.zip zip file in the <PPM_HOME> directory.

2. In the <PPM_HOME>/bin directory, run the following command:

   sh ./kMigratorImport.sh -username <username> -password <password> -action import -filename excelReport_ReportType.zip -i18n none -refdata nochange -flags NNNNNNNNNNNNNNNNNNN

   Make sure to replace the username and password with your values.

To create a report type in PPM workbench:

1. Create a new or open an existing report type. For details, see Report types.

2. Complete the general information for the report type as follows. You can make changes as needed.

   Field name	Value
   Report Type Name	Resources Details (Excel)
   Reference Code	RESOURCES_DETAILS_EXCEL
   Description	"Resources Details" Excel report
   Enabled	Yes
   Report Category	Resource Management

3. On the Fields tab, create a field and configure the following settings for the field:

   

   Field name	Value
   Prompt	Report Title
   Token	Title
   Component Type	Text Field
   Validation	Text Field - 400

4. On the Commands tab, click New Cmd and enter the following information for the command. You can change everything except the steps.

   Field name	Value
   Command	Run "Resources Details" Excel report
   Timeout (s)	3000
   Enabled	Yes
   Steps	Enter the following command steps: ksc_run_excel_report reports/rm/ResourcesDetailsTemplate.xlsx REPORT_ID=[RP.REPORT_SUBMISSION_ID] ksc_end_report_parameters These command steps are very important to properly run the Excel report. They indicate what excel template should be used to render the report. The second line is also mandatory and is used to pass the report ID to the Excel report engine. It should never be omitted nor modified. It is possible to add any number of arbitrary parameters between the first and the last line. All these parameter values will be passed to the Excel template when the report is generated. For example, to pass a parameter TIME_PERIOD with value weeks, you can use the following command steps: ksc_run_excel_report reports/rm/ResourcesDetailsTemplate.xlsx REPORT_ID=[RP.REPORT_SUBMISSION_ID] TIME_PERIOD=weeks ksc_end_report_parameters

Back to top

Obtain the Excel template

This section describes how to obtain the Excel template.

Overview of the Excel template

The Excel template is an XLSX file that looks similar to the final report. The Excel template contains placeholders that will be filled with data when the report is generated.

The Excel template can also contain scripts to execute arbitrary code. Scripting in the template offers the same features as code run from a JSP file. However, we recommend limiting the use of scripts because it is not easy to maintain code in the template and tedious to troubleshoot any syntax problems or bugs.

All the formatting defined in the Excel template (such as font, style, and cell format) is usually preserved when the report is generated. The other Excel objects (such as tables, charts, and pivot tables) are left untouched during the report generation.

From the template, it is possible to retrieve data from PPM dashboard data sources, and to run SQL queries against any database (PPM database, PPM Reporting database, and arbitrary external database reachable from PPM Server through JDBC).

Create the Excel template

In this example, you will get a final report that looks like the Samples/1 – Your First Report/1 - Target report.xlsx file. All you need to do is to add placeholders in the template. At the report run time, PPM replaces the placeholders in the template and transforms the template into the final report.

Follow the steps below to add placeholders in the template:

• Add the ${TITLE} placeholder right after the report title. This placeholder will be replaced by the value provided in the Report Title field when submitting the report.
• Add the ${RPT_TIMESTAMP} placeholder in place of the report creation time. This is a built-in token that is passed by PPM to the template and contains the report creation time.
• Add the ${TITLE} placeholder in the Spreadsheet Name tab. The Excel Spreadsheet is named with the value provided in the Report Title field when submitting the report in PPM.

After you add the placeholders in the template, the template looks like the Samples/1 – Your First Report/2 -Template with simple placeholders.xlsx file.

For details on how to list all the names that can be used as placeholders in your Excel template, see Debug mode.

Store the Excel template files

The path to the Excel template and the template file name must match the path used in the command steps of the report type. In this example, the following path is used: reports/rm/ResourcesDetailsTemplate.xlsx.

You need to rename the template file to ResourcesDetailsTemplate.xlsx and store the template in the following in the reports/rm diretory.

By default, the Excel template files are stored in the <PPM_HOME>/conf/custom_excel_templates directory. In a clustered environment, a template file must be copied to every PPM_HOME directory in the cluster. This is tedious to maintain. Therefore, if your PPM is running in a clustered configuration, we recommend that you set the EXCEL_TEMPLATES_PATH parameter to a shared folder where the Excel templates are stored. This allows to store the templates in one single folder. Moreover, this enables the report creators to access and modify the Excel templates without giving them the access to the PPM server file system.

We recommend that you create a folder under the custom_excel_templates folder (such as Reports), and organize your templates in sub-folders by module or business department.

Back to top

Get data to be filled in the Excel report

Before running the Excel report, you should get data to be filled in the Excel report. You can retrieve data directly from PPM dashboard data sources, or run SQL queries against any databases, including PPM database, PPM Reporting database, and arbitrary external database reachable from PPM server through JDBC.

Get data from PPM dashboard data sources

If you have dashboard data sources already defined in the workbench with the relevant data and filters, or if you prefer not to write hard-to-maintain SQL in the Excel template, you can retrieve data directly from dashboard data sources by using the dashboard data source name in the Excel template.

To get data directly from a dashboard data source, insert the following placeholder in the Excel template:

${datasources.get('My Datasource Name').getData()}.

If you use more than one language on your PPM instance, this method only works with the data source name defined in the language of the user running the Excel Report. If you want to run the report in multiple languages, use the method getFromId() that uses the data source ID instead of the data source name as a parameter. For example:

${datasources.getFromId(34567).getData()}

To get the data source ID, run the following SQL query on PPM database:

SELECT DATA_SOURCE_ID FROM KDSH_DATA_SOURCES WHERE DATA_SOURCE_NAME = '<Insert the name of your datasource here>'

You can also add filters to the data source by calling the method addFilter(‘FILTER_NAME’, filter_value) as many time as needed before calling the method getData().

Get data by running SQL queries

You can also insert SQL queries in the Excel template to get data to be filled in the Excel report.

In this example, the following SQL query (available in Samples/1 – Your First Report/4 – SQL to retrieve resources details.sql) returns the expected results:

SELECT 
  USERNAME,
  FULL_NAME,
  START_DATE,
  END_DATE,
  DEPARTMENT_MEANING DEPARTMENT,
  MANAGER_FULL_NAME MANAGER,
  RESOURCE_CATEGORY_MEANING CATEGORY,
  roles.ROLE_NAME ROLE
FROM KNTA_USERS_V u,
  RSC_RESOURCES r,
  RSC_ROLES roles
WHERE RESOURCE_FLAG = 'Y'
AND u.ENABLED_FLAG = 'Y'
AND r.user_id       = u.user_id
AND r.PRIMARY_ROLE_ID  = roles.ROLE_ID (+);

To use SQL queries to retrieve data, insert the following placeholder in the Excel template:

${ppmdb.execQuery('SELECT u.USER_ID, USERNAME, FULL_NAME, START_DATE, END_DATE, DEPARTMENT_MEANING DEPARTMENT, MANAGER_FULL_NAME MANAGER,  RESOURCE_CATEGORY_MEANING CATEGORY,  roles.ROLE_NAME ROLE FROM KNTA_USERS_V u,  RSC_RESOURCES r,  RSC_ROLES roles WHERE RESOURCE_FLAG = \\'Y\\' AND u.ENABLED_FLAG = \\'Y\\' AND r.user_id = u.user_id  AND r.PRIMARY_ROLE_ID  = roles.ROLE_ID (+)')}

Consider the following when executing SQL queries in the template:

• Any SQL query inserted in the template must be on a single line.
• The SQL query is delimited by single quotes (‘). Every single quote used in the query must be escaped by prefixing two backslashes (\\).

• You can pass parameters to a SQL query by inserting a question mark (?) in the query where the parameters stand, and pass as parameters values as extra parameters of the execQuery() method.

  For example, if you have a report field using a validation returning the resource categories and you pass the validation in the P_RESOURCE_CATEGORY token, you can filter results in the SQL using the following code:

  ${ppmdb.execQuery('SELECT * FROM knta_users_v WHERE RESOURCE_CATEGORY_CODE = ?', P_RESOURCE_CATEGORY)}

  For more information about the JETT, the third-party library used by Excel reports to fill in templates, see the JETT web site.

  To iterate over each result returned by the SQL, use the <jt:forEach> template tag at the start and end of the line where resources info will go. This tag stores the result of the SQL query in a variable resource, and then use placeholders in each cell to display values of the different columns returned by the SQL query. For example, the resource full name is returned in the column FULL_NAME, use the placeholder ${resource.FULL_NAME} in the cell where you want to insert the resource full name. You can find the example template in the Samples/1 – Your First Report/5 – Template with SQL query.xlsx file.

  For details about the forEach tag, see the JETT web site.

  After replacing the template on the PPM server with this template and re-running the report, the produced Excel report can be found in Samples/1 – Your First Report/6 - Created Report with SQL query.xlsx.

Run SQL queries against PPM reporting database

If you have advanced reporting requirements, you can run SQL queries against PPM operational reporting database. It is not only faster and lightens the load on PPM production database, but also results in much simpler SQL because the data is already aggregated in facts and dimensions tables.

For example, the SQL query mentioned above which lists all resources hits two tables and one complex view (KNTA_USERS_V, leveraging 12 database tables). However, running the same SQL query against the PPM reporting database only needs to get data from one single table (RPT_DIM_RM_RESOURCES) that includes all resources information.

To run SQL queries against the PPM reporting database, in the placeholder where the SQL query is executed, replace ppmdb with reportingdb. For example: ${reportingdb.execQuery('SELECT * FROM RPT_DIM_RM_RESOURCES')}.

Run SQL queries against external database

You can also run SQL queries against arbitrary external databases reachable by PPM server through JDBC.

Use either of the following approaches to run SQL queries against an arbitrary database:

• If it is acceptable to store the DB user name and password in the Excel template, use the following:

  ${externaldb.getJdbcConnection (jdbcUrl, username, password).execQuery(…))}.

  The user name and password are optional parameters. The drawback of this approach is that everyone with access to the Excel template will know the user name and password. Moreover, if the database information (JDBC URL, user name, password) changes, you need to edit your templates.

  This solution targets the non-Oracle databases. To use this solution, make sure that the JDBC driver used to connect to the external database is on PPM classpath (for example, in <SERVER_HOME>/deploy/itg.war/WEB-INF/lib ).

• Create a JNDI data source in PPM server

  To create a JNDI data source in PPM server:

  1. Copy the itg-ds.xml file located in the <SERVER_HOME>/deploy directory.

  2. Edit the content in the file to match your database.

     Remove the <security-domain> element. Add the <user-name> and <password> elements in the JNDI data source definition.

     The new file name must end with -ds.xml and it must be located in the <SERVER_HOME>/deploy/ directory.

  3. Run the kUpdateHtml.sh script.
  4. Restart PPM.

  This approach has the following limitations:

  • PPM does not support encryption of user name and password in the custom JNDI data source.

  • You can only create JNDI data sources that target the Oracle databases.

    When the JNDI datasource is added, you can query it from the Excel template using the following:

    ${externaldb.get(JNDI_name).execQuery(…))}.

Back to top

Run the Excel report

You can run the Excel report from the PPM menu.

To run the Excel report:

1. From the PPM menu, select Create > Report,

2. Select Resource Management (or whatever report category you set for the report type).

3. Click the report type name (Resources Details (Excel) in this example).

4. In the Report Title field, enter a title for the report (my first report for example), and then click Submit.

   

   When the report is completed, you can download an XLSX file containing the report.

   You can find the expected result file in Samples/1 – Your First Report/3 - Created Report with simple placeholders.xlsx.

Back to top

See also:

• Run reports