5_2:spreadsheet_table_reporting_reference

KB0000390 Spreadsheet Table Reporting Reference

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

Formatting Table Data

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.

Implicit Cell Styles

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

Item Description
type E.g. 'number' or 'date'
excel_color The text color in HTML format.
excel_backgroundcolor The cell background in HTML format.
excel_borders The excel cell borders converted to CSS format.
excel_originalnumberformat The original number or date format set in the spreadsheet.
excel_numberformat A translated number format for use in the output report.
excel_textalign Text alignment e.g. 'left', 'centre', 'right'.
excel_style Some font styles in CSS format: font-weight, font-style.

Explicit Cell Styles

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:

Item Example Description
inline_color [attribute color=“#FF0000”] The text color in HTML format.
inline_backgroundcolor [attribute backgroundcolor=“#FF0000”] The cell background in HTML format.
inline_borders [attribute border-top:1px solid #000000;“] The cell borders in CSS format.
inline_textalign [attribute textalign=“left”] Text alignment e.g. 'left', 'centre', 'right'.
inline_style [attribute style=“font-size:12pt;”] CSS Styles
inline_class [attribute class=“myclass” CSS Class

Number and Date Formats

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>	

Special Cell Directives

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

Item Description
[url] Display the cell contents as a clickable URL.
[img] The cell contains the HTTP source of an image to be displayed.
[exclude] Exclude this cell.
[formatonly] Only display the cell format, not the value.

Overriding Cell Styles for Project and Portfolio Reporting

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:

Instruction Description
include Include the table at this level i.e. project, portfolio.
exclude Exclude the table at this level.
color Include the text color.
backgroundcolor Include the background color.
borders Include the borders.
textalign Include the text align style.
style Include the CSS style.
class Include the CSS class.
nocolor Exclude the text color.
nobackgroundcolor Exclude the background color.
noborders Exclude the borders.
notextalign Exclude the text align style.
nostyle Exclude the CSS style.
noclass Exclude the CSS class.

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]

Merging Tables for Portfolio Reporting

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.

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 include] [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
[first] First Example E F G

Column Directives for Merging

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

Row Directives for Merging

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

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

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>
5_2/spreadsheet_table_reporting_reference.txt · Last modified: 2019/03/03 08:09 by admin