The Column Name field is a string field which will allow you to change the name of the column exposed to the application. This name cannot contain any special characters, spaces or numbers as the first character.

The Formula field will be defaulted with the formula for the column the view was created with but the formula can be changed to produce a different results. Formulas in TM1Connect are use a token-replacement syntax for determining the results. They are also sensitive to the Data Type setting on the Mappings page.

For String Data Type

Token replacement means that all text outside a specified token will be treated as prepending or appending text without having to supply functions, quotes, string concatenation syntax.

For example, with a formula of:

Dept ${.RowDimensions[1].ID} - ${.RowDimensions[1].Name}

Will result in ID and Name fields (of the row dimension) being concatenated together and prefixed with 'Dept'. For example:

Dept 1100 - Human Resources

For Numeric Data Type

When a formula is evaluated for numeric data types, operators such as +, -, / and * can be used to apply basic math to the value.

For example, with a formula of:

${.Columns[1].Value} * 0.75 + .1

Will result in multiplying the value in column 1 by .75 and then adding .1 to that result.

0.85

Conversions on Output should be used when a specific format, such as a date or a Boolean value (T/F), is required by the application. Selecting this check box will display additional options:

Similar to the Formula, conversions are sensitive to the column data type specified in the Mapping Properties screen.

For String Data Type

If the column is specified as a string value in the Mapping Properties screen, the results will be returned from TM1Connect as a string value even if the value in TM1 is numeric, or a string measure with a date value in it, or a numeric value that represents a date etc.

For example, assume a measure called Date contains a numeric date value in TM1 (stored in integer format), but the application requires a date to be a string data type that uses the format yyyy/MM/dd. TM1Connect understands the transformations required (TM1 date vs UTC) and will convert the TM1 numeric value to a date using the yyyy/MM/dd format by specifying the options shown below:

For Numeric Data Type

When the output data type is numeric, TM1Connect will attempt to perform a numeric conversion if possible, even if the TM1 measure is a string. If the conversion fails, a zero will be returned.

For Date Data Type

When the output data type is date, TM1Connect will attempt to perform a numeric conversion whether the TM1 measure is a string or a numeric. If it is a string, TM1Connect understands a variety of date formats that can be received from the view (such as YYYY/MM/DD or MM/DD/YY or MMMM DD, YYYY) and convert them to a UTC encoded date.

For Boolean Data Type

When the output data type is Boolean, TM1Connect will attempt to convert to a Boolean value true/false using the following conversion:

Y, Yes, YES, 1, T, True, TRUE, will be converted to a Boolean value true

N, No, NO, 0, F, False, FALSE will be converted to a Boolean value false