2_5:kb2510250

KB2510250 Portfolio Reporting Features in 2.5

PIAB 2.5 has new reporting features aimed at aggregating custom spreadsheet reporting at the porfolio level. You can now 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.

How To Guide

Download the guide for a walkthrough and examples:

how_to_use_2_5_portfolio_report_extensions.pdf

Reference

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.

Merging Tables

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

[portfolio treatment] [hide] Value [label] Item A Item B Item C
[add] Add Example 100 200 300
[multiply] Multiply Example 5 6 7
[average] Average Example 0.2 0.2 0.3
[min] Min Example 70 71 72
[max] Max Example 70 71 72
[exclude] Exclude Example A B C

Instruction Codes for Merging

  • [portfolio treatement] - Nominates the column containing the aggregation instructions.
  • [hide] - Hide a column.
  • [label] - Indicates that the column contains name labels. Normally containing string values, these are not subject to the merging instructions.
  • [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.
  • [exlcude] - Do not show the cell value.

Appending Tables

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

[portfolio treatment] [hide] [remove blank rows] Value [label] Item A Item B Item C [sort asc]
[append row] Name 1 100 200 300
[append row] Name 2 5 6 7
[append row] Name 3 0.2 0.2 0.3

Instruction Codes for Appending

  • [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]

Table Names and Cell Ranges

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:

Type Example Report Table Name
A worksheet and cell range “Export Data” “A1:D5” spreadsheet.xls (Export Data.A1:D5)”
A named range in the spreadsheet “NamedRange1” spreadsheet.xls (NamedRange1)”
A 'Template Range' “!Portfolio Risk” “Porfolio Risk”

Template Ranges

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>
2_5/kb2510250.txt · Last modified: 2017/06/22 13:13 (external edit)

Page Tools