Tables

Extended supports tables, as well as various methods for manipulating and styling them. Tables can hold all of the datatypes supported by Extended (numbers, booleans, strings, lists, objects).

Table Creation

Tables can be created using the table() method or the createtable() function.

table

The table() method can be applied to a list, and will create a table with the objects in the list as rows. Any properties provided as arguments within the parentheses will be created as columns for the table.

Syntax	Return Value
`<list>.table([arg1], [arg2]...)`	Table with the list values as rows, and the properties as columns

args: [Optional] Arguments should be property IDs of the objects in the list. For each property, a column will be made that will display the value of that property for each row in the table.

Examples

myKpis := list(t.KP001, t.KP002, t.KP003)       // List with 3 KPIs in it
myKpis.table(id, name, responsible)             // Table with 3 KPIs as rows, and columns id, name, responsible

createtable

The createtable() function can be used to create an empty table with customized column headers. The specified column header names must be strings.

Syntax	Return Value
`createtable([arg1], [arg2]...)`	Empty table with columns “Header Name1” and “Header Name2”

args: [Optional] For each argument passed, a column will be created with the argument as the header. Arguments should be strings. If a datatype besides string is passed in, the system will try to use the string representation of that datatype.

Examples

myTable := createtable("Kpi Name", "Objective", "Parent Organization")  // Makes an empty table with columns
                                                                        // Kpi Name, Objective, Parent Organization

Adding to Tables

Additional columns and rows can be added to tables using the addColumn() and addRow() methods. These methods are commonly used to to add values that are not stored in properties. The provided values are typically hardcoded, or are the result of more complex expressions. Both of these will modify the table they are applied to in place. See below for syntax and examples.

Methods

Methods that can be chained onto tables to perform various actions, such as adding columns and rows, or styling the table.

addColumn

The addColumn() method can be used to add columns to an existing table. It takes two arguments: a string representing the column header, and an expression that will resolve to the value of the column. The ability to take an expression as an argument differentiates addColumn() from the table() method which can only take property IDs as arguments.

Syntax	Result
`<table>.addColumn(propertyID)`	Adds a column named after the property, whose value is the property value
`<table>.addColumn([headerName], expression)`	Adds a column named <headerName> whose value is the expression result

headerName: A string or string variable representing the name of the header for the column. If a datatype besides string is passed in, the system will try to use the string representation of that datatype. It is mandatory when using an expression that is not a property ID, and for situations where a custom header name is required.
expression: An expression evaluating to a value. Complex expressions can be written here. The context object for the expression be the object in the row of the table.

Examples

myKpis := SELECT Kpi                                        // Revenue, Net Promoter Score, Employee Retention
myKpis.table(id, name)
    .addColumn("Perspective", parent.parent.name)

addTimeColumns

The addTimeColumns method can be used to add multiple columns to a table for a range of periods. This method takes a value expression, a start date, an end date, a time period, and a column header. The system will create a column for each time period in the start-end date range, and the value will be the value of the value expression evaluated for that particular time period. The name of the column will be the specified column header. This is a very useful tool for showing values accross time periods without having to use repetitive addColumn calls.

Syntax	Result
`<table>.addTimeColumns(<value>, <periodType>, <startDate>, <endDate>, <columnName>)`	Adds columns with value for each `periodType` in the date range

value: A value or expression to be calculated
periodType: A valid period type ID, such as M (Month) or Q (Quarter)
startDate: A date, or expression resolving to a date, to be used as the start of the time range
endDate: A date, or expression resolving to a date, to be used as the end of the time range
columnName: A value, or expression resolving to a value, to be used as the name for the column. The string representation of the value will be used.

Examples

myKpis := LIST(t.KPIA, t.KPIB, t.KPIC)

myKpis.table(name)
    .addTimeColumns(actual, M, BOY, EOY, this.bop.long)

addRow

The addRow() method can be used to add rows to an existing table. It takes 1 + n arguments, where the first argument is an object reference. This object will be the context object for each cell in the row. Reusing the same context object more than once in the table is not supported.

The remaining n arguments each correspond to a column in the table. The order of arguments is directly tied to the order of the columns. So the first argument (after the context object) will be placed into the first column, the second argument into the second column, and so on.

Syntax	Result
`<table>.addRow(obj, [arg1], [arg2]...)`	Adds a row with context object <ob>, with column values <arg1>, <arg2>

obj: The context object to the used for the row. Property expressions, such as id will be based on this object.
args: [Optional] An expression resolving to a value to be calculated for the corresponding column in the table. The first argument (after obj) will be used for the first column in the table, the second one for the second column, and so on.

