Working with Columns

<< Click to Display Table of Contents >>

Navigation:  User Guide > Using the Manager with TM1 > Managing Cube Views > Managing Mappings >

Working with Columns

When you first access the mappings screen the Available Fields section on the left will be populated with all of the information associated with the view that was defined in the View Properties and Advanced View Properties screens. The Column Mappings section on the right will be initialized with the default columns, which are one column for each row dimension, and one column for each measure dimension member.

 

hmtoggle_arrow1        Adding columns        

To add a column to the query result, locate the field from the Available Fields section and drag them into the column list. New fields will be placed at the bottom of the column list.

Add Column

When adding a field from the RowDimensions or the Columns area, you can select and drag the text of the column name and TM1Connect will automatically select the appropriate property. In the example above, dragging Salary Modification measure will automatically set the formula so the Value or FormattedValue (if TM1 formatting has been applied to the measure) property. Which field is selected will be reflected in the formula column.

hmtoggle_arrow1        Reordering columns        

To re-order columns, click on the name of the column and drag it to the location the column should reside. This will allow you to control precisely the order of the fields for the application.

Reording Columns

hmtoggle_arrow1        Removing columns        

To remove a column, simply click on the remove icon to the right of the column.

Remove Column

You will be prompted to confirm deletion, and by selecting OK, the column will be removed from the list.

hmtoggle_arrow1        Editing the Formula        

In some cases, the field assigned by TM1Connect may not be the desired choice, if, for example, the formatted value field is needed instead of the numerical value, you can click on the field and edit the formula directly, without having to drag the FormattedValue field in from the field selection area.

Editing the Formula

hmtoggle_arrow1        Changing the Data Type        

TM1 supports only Numeric and String data types, but applications sometimes require other data types to take advantage of native capabilities, such as date fields, or boolean fields. You can alter how TM1Connect returns the information from TM1 to your application by changing the Data Type. Click on the value in the Data Type column and a dropdown will appear with the possible values you can select from.

Change Data Type

TM1Connect will coerce the value into the desired data type from either string or numeric values from TM1. In some cases, if the data type cannot be converted, a value of 0 or null will be returned instead.

TM1Connect Data Type

TM1 Data Type

String

Numeric

Boolean

Date

Numeric

Yes

Yes

0 = False

Non-zero = TRUE

UTC Date

(if valid)

String

Yes

Yes

(0 if not a number)

Y, Yes, True, T, 1 = TRUE

N, No, False, F, 0 = FALSE

UTC Date

(if valid)

Numeric

(Storing Dates)

Yes

(defaults to MM/DD/YYYY)

Yes

(no conversion)

N/A

(treated like numeric)

UTC Date

(if valid)

String

(Storing Dates)

Yes (no conversion)

Yes

(converted to TM1 numeric date value)

N/A

(treated like string)

UTC Date

(if valid)

For advanced conversions, see Advanced Mappings.