Prerequisites for Running an Excel Report

A Report Type Defined in PPM Workbench

The report type is similar to a JSP Report, with the only difference that the special command ksc_run_jsp_report <path_to_jsp_file> is replaced by ksc_run_excel_report <path_to_excel_template>.

The report type will define the PPM Validations to be used as input data to the Excel reports, as well as the security model of who can run the report from PPM.

An Excel Template (XLSX File)

The Excel template is an XLSX file that looks similar to the final report, where all the data is replaced by placeholders that will be filled with data when the report is generated.

The 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, it is recommended to limit the use because code located in the template is not easy to maintain and is tedious to troubleshoot in case of syntax problem or bug.

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 Datasources, and to run SQL queries against any database (PPM database, PPM Reporting database, arbitrary external database reachable from PPM Server through JDBC).

(Optional) A Java Pre-Processor

When there are some advanced requirements to process data used in the Report Template, it is tedious to use the scripting features in the template. For this reason, it is possible to define a Java Pre-Processor class that can be run before the report and can feed data to the report. All the data available to the template is also available to the Java Processor, so that you can modify existing data or add new data to the report.

Even though everything done in a Java Pre-Processor can also be done by in-template scripting, it is recommended to rely on a Java Pre-Processor for every non-trivial operation, such as complex SQL Query generation or invocation of an external web service.

(Optional, since 9.40) A Java Report Postprocessor

If you need to use the low-level Apache POI library to manipulate the report XSLX after it was filled by JETT (to add some charts or pictures for example), you can write a Java class that will get the Apache POI Workbook object as an input and let you manipulate it any way you want using the POI XSSF Interface.

Even though such code can be written directly in the Excel Template (as JETT exposes the POI Workbook and Sheet objects and lets you manipulate them using scripting), it is recommended to rely on a Java Report Postprocessor for every non-trivial POI XLSX manipulation.

(Optional, since 9.40) A Java Report Streaming Class

When you have a huge amount of data to include in your report (tens of thousands of rows or hundreds of thousands of cells), you have to stream the data to the XLSX in order to get good performance and low memory usage. The first option is to use the Large Data Mode (documented in Large Data Mode), however, this approach is very limited in usage: data can only be inserted at the end of the first sheet of the workbook, and you cannot change the style of the cells.

If you want full control of the XLSX streaming process, you can use a Java Report Steaming class as an alternative, which will give you access to the workbook through the POI SXSSF API. This API lets you stream data to any spreadsheet of the workbook, and gives you full POI control of the data inserted (including cell style).

It is NOT possible to use both “Large Data Mode” and a Java Report Streaming class or a Java Report Post-Processor. Trying to do so will result in an error.

Keep in mind that the Apache POI SXSSF API is more limited in usage than the XSSF API: you can only add data to existing spreadsheets, and can only manipulate one row at a time. It is possible to first modify the workbook with a Java Report Postprocessor and then insert large amounts of data with a Java Report Streaming class, but it will always happen in that order.

Order of the Excel Report Generation Process

Generating an Excel Report will always go through different phases in the following order:

  1. (Optional) Java Preprocessor gets the data to make it available to fill the template.
  2. JETT fills the XLSX template with the data to create the XLSX report.
  3. (Optional) If a Large Data Mode object name is defined, Large Data Mode is used.
  4. (Optional) Java Report Postprocessor manipulates the report with POI XSSF API.
  5. (Optional) Java Report Streaming class manipulates the report with POI SXSSF API