Examples

myTable := createtable("ID", "Name", "Number of Children")                          // Create table with 3 columns
myTable.addRow(t.CORPORATE_FINANCE, id, name, t.CORPORATE_FINANCE.children.size())  // Add perspective row
myTable
    .addRow(t.CORPORATE_KPI001, id, name, t.CORPORATE_KPI001.children.size())       // Chain several addRow() to
    .addRow(t.CORPORATE_KPI002, id, name, t.CORPORATE_KPI002.children.size())       // add two more rows with KPIs

align

The align() method can be used to modify the alignment of values in rows, columns, or the entire table. The arguments are limited to certain pre-defined values.

Value	Definition
`LEFT`	Aligns value to the left
`RIGHT`	Aligns value to the right
`CENTER`	Aligns value to the center

Syntax	Result
`<table>.align(<alignment>)`	Aligns the entire table based on the argument
`addRow(...).align(<alignment>)`	Aligns the row based on the argument
`addColumn(...).align(<alignment>)`	Aligns the column based on the argument

alignment: The alignment value to align by. Must be one of LEFT, RIGHT, CENTER.

Examples

myKpis := SELECT Kpi
myKpis.table(name, actual)
    .addColumn("Double Actual", actual * 2).align(RIGHT)

collapse

The collapse() method can be applied to a parent row to hide its children rows by default. Web Users still retain the ability to expand the row by clicking on it. Rows are considered children of another if their indentation (via indent() method) is greater than the row before them.

Syntax	Result
`<table>.addRow(...).collapse()`	Displays any children rows collapsed by default

Examples

myTable := createtable("ID", "Name")

myTable.addRow(t.METRIC001, id, name).collapse()        // collapse() is applied at parent row
myTable.addRow(t.CHILD_METRIC001, id, name).indent(1)
myTable.addRow(t.CHILD_METRIC002, id, name).indent(1)   // both rows are indented, and thus considered children rows
                                                        // both rows will display as collapsed by default

decimals

The decimals() method can be used to control how many decimal places to show for numeric values. It can be applied to the entire table, columns, or rows.

Syntax	Result
`<table>.decimals(<n>)`	Sets the number of decimals for the table to `n`
`addColumn(...).decimals(<n>)`	Sets the number of decimals for the column to `n`
`addRow(...).decimals(<n>)`	Sets the number of decimals for the row to `n`

n: The number of decimal places that should be displayed.

Examples

myKpis := SELECT Kpi WHERE name = "Revenue"
myTable := myKpis.table(id, name)
myTable.addColumn("Actual", actual).decimals(2)         // Column values will display with 2 decimals places

formattype

The formattype() method can be used to change the formatting of numeric values in a table, row, or column. It is very similar to the Format Type property found in Function objects.

Value	Definition
`NORMAL`	Normal formatting
`PERCENTAGE`	Percentage formatting (multiplied by 100 and with % postfix)
`THOUSANDS`	Values in thousands
`MILLIONS`	Values in millions
`BLANK`	Blank values
`DATE`	Date value
`DURATION`	Number treated as milliseconds and expressed as hours:minutes

Syntax	Result
`<table>.formatttype(<format>)`	Formats the entire table as `format`
`addColumn(...).formattype(<format>)`	Formats the entire column as `format`
`addRow(...).formattype(<format>)`	Formats the entire row as `format`

format: The format that should be applied to the table, row, or column.

Examples

myKpis := SELECT Kpi
myTable := myKpis.table(id, name)
myTable.addColumn("Achievement", actual / target).formattype(PERCENTAGE)  // Formats the value as a percentage

hidden [5.3.1.0 +]

The hidden() method can be used to set the visibility of a table column created by addColumn() to hidden. This means the column will still be accessible on the web, but it will be hidden by default. Web Users can then unhide the column in the same manner they would unhide columns on other, non-extended, table types.

Syntax	Result
`addColumn(...).hidden()`	Hides the added column

Examples

// In the example below, the id, name, actual, and "Responsible Person" columns would be visible by default
myKpis := SELECT Kpi

myKpis.table(id, name)
    .addColumn(actual)
    .addColumn(target).hidden()                             // Hides the target property column
    .addColumn("Responsible Person", responsible)
    .addColumn("You can't see me", "John Cena").hidden()    // Hides the custom column "You can't see me"

indent

The indent() method can be used to add indentation to a table row. Indented rows are considered children of the last row above them that was less-indented.

Syntax	Result
`addRow(...).indent(n)`	Indents the row `n` indentations.

n: The number of indentations that should be added.

Examples

myTable := createtable("Name", "Actual", "Status")                      // create table with 3 columns
myTable
    .addRow(o.CORPORATE, name, "", "")                                  // add an Organization row
    .addRow(t.KPI001, name, actual, statusClassification).indent(1)     // add a KPI row, with 1 indent

postfix

The postfix() method can be used to add a postfix to a table, column, or row.

Syntax	Result
`<table>.postfix(<text>)`	Sets the postfix for table values to `text`
`addColumn(...).postfix(<text>)`	Sets the postfix for the column values to `text`
`addRow(...).postfix(<text>)`	Sets the postfix for the row values to `text`

text: The text to be used as a postfix. If a datatype besides String is passed in, the system will attempt to use the string representation of the datatype.

Examples

myKpis := SELECT Kpi WHERE name = "Revenue"
myTable := myKpis.table(id, name)
myTable.addColumn("Actual", actual).decimals(2).postfix("USD")  // Formats column with 2 decimal places and USD postfix

prefix

The prefix() method can be used to add a prefix to a table, column, or row.

Syntax	Result
`<table>.prefix(<text>)`	Sets the prefix for table values to `text`
`addColumn(...).prefix(<text>)`	Sets the prefix for the column values to `text`
`addRow(...).prefix(<text>)`	Sets the prefix for the row values to `text`

text: The text to be used as a prefix. If a datatype besides String is passed in, the system will attempt to use the string representation of the datatype.

Examples

myKpis := SELECT Kpi WHERE name = "Revenue"
myTable := myKpis.table(id, name)
myTable.addColumn("Actual", actual).decimals(2).prefix("$")      // Formats column with 2 decimal places and $ prefix

readonly [5.1.8.0 +]

The readonly() method can be used to make a row, column, or the entire table read-only.

Syntax	Result
`<table>.readonly()`	Makes the entire table read-only
`addColumn(...).readonly()`	Makes the column read-only
`addRow(...).readonly()`	Makes the row read-only

Examples

myTable := this.object.kpis.table(id, name, responsible)
myTable.readonly()                                                  // Makes myTable read-only

myIndicators := SELECT Indicator
myNewTable := myIndicators.table(id, name, actual)
sumActuals := myIndicators.as(actual).sum()
myNewTable.addRow("", "Sum: ", sumActuals).readonly()               // Adds a read-only sum row to myNewTable

myNewTable.addColumn("Responsible", responsible).readonly()         // Adds a read-only Responsible column to myNewTable

style

The style() method can be used to apply various styles to the rows, columns, or the entire table. The arguments are limited to certain pre-defined values.

Value	Definition
`bold`	Bold font
`italics`	Italic font
`wrapped`	Wraps cell text
`full`	Displays cell text as full text
`truncated`	Truncates cell text
`separator`	Adds a vertical line after a column. 5.2.0.0+

Syntax	Result
`<table>.style(arg1, [arg2]...)`	Styles the entire table based on the arguments
`addRow(...).style(arg1, [arg2]...)`	Styles the row based on the arguments
`addColumn(...).style(arg1, [arg2]...)`	Styles the column based on the arguments

args: The style values to be applied. There must be at least one. italics, bold are not mutually exclusive and can be applied together. wrapped, full, truncated are mutually exclusive and cannot be applied together. In the case of conflicting arguments, the last one will be applied. separator is only valid when style is chained to .addColumn.

Examples

myKpis := SELECT Kpi
myKpis.table(id)
    .addColumn("KPI Name", name).style(bold)                        // Bolds the text in the "KPI Name" column
    .addColumn("KPI Description, description").style(wrapped)       // Wraps the text in the "Description" column when it cannot fit on 1 line

url

The url() method can be used to add a link to a table, or a table column. The link can be to an external URL, or to an internal object. After url() is used, the table or column values will all become clickable, and take the user to the specified link when clicked.

Syntax	Result
`<table>.url(<link>)`	Links the entire table to `link`
`addColumn(...).url(<link>)`	Links the column to `link`

link: What the table or column will be linked to. The value must be a URL, or an object reference.

Examples

myKpis := SELECT Kpi WHERE id = "CORPORATE_KPI001"
myTable := myKpis.table()
myTable
    .addColumn("External Link", "Sharepoint").url("https://www.sharepoint.com") // Add column with link to sharepoint
    .addColumn("Actual", actual).url(t.CORPORATE_KPI001)                        // Add value column with link to object