Value Range Function (CO, PF)
ABS: Determine the Absolute Value of a Number
Objective
- Detecting the absolute value of a number
Information
- The ABS function provides the absolute value of a number. The absolute value of a number is its value without the +/- sign.
Rule
Example
- The expression ABS(-2) provides the value 2.
ATOI: Conversion of a String into a Numeric Value
Objective
- Converting a string (Type X) to a numeric value.
Rule
- ATOI (<Alphanumeric DI, Variables>)
Example
Note
- If the content of the string cannot be interpreted as a number, the result of ATOI is the value 0.
CONST: Constants in Value Ranges
Objective
- Fetching a constant name, possibly on a language-dependent basis
Rule
- CONST (Language, Constant ID)
- CONST: Function
- Language: @19, 0000, 1000
- Constant ID: ID number of the constant
Example
Note
- The constant name is automatically trimmed.
EXIST: Existence Check without Relationships
Information
- If it is not possible to carry out a check using relationships, the existence of a data item in a data table can still be checked using this function.
- If a corresponding data record does not exist in the specified data table, any user entry is rejected with the message “Please check input”.
Rule
- EXIST (DI, DT)
- EXIST: Function
- DI: target DI which is to be checked (usually a DI with VR)
- DT: the data table in which the existence of a data record is to be checked.
Example
- The EXIST function to check for existent records.
| DI |
Name |
WB type |
Value range |
Explanation |
| 023623 |
VH Open module |
CS |
EXIST(DI000111,405) |
DI023623 and DI000111 LBMOD are both from DT411. Via EXIST() you can check, whether the listbox module in DT405 exists. |
- EXIST function to check for non-existent records.
| DI |
Name |
WB type |
Value range |
Explanation |
| 023623 |
VH Open module |
CS |
EXIST(DI000111,405)!=1 |
DI023623 and DI000111 LBMOD are both from DT411. Via the value range, you can check, whether the listbox module in DT405 exists. |
Note
- The check
- can be done for IDs which are compiled from a DI;
- does not have to check ID data items themselves, as these are checked via the relationship function;
- can only access IDs with DB-Typ =X.
- cannot access compound IDs.
EXP: Exponentiate Numbers
Objective
Information
- The EXP function provides exponentiated values of a number.
Rule
- Syntax: EXP(Operand 1;Operand 2)
- Operand 1 = Basis
- Operand 2 = Exponent
Example
- The expression EXP(2;3) provides the value 8.
- The expression EXP(4;0.5) provides the value 2.
- The expression EXP(-3;3) provides the value -27.
Notes
- The exponent cannot be a rational number. Fractions such as ½ must be replaced by a value such as 0.5.
- In the case of roots of positive numbers, only the positive number is provided. The result of EXP(4;0.5), e.g., is +2 and –2. The software provides the value +2.
- Calculations with a negative basis are only permitted with whole-number exponents. Calculations with the EXP function should therefore only be carried out after conversion to an absolute value.
Expand: Return the Value of a Variable
Objective
- Read out the current value of a variable
Example
Note
- Possible variables are "@xxx", "@Gxxx" , "@Dxxx", and "@Uxxx"
FMTDATE: Convert a date DI into a String
Objective
- Converting a date DI to a string, for example to include this in a character string
Rule
- FMT(Date DI, <Format ID>)
- Date DI: Date data item with DB type = N4
- Format ID:
- ID number of the format as a character string (with “”) or
- "" (blank string): Output in the default date format for the user interface (date format)
FMT: Convert a numeric DI into a String
Objective
- Converting a numeric DI to a string, so that it can be included in a string, for example
Rule
- FMT(Date DI, <Format string>)
- Format string: „[-][W][.P][e|E]"
- -: left aligned (default: right)
- W: min. width
- P: max. number decimal place
- E: Exponential representation
Example
- Format result, value: 298,123
- „8.2" „ 298.12"
- „-10" „298.12 "
- „.1" „298.1"
Notes
- All numeric DB types are permitted.
- Corresponds to the Unix function man fprintf.
FR: Append String
Objective
- Fetching an operand value and add in strings before and/or after the fetched value
Rule
- FR(<Character string>, <DIxxxxxx or @xx>, <Character string>)
- Operands can be data items and variables.
- The character strings
- can be of any length.
- are trimmed beforehand.
Note
- This function cannot be used with I-texts.
IIFS: Determine Values in Accordance with a Condition
Objective
- Combining strings in different ways depending on conditions.
Rule
- IIFS (Condition, String1, String2)
- Condition
- logical expression, whose result is either "TRUE" or "FALSE."
- String1: The value assumed by the testing DI if the condition is met.
- String2: The value assumed by the testing DI if the condition is not met.
Note
e
- The number of IIFS conditions in a value range construction has been increased to 15. Up to 15 IIFS conditions can be created.
- Applies only for X, AL fields, but not for numerical ones.
LENGTH: Determine Number of Characters of a Character String
Objective
- Determining number of characters of a character string
Information
- The LENGTH value range functions provides the number of characters in a string.
- Blanks at the start and end are not counted.
Rule
Example
- The expression LENGTH(“12345678”) provides the value 8.
Note
- The function can only be applied to strings (DB type=X, Class=0). It cannot be applied to I texts or continuous texts.
NB: Input Check for Alpha Data Item According to Numeric Rules (from, to)
Information
- An alphanumerical data item is converted into a number.
- A check is then carried out to determine whether this number lies between from and to.
Rule
- NB (data item, from, to).
Example
Note
- Entries are rejected with the error message “Please check input” if the check shows that
- the result is not a valid number, or
- the number does not lie within the “from/to” range.
NCHILD: Output the Directly Subordinate (Child) Records
Note
Information
- This function returns the number of directly subordinate (child) records from the first subordinate data area, in N4 format.
Rule
Example
| DI |
Name |
WB type |
Value range |
Explanation |
| xxxxxx |
Example DI |
CO |
NCHILD() |
Example: If the DI is created in DT461 Project and incorporated in a module in a data area of DT461 in which the first child data area stems from DT463, the number of tasks of a project is output in the field. |
NORMSINV: Quantiles of Standard Normal Distribution
Information
- The NORMSINV function provides the quantiles of standard normal distribution.
Rule
- NORMSINV (Operand)
- The operand is the probability belonging to standard normal distribution.
- An operand <0 or >1 is not permitted.
Example
- The expression NORMSINV (0.75) provides the value 0.674490.
OBS: Overwrite Object Protection
Objective
- Overwriting object protection data items with the value specified.
Example
Rule
- OBS<OS part > (value for part1, value for part2, ...)
- OBS: Function
- OS part:
- 1: overwrites (rwd owner, rwd group, rwd any)
- Example: OBS1 ("r--","r--","r--")
- Result: after they are exported, the records can no longer be edited or deleted
- 2: overwrites (system user [1-4], system user [5-8])
- Example: OBS2 ("z", " ")
- Result:
- after they are exported, the system user for the records is "z".
- in the corresponding modules, records can be searched for "without z". This means that records with the user "z" are not displayed, and they can no longer be edited or deleted.
Notes
- This function
- can only be used when exporting from PPMS;
- is assigned to virtual DIs in the affected data tables with DB type X and DB length = 2.
- The data area which contains the DIs with these VR functions may not contain data fields in any other data tables.
- The records are changed directly within the database. This ensures that
- the object protection data fields which are to be changed are only updated after a new search;
- any available exits are not executed.
PCT: Calculation of Percentage Deviation
Objective
- Calculating the percentage difference between two data items.
Rule
- PCT<DBTyp> (DI-1, DI-2)
- PCT: Function
- DB type:
- DB type of the data items shown in parentheses.
- Possible values: N2, N4, N8
- DI 1: e.g. actual value
- DI-2: e.g. target value
Notes
- This function can only be used with DIs which have the same DB type.
- If a % deviation is to be applied between DIs with different DB types, then
- a DI with VR type=CS of one of the two DB types must be converted.
- the value calculation must always be executed with the VR type =CO.
PERIOD: Determine a Period
Objective
- Determining the start or end date of a period relative to a date.
Information
- Function for determining a period:
- Result
- the date of the day which is the first or last day of the period determined by the parameters input for the function.
- Possible applications
- Start and end dates for date bars
- Start and end dates for date bars
- As dynamic variables
- As dynamic variable in value ranges
Details
- Starting from a given date, PPMS is to determine the start or end date of the period which contains this date, or of a preceding/following period:
- calendar week containing the date
- nth week before that (not implemented)
- nth week after that (not implemented)
- month containing the date
- nth month before that
- nth month after that
- quarter containing the date
- nth quarter before that
- nth quarter after that
- year containing the date
- nth year before that
- nth year after that
Rule
- PERIOD (<Datum>, <Raster>, <Terminlage>, <Abstand>)
- Date
- The date relative to which the period is to be determined.
- This parameter can be a date data item or a dynamic, global, user or system variable which contains a date.
- Grid
- Defines the period of time for which the start or end date is to be found.
- Values:
- 3: Week
- 4: Month
- 5: Quarter
- 6: Year
- Date position
- Values:
- 0: the start date is determined
- *·1
- the end date is determined
- This parameter can be a data item with DB type= N2, or user variable, or a dynamic, global variable, containing the value 0 or 1.
- Distance
- Defines which period is determined before or after.
- Values:
- 0: the period containing the date itself
- <0: number of equal time periods before that
- >0: number of equal time periods after that
- This parameter can either be a data item with DB type=N2, or user variable, a dynamic, global variable, containing an integer.
Examples
| DI |
Name |
WB type |
Value range |
Explanation |
| xxxxxx |
Example DI |
CO |
PERIOD(@D1008,6,0,1) |
First day of the previous year to the date in the dynamic variable @D1008 mit Raster = 6, date status = 0 and distance = -1 |
| xxxxxx |
Example DI |
CO |
PERIOD(@15,5,1,2) |
Last day of the quarter after the next quarter to today's date with grid = 5, date status = 1 and distance= 2 |
RELPOS: Output the Position of a Data Record
Information
- This function returns the relative position of a record in the data table, in N4 format.
Note
- The function does not work in modules with structural sorting.
ROUND: Round Numbers
Information
- The ROUND function rounds a number to a definable number of places, for example the expression ROUND(12,3456;2) provides the value 12.35.
Rule
- Syntax: ROUND(<Value>;<Number of decimal places>).
CTR: Truncate a String
Objective
- Fetching the value an operand and to truncate a string at any required point.
Rule
- STR(<DIxxxxxx or @xx>, Start position, Length)
- Operands can be data items and variables.
- Start position
- Position within the operand string from which it is to be truncated.
- From which the first operand position is counted incrementally.
- Length
- Number of characters cut off from the start position
- Maximum length is the length of the operand
Example
| DI |
Name |
WB type |
Value range |
Explanation |
| 009488 |
OC Project Y/N |
CS |
(STR(DI009487,1,2)==@G935) |
In this example it is checked, whether the first 2 characters of DI 009487 equate to the value of variable @G935 |
SYSTEM: Open the Program from the Software
Information
- This function may no longer be used.
- This function enables another program to be opened from within the program.
- The program is executed synchronously, i.e. the system waits until the program which was called terminates.
Rule
- SYSTEM(DI<DI numbers>)
- DI<Number>: the DI receives the command like for a shell input „cp file1 file2 1>&2 2> cp.err$$".
- To check only for errors, the comparison operand is appended after the parentheses e.g. SYSTEM(DI) = = 0
Notes
- The SYSTEM function returns the value 0 if successful; it should therefore be linked to a DI of the internal type integer. To specify which program is to be executed requires a text DI, which holds the program name and options.
- If this value range is linked to a Y/N field with the name Error the status is displayed as text.
- The new process adopts the standard input and output of PPMS, and is therefore directly linked to the PPMS client. Outputs would be sent to the client, and standard input, output, and errors must therefore be rerouted.
- The return value from SYSTEM corresponds with the return status (integer) of the program which is called-up.
- In order to distinguish the files produced by rerouting of the output, “$$” can be inserted into the file name. The two signs are replaced by the process number at execution.
TRIM: Remove Blanks
Objective
- Removing any blanks before and/or after a string.
Rule
- TRIM (Operand)
- TRIM: Function
- Operand: DI, variable or constant ID number
VH: Number of Records in a Subordinate Data Table
Information
- This function displays the number of data records for a superior (parent) data record which exist in a subordinate (child) data table. This does not require that the module contains a data area for this data table.
Rule
- VH (DT, index, path, DI)
- DT: target data table, for which the usage frequency is to be determined
- Index: specified data table in the ID of the target DT
- Path: dependent on "variable name" from relation
- 0: e.g. "ID425"
- 1: e.g. "ID425A"
- 2: e.g. "ID425B"
- 3: e.g. "ID425C"
- DI: identifying DI in the data table of the superior object, using which the subordinate object is to be searched for.
Note
- This function can be used with the following VR types: CS, CO and CL.
Warning
- If this value range is wrongly constructed, the error message "Database error - 52" will occur in the module during the search, which must be confirmed for every data record found.
IKV: EXCEL Function for Financial Mathematical Calculations
Objective
- Using the EXCEL function IKV as value range function in PPMS to realize the financial mathematical calculations with PPMS-Customizing.
Rules
- IKV (value1, value2, value3,....,value12, estimated value)
- Algorithm: MS Excel, function IKV
- Input parameter
- Value1 to value12
- Values of payment series as costs (negative) and benefits (positive) in sequence of years.
- There must be at least one negative and one positive value.
- Permissible operators for values 1-12:
- DIxxxxxx (numerical data items, DB type = N8) @DIxxx (dyn. variables on numeric data items, DB type = N8
- numeric constants, e.g. 0.0
- If less than 12 values are required, the remaining values can be specified constantly with 0.0 in the value range.
- Estimated value
- see EXCEL help
- 0, if not used. The system then adopts „10%" internally. For this, see EXCEL function
- Return value
- Result of EXCEL-IKV
- 999999.99, if calculation cannot be carried out (in Excel: #NUMBER)
Example
- IKV(DI299101, DI299102, DI299103, DI299104, DI299105, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0)
Note
- The value range should not be used with value range type CO because then a recalculation of the IKV takes place after the entry of every field involved in the formula, which can take time because of the iteration process used.
Distance and Date Scheduling
Objective
- Calculating the data or periods between two dates using the calendar, stored in PPMS
Details: Determine the number of the working days
- Calculate the number of working days between two dates
- In order to calculate the number of the working days between two dates use the following formula:
- WD_BETWEEN([calendar],[date1],[date2])
Example 1
- Number of working days between today and today + 100 days in the calendar “BC”
- Result:
- WD_BETWEEN("BK",@15,@15+100)
Example 2
- Number of working days between 01.01.1970 and today’s date in the calendar „BC“
- Result:
Details: Determine the date
- Determine the date using working days
- For calculation using the number of the working days use the following formula:
- WD_ TIMEINTERVAL ([calendar],[start date],[number])
Example 1
- Today’s date + 5 working days stored in the „BC“calendar.
- Result:
- WD_TIMEINTERVAL("BK",@15,5)
Example 2
- Today’s date + number of the working days from DI000002+1 stored in the calendar from DI000001.
- Result:
- WD_TIMEINTERVAL(DI000001,@15,(DI000002+1))
. 
