PIAB has the ability to bring data from spreadsheets into reports. This is a reference guide to these features.

PIAB can read data tables from spreadsheets. It provides features to allow you to format the output so it appears the way you want in reports. Firstly, it attempts to replicate the look of the spreadsheet table e.g. text and background colors, text alignment etc. Secondly it provides a way of overriding the formatting to provide, for example, conditional formatting.

PIAB reads cell style and format information directly from the spreadsheet, which is returned as follows.

Yan override these implicit values with explicit ones using the PIAB inline attribute feature. E.g. the following cell would have return a value of 100.1, with white text on a red background:

  100.1 [attribute color="White"][attribute backgroundcolor="Red"]

The set of inline attributes is as follows:

Spreadsheets like Excel have a set of format codes that you can apply to cells to format numerical data e.g. using accountancy formats. They also have many ways to format dates e.g. standard formats for a region, or custom ones for a specific task. In many cases you will want to translate these specific date formats for suitable display in a report. PIAB provides a way to let you identify a spreadsheet-specific format string and convert it to a suitable output format for display in its HTML reports.

For example, one of the accounting formats in Excel looks like this…

"£"#,##0.00;[Red]"£"#,##0.00

…and PIAB will replace it with the following in the report…

£#,##0.00

The following Excel date format…

DD MM YYYY

…is replaced with the output display format…

dd MM yyyy

These replacements can be specified by clicking the 'Options' link the 'Project Summary Information' form. The can also be permanently set in the the Excel plugin config file:

  [Install Folder]\plugins\excelgembox.cfg

The format is…

<config>
  <options>
    <!-- Date Formats -->		
    <celldateformats>
	<cellformat from='M/D/YY'               to='M/d/yy'/>
	<cellformat from='ddd\ dd\ mmm\ \ yy'   to='ddd dd MMM yy'/>
	</celldateformats>
		
    <!-- Number Formats -->
    <cellnumberformats>		
	<cellformat from='_-"£"* #,##0_-;\-"£"* #,##0_-;_-"£"* "-"_-;_-@_-' to='£#,###0'/>
	<cellformat from='#,##0_ ;[Red]\-#,##0\' to='#,###0'/>
	<cellformat from='_(#,##0_);[Red](#,##0)' to='#,###0'/>
    </cellnumberformats>
  </options>
</config>	

PIAB includes a number of special cell directives e.g.

You can tell PIAB to use or not to use the various cell styles, both at project and portfolio level. This is useful e.g. for using explicit, conditional formatting at project report level, but using implicit styling at portfolio report level. To use this features, one cell must contain the directive:

[project <instruction> <instruction>..<instruction>]

or

[portfolio <instruction> <instruction>..<instruction>]

Where the instructions are:

For example, the following directive would tell PIAB to exclude the table from the project report, but include it in the portoflio report, but ignoring the text color and cell borders from the spreadsheet.

[project exclude][portfolio include nocolor noborders]

PIAB can merge table data from spreadsheets to create an aggregated portfolio table. In order to merge data from tables from different projects, the following criteria must be met:

1. The tables have the same description or 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].
3. That column must contain row merging 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:

• [portfolio] - 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.
• [sort <options>] - Sorts the rows based on the value in this column. See below for sorting options.

• [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.
• [exclude] - Do not show the cell value.
• [first] - Only show the first value.

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]

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. If you specifiy a 'Description' for the table when you define it, then this description value is used to identify the table for merging. Otherwise, the name is generated as in the following table:

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>