3_0:kb2010320

KB3010320 Portfolio Reporting Features in 3.0

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.

Merging Tables from Many Projects

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.

Example Table Layout

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

Column Instructions

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.

Row 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.

Cell Instructions

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.

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]

Formatting Output with HTML and CSS Styles

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.

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>
3_0/kb2010320.txt · Last modified: 2017/06/22 13:13 (external edit)

Page Tools