Preparing Data

The second goal of Data mode is to let you prepare data and change values without creating new variables.

In the Dataset view, you can apply transformations by selecting the More button on a table or facet header.

This opens a contextual menu.

If your column contains a dimension or detail, choose a transformation to apply to the values in that column:

• Upper case: Text values in your selected column will become all uppercase.
• Lower case: Text values in your selected column will become all lowercase.
• Replace: You can replace all instances of the text you specify in the Find What text field with the text you specify in the Replace With text field.
• Trim: You can select the Space option to remove extra spaces. Alternatively, you can choose to remove a specific character by selecting the Other Character option. You can also choose to trim spaces or characters at the start, end, or both the start and end.
• Fill: This transformation allows you to make sure that all your string values have the same length. In the Fill dialog box, you can choose your desired string fill pattern, length, and whether your fill pattern should be added to the start or end of your string.
• Formula: This transformation allows you to use a formula to apply to the object’s values. The formula may contain other objects of the same cube and functions of the Web Intelligence formula language, except the ones in the Aggregate, DataProvider, Document and Set categories. In the Misc. category, only the If, Else, ElseIf and Then functions can be used.
• Group: This transformation allows you to select certain values and have them be seen as only one value. In the Manage Groups dialog box, after selecting the values to group, select the drop-down list for Group and choose Create Group. In the New Group dialog box, you can enter a name for this group and select OK. In the table, the values you have added in the group will now display the group name instead of their individual values.

If your column contains a measure, you may only select a transformation based on a formula.

Manage Transformations

When you define transformations for an object, a Transformation icon appears next to the object in the Main panel→Objects tab.

To view the list of transformations, select the object in the Main panel→Objects tab and select Build panel→Object or Variable Properties tab→Transformations sub-tab.

Local Variable

You can create a local variable if its formula uses only objects from a single cube. This helps reduce the number of variables listed under the Variables folder in Design mode.

To create a local variable, select a cube. Then click the Add New Variable button in the Main panel→Objects tab. You can also click Create Variable in the cube's contextual menu.

The Create Cube Variable dialog box opens. Here, you enter the variable name, qualification, associated dimension (if it is a detail), description, and formula.

The formula can include:

• Functions from the Web Intelligence formula language in the Character, Date & Time, Logical, and Numeric categories. You can also use the If, Then, Else, and ElseIf functions.
• Any object from the cube, including other local variables.

After you create the variable, a new column with its values appears in the Dataset view.

In the Main panel→Objects tab, local variables appear under their cube. The contextual menu for a local variable includes these options:

• Edit, Rename, and Delete to manage the variable.
• Hide or Show to control whether the variable is visible.
• Search in Variables to find this variable in other variable definitions.

An icon appears next to each local variable and the cube that contains it.