Example of Large Data Excel Report

This example shows how to create a new Excel report in 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.

    Last line of the spreadsheet contains the $$large data mode$$ placeholders to insert the data.

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

There are a few things to note about this template file:

  • $$large data mode$$ cells are located on the last line of the first spreadsheet. Verify that only the 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, and 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 there using 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 method execQuery() returns a List<ResultSetRow>) or a Map<String, Object>. If using a Map<String, Object>, a cell $$ MY_VALUE $$ 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 whatever value is 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.