Applies to: 3.0.1.002

PIAB has reporting features aimed at aggregating custom spreadsheet reporting at the porfolio level. You can put instructions codes into your spreadsheets that tell PIAB how you want the data to be handled. Same-named tables can be aggregated between projects in various ways.

This feature was present in previous version, and has been enhanced in release 3.0.1.002.

To merge or append data from tables from different projects, the following criteria must be met:

1. The tables have the same name in the output report (see 'Table Names and Cell Ranges' below) which normally means ensuring they are from equivalent spreadsheets and cell ranges in the different projects.
2. The first cell of one column must contain the instruction code [portfolio treatment].
3. That column must contain merge instructions e.g. [add], [multiply] etc.
4. The structure of the table must be the same between projects.

Note that there are two kind of aggregations: merging and appending. In merging, the cells of equivalent tables are merged according to the instructions e.g. add, min, max. In appending, an output table is built up by appending the rows from each of the equivalent tables.

To merge two or more tables from different project, your spreadsheet tables need to be laid out as in this example format:

These codes are used in the top cell of a column, and apply to the column:

• [portfolio treatement] - Nominates the column containing the aggregation instructions.
• [hide] - Hide the whole column.
• [label] - Indicates that the column contains name labels. Normally containing string values, these are not subject to the merging instructions.

These codes are used in the [portfolio treatment] column, and apply to the whole row.

• [first] - Use the first non-blank value.
• [exclude] - Do not show the cell value or attributes
• [formatonly] - Just use the formatting attributes.
• [add] - Add cells.
• [multiply] - Multiply cells.
• [average] - Take the average of all cells.
• [min] - Take the minimum value across projects.
• [max] - Take the maximum value across projects.
• [count] - Count all the non-blank cell values.
• [countunique] - Count the unique cell values, and display each value with its number of repetitions e.g. 'high(3) med(2) low(6)'
• [concatenate] - Concatenate the cell values in the order they are processed.

These codes apply to individual cells:

• [exclude] - Do not show the cell value or attributes
• [formatonly] - Just use the formatting attributes.
• [url] - The cell contains a URL, and will be displayed as a hyperlink.
• [img] - Loads an image from a location, specified by a hyperlink.

To append rows from two or more tables from different project, your spreadsheet tables need to be laid out as in this example format:

• [remove blank rows] - Remove rows where the all the value and label cells are blank.
• [append row] - Append this row to the output table.
• [sort asc] - Sort on this column, numeric and ascending by default.

Other sort instructions:

• [sort desc] - Sort descending.
• [sort asc string] - Sort ascending on the string value.
• [sort desc string]
• [sort asc date] - Sort descending on the date value.
• [sort desc date]

HTML attributes can be exported with the cell data using the syntax:

<cellvalue> [attribute <name>=“<value>”]

e.g. the following cell has a value of 12.4 and a 'class' attribute set:

12.4 [attribute class="background_red"]

this translates to the following HTML output:

<td class="background_red">12.4</td>

You can edit the report CSS stylesheet (report.css) to apply personalised styles to this class.

There are various ways to specify the cell ranges of spreadsheet tables in the Project Summary Information form. These create report tables with different name formats:

Note the use of the ! character in the 'Template Range' name example. A 'Template Range' is one that you specify in the project template itself. It is a definition of a cell range in the spreadsheet, for example:

<spreadsheetnamedranges>
<range name="Portfolio Risk" worksheet="export data" cellrange="A1:D9" />
..
</spreadsheetnamedranges>