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:
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:
[portfolio treatment] [hide] | Desc [label] | Item A | Item B | Item C |
---|---|---|---|---|
[first] | Display the first value | one | two | three |
[exclude] | Exclude cells | A | B | C |
[formatonly] | Include formatting but not values | A | B | [attribute style=“color:red;”]Hello World |
[add] | Add values | 100 | 200 | 300 |
[multiply] | Multiply values | 5 | 6 | 7 |
[average] | Average values | 0.2 | 0.2 | 0.3 |
[min] | Display min value | 70 | 71 | 72 |
[max] | Display max value | 70 | 71 | 72 |
[count] | Count non-blank values | A | B | C |
[countunique] | Display and count unqiue values | A | B | C |
[concatenate] | Concatenate strings | A | B | C |
These codes are used in the top cell of a column, and apply to the column:
These codes are used in the [portfolio treatment] column, and apply to the whole row.
These codes apply to individual cells:
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 |
Other sort instructions:
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:
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” |
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>