Large data mode

This topic describes how to run Excel reports in the large data mode.

What is large data mode

When filling the Excel template with the report data, the Excel reports engine needs to load the whole XLSX file into memory to allow complex document manipulation. As a result, if you have a lot of data to insert into your template, you risk poor performance and running out of memory. Using the large data mode, you can insert data into the Excel report file row by row in an efficient way, without loading the whole XLSX file into memory.

However, using large data mode has the following limitations:

  • Any features of the third-party JETT are not supported, including scripting, placeholders, tags, and cells formatting.
  • You can insert the data only at the bottom of the first spreadsheet of the workbook;
  • You can insert only one list of values in a given workbook.

As a result, the large data mode is typically used to output large amounts of raw data that will be filtered, formatted, or rendered as charts on other spreadsheets of the workbook by leveraging all the features of Excel reports.

Note: Another option to fill the Excel template with huge amounts of data is using a Java report streaming class. See Use a Java report streaming class for details.

Back to top

When to use large data mode

Consider the following factors as when to use the large data mode:

  • PPM server hardware configuration. This impacts the template rendering time.

  • PPM server heap memory. More memory means that larger amount of data can be inserted in the template without memory issues.

  • Excel template complexity. If you use lots of formatting on the data you insert in the template, this will result in a much bigger XLSX file and more memory usage.

We recommend that you consider using large data mode when you have more than 10,000 rows of data in the Excel report.

Back to top

How to use large data mode

To use the large data mode, you need to meet the following conditions:

  • An object is available in the template containing a collection of all the objects to be inserted in the large data mode.

    This object can be created in a scripting block of the template, or added by a Java preprocessor.

    The default name of this object is largeData. You can modify the name by setting the LARGE_DATA_OBJECT_NAME parameter in the command steps of the report type.

  • Cells in the last line of any spreadsheet of the Excel template contain the names of the attributes of the large data collection objects to be inserted, enclosed in $$.

    Example: The last line should be like the following:

    $$name$$ $$address$$ $$phone$$

    If the data to be inserted is in a different object than largeData or the object defined in the LARGE_DATA_OBJECT_NAME parameter, you should add the object name before the attribute name in any $$ cell, with a space between the object name and the attribute name. For example:

    $$<yourOtherLargeDataObjectName> address$$.

    You only need to specify the object in a single $$ cell.

    If, for any reason, you add different object names in different $$ cells of the same line, the object defined in the last $$ cell prevails.

    Note: It is very important that no empty cell exists after the last $$ value. If you select the first $$ cell of the last line and press CTRL+SHIFT+<END>, it should only select all the $$ values on the last line. No empty cell should be selected on the right or below that line. Failing to do so results in a NullPointerException message.

Back to top

Example of large data Excel report

This example shows how to create a new Excel report in the large data mode that lists all sequenced task names and the names of the work plan they belong to:

  1. Create a new report type with no report field, with the following command steps:

    ksc_run_excel_report reports/largedata/SampleLargeDataTemplate.xlsx
REPORT_ID=[RP.REPORT_SUBMISSION_ID]
ksc_end_report_parameters

  2. Create an Excel template where a block of script code will save the result of the SQL query, listing all tasks in the largeData variable.

    The last line of the spreadsheet contains the $$large data mode$$ placeholder to insert the data.

You can find this template in Samples\5 - 'Large Data' mode\1 - Template for 'Large Data' mode.xlsx.

Note the following:

  • $$large data mode$$ cells are located on the last line of the first spreadsheet. Make sure that only three $$ cells are selected by selecting the first $$ cell and pressing CTRL+SHIFT+<END>.
  • As long as you are not in a $$ cell, all the tags and placeholders will work. It is actually possible to dynamically generate the last line with $$ values, because the XLSX template file will first be rendered like any Excel report. If a large data object exists, the report engine will look for $$ cells on the last line of the produced report and will insert the data in the $$ cells using the large data mode.
  • In the template, the largeData object is initialized using a SQL query. The largeData object must be a collection; each object in the collection must be either a ResultSetRow (the execQuery() method that returns a List<ResultSetRow>) or a Map<String, Object>. If using a Map<String, Object>, the $$ MY_VALUE $$ cell will be replaced by the value of invoking map.get(“MY_VALUE”).

  • The largeData object is initialized with the data from the SQL query with the ${{ … }} placeholder rather than the standard ${…} placeholder.

    The only reason is that the cell value will be filled with the value returned by the first expression in the placeholder. If you only have the code ${ largeData = ppmdb.execQuery(…) }, the cell is filled with the result of the SQL, or more exactly with the dump to strings of all the objects in the collection, which is too much for Excel to handle.

The result of running this report on PPM can be found in Samples\5 - 'Large Data' mode\2 - Created Report with 'Large Data' mode.xlsx.

Back to top

See also: