Text
Since data used for analyses is mostly represented in numbers, dates or process paths, formatting text is not a very common task in CPM and will mostly be necessary when using OLAP Tables. The standard editing menu for components already offers options for font color, size and weight that have been explained in the respective chapters. In case these options are not sufficient, some more functions for editing text exist. These will be used in the formula field of dimensions and aggregations and on columns that contain string values.
UPPER(text)
Converts the string specified in the ‘text’ parameter to uppercase. The text parameter can for example be the name of a column containing string values.
TRIM(text)
This function removes spaces (if there are any) in front of the string specified as ‘text’ parameter. The text parameter can for example be the name of a column containing string values.
Numbers
Numbers play a very big role when analyzing data and thus should be presented in an easily understandable manner. For that purpose, CPM offers some formatting functions for numbers.
TEXT(value; text)
Although the name does not seem to represent a function for numbers, the TEXT function can be used on numbers to turn them into texts according to a given format. Thus, the TEXT function is a very useful tool for making numbers more easily readable. Using the TEXT function, specifications can be made about whether or not a thousands separator will be used or how many decimal places should be displayed in the data. Please note that no specifications can be made regarding the characters used for thousands and decimal separators since these will be configured in the “My Profile” settings described in Das Benutzermenü.
The value parameter specifies the value the format will be applied to. This will in most cases be the $value variable that can be used as a representation for the values inside a column. The text parameter then specifies the format and must be given in single quotation marks.
Example: To convert the value 123456 into a number with thousands separator and two decimal places, we use the TEXT function as follows: TEXT(12345, ‘0,000.00’) and get 12,345.00 as result.
FLOOR(value)
The FLOOR function will round the given number down to the nearest integer value. No matter the value of the decimal place, the number will always be rounded down. The value parameter specifies the value that will be rounded down. This will in most cases be the $value variable that can be used as a representation for the values inside a column.
Example: To round down 3.9 to 3, we use the FLOOR function as follows: FLOOR(3.9).
CEILING(value)
The CEILING function will round the given number up to the nearest integer value. No matter the value of the decimal place, the number will always be rounded up. The value parameter specifies the value that will be rounded up. This will in most cases be the $value variable that can be used as a representation for the values inside a column.
Example: To round up 3.1 to 4, we use the CEILING function as follows: CEILING(3.1).
ROUND(value; digits)
The ROUND function will round numbers up or down to a certain number of decimal places. If the digit that will disappear due to rounding is below 5, the value will be rounded down to the closest integer. If it is equal to five or greater, the value will be rounded up to the closest integer.
The value parameter specifies the value that will be rounded up or down. This will in most cases be the $value variable that can be used as a representation for the values inside a column. The digits parameter specifies the number of decimal places the value will be rounded to. However, the digits parameter can also take negative values. If a negative parameter is used, the system will behave as if the decimal separator was moved to the left, then round the value (without deleting the decimal places) and then move the decimal separator to the right again.
Example: To round 123.56 to one decimal place resulting in 123.6, we use ROUND follows: ROUND(123.56, 1). However, ROUND(123.56, -2) will result in 100.