expression Command

add expression NAME [ADD_ITEM {ADD_ITEM}];

• ADD_ITEM provides additional information about the expression.

The add expression clauses are:

description STRING_VALUE

value VALUE

icon FILENAME

[!|not] hidden

property NAME [to ADMIN] [value STRING]

This clause is the string that defines the expression. It can contain selects, comparisons, equations, and so on. Enclose the value string in double quotes if it contains any space.

For more information, see Formulating Expressions for Collections and Evaluating Expressions.

If-Then-Else

If-then-else logic is available for expressions. The syntax is:

if EXPRESSION1 then EXPRESSION2 else EXPRESSION3

• The EXPRESSION1 term must evaluate to TRUE, FALSE, or UNKNOWN. If the EXPRESSION1 term evaluates to UNKNOWN, it is treated as TRUE. The if-then-else expression returns the result of evaluating EXPRESSION2 or EXPRESSION3 depending on whether or not EXPRESSION1 is TRUE or FALSE.
• Only one of EXPRESSION2 or EXPRESSION3 is evaluated. So if the expressions have side-effects (which can happen since expressions can run programs), these effects will not occur unless the expression is evaluated.

For example:

eval expr' if (attribute[Actual Weight] > attribute[Target Weight])
 then ("OVER") else ("OK")' on bus 'Body Panel' 610210 0;

Substring

The substring operator works within an expression to provide the ability to get a part of a string; the syntax is:

substring FIRST_CHAR LAST_CHAR EXPRESSION

The substring operator works as follows:

• The FIRST_CHAR and LAST_CHAR terms must evaluate to numbers that are positive or negative, and whose absolute value is between 1 and the number of characters in the string returned by EXPRESSION .
• The numbers returned by these terms indicate a character in the string returned by EXPRESSION .
• The characters are counted so that ‘1’ refers to the first character. A negative number indicates the character found by counting in the reverse direction. So ‘-1” refers to the last character.
• The substring operator returns the part of the string returned by EXPRESSION consisting of the characters from the FIRST_CHAR character to the LAST_CHAR character, inclusive.
• If FIRST_CHAR evaluates to a character that is after the character indicated by LAST_CHAR , an empty string is returned.

For example, to obtain the last 4 characters of a 10-character phone number, use:

eval expression 'substring -4 -1 attribute[Phone Number]' on bus Vendor 'XYZ Co.' 0;

Dateperiod

Dateperiod lets you determine the period (year, quarter, month, week and/or day) in which a given date has occurred. You include 2 arguments with the keyword dateperiod; the first defines the period to evaluate; the second is the date in question. To define a dateperiod, you can include one or more of the following periods to inquire about:

Any other characters are simply returned with the expression’s output.

For example, the following expression determines in which fiscal quarter the objects it is evaluated against are due:

add expr QuarterlyDeliverables value 'dateperiod fq07 attribute[DueDate]';

Other examples:

Dates in Expressions

The following calculations can be performed on dates within an expression:

• Subtract two dates, obtaining a number of seconds
• Add or subtract a number (of seconds) to/from a date
• Use the string MX_CURRENT_TIME for the current date/time

For example, the following could be used to determine how old an object is (in hours):

evaluate expr '(MX_CURRENT_TIME - state[Released].actual) / 3600' on bus 'Body Panel' 610210 0

The following returns the average age of all objects in the set M6000-panels (in hours):

evaluate expr 'average ( (MX_CURRENT_TIME - state[Released].actual) / 3600)' on set M6000-panels;

Keywords

These expressions are formed using one of several keywords. They are:

count

sum

maximum

minimum

average

median

standarddeviaion (stddev or stddeviation)

correlation (cor)

All but the last of these keywords is expected to be followed by one expression, its argument, that applies to business objects. The last one, correlation, needs to be followed by two such expressions. Alternative spellings are indicated in parentheses. For each keyword, you can use all lowercase, all uppercase, or first character uppercase followed by all lowercase.

Count

The count keyword takes as its argument a Where clause expression that evaluates to TRUE or FALSE. It returns an integer that is the number of items in the collection that satisfy the argument. A simple example of such an expression is “count TRUE”, which evaluates to the number of objects in a collection of business objects.

For example, when the following expression is evaluated, it indicates the number of objects of Type ‘Body Panel’ in the database:

eval expr 'count TRUE' on temp query bus 'Body Panel' * *;

Evaluating the following returns the number of objects in the set “NewBooks” whose cost is between 10 and 50:

add expr LowCost value 'attribute[cost]>=10 AND attribute[cost]<=50';
eval expr 'count expression[LowCost]' on set NewBooks;

To do the same, but exclude children’s books, you could use the following:

eval expr 'count expression[LowCost]' on set NewBooks LESS
 temp query bus Book * * where 'attribute[Reading Level]==Child';

Or change the expression to:

add expr NewAdultLowCostBooks value 'attribute[cost]>=10 AND attribute[cost]<=50 
AND attribute[Reading Level]!=Child';

Sum

Sum returns a real number that represents a total of all the values of the specified attribute for all business objects in the collection. For example, when evaluated, the following returns the total of the values of the “Amount Due Employee” attribute for all business objects in the saved query “Expense Reports”:

add expr Expenses value 'attribute[Amount Due Employee]';
eval expr 'sum expression[Expenses]' on query Expense Reports; 
OR
eval expr "'sum attribute[Amount Due Employee]' on query Expense Reports";

The following commands evaluate the ratio of total price to total cost for all objects in the set “Components”:

add expression Price value 'attribute[price]';
add expression Cost value 'attribute[cost]';
eval expr '((sum expression[Price]) / (sum expression[Cost]))' on set Components;

Maximum, Minimum, Average

Maximum returns a real number that represents the single largest value for the specified attribute for all business objects in the collection. For example, the following checks the value contained in the “diameter” attribute of each business object in the set “o-rings,” and returns whichever value is the highest:

add expr MaxDiameter value attribute[diameter];
eval expression ‘maximum expression[MaxDiameter]’ on set o-rings;

Minimum returns a real number that represents the single smallest value for the specified attribute for all business objects in the collection.

eval expr "'minimum attribute[diameter]' on set “o-rings”";

Average returns a real number that represents the average of all values for the specified attribute for all business objects in the collection.

eval expr "'average attribute[diameter]' on set “o-rings”";

Median

Median returns a real number that represents the middle number of all values for the specified attribute for all business objects in the collection.

For example, the following shows the values, listed in numerical order, for the attribute Actual Weight for seven business objects that comprise the set FprSet:

Since the middle number of seven numbers is the fourth number, the median in this case is 25. That is the value returned for the following command:

eval expr "'median attribute[Actual Weight]' on set FprSet";

Standard Deviation

Standard deviation, generally used in statistical analysis, tells how closely data conforms to the mean in a set of data. There are two formulas for standard deviation; one for calculating the standard deviation given all elements of some population; another for when using a sample to get a good estimate for the population. 3DSpace assumes the stddev expression is evaluated over the entire population, and not just a sample.

Use Standard deviation to compare the values of business object attributes. The returned value is a real number.

For example, if you know the average age of all employees, you might want to know how many people are close to that age. The standard deviation will tell you, on average, how much the ages of the group differ from the mean. If the standard deviation is a small number, it could indicate that most of the people are close to the average age. If the standard deviation is a large number, it could indicate that there is a broader spread of ages.

The following example performs a standard deviation on the age attribute of all persons in the set Employees:

eval expr "'stddev attribute[age]' on set Employees";

Correlation

Correlation, generally used in statistical analysis, is a direct measure of the relationship between variables. It can be used to determine the relationship between attributes of an object or group of objects. The returned value (the correlation coefficient) is a real number between -1 and +1.

• If the returned value is between 0 and +1 (positive correlation), it indicates that an increase in the value of one attribute results in an increase in the value of the other (or vice-versa).
• If the returned value is between 0 and -1 (negative correlation), it indicates that an increase in the value of one attribute results in a decrease in the value of the other (or vice-versa).
• A returned value of 0 represents no relationship.

For example, the following expression can be used to check how well cost correlates with price for all objects of Type “tire frame”.

eval expr "'cor attribute[Cost] attribute[Price]' on temp query bus 'tire frame' * *";

Syntax

To evaluate an expression, use the evaluate expression command:

evaluate expression EXPRESSION {EXPRESSION] on ON_ITEM {on ON_ITEM} DUMP [RECORDSEP];

• EXPRESSION is an expression.
• ON_ITEM can be any of the following clauses:

  list businessobject SEARCHCRITERIA

  relationship

  SEARCHCRITERIA

  businessobject TYPE NAME REV

  relationship ID

Expressions can be evaluated against individual objects or against groups of objects. When the on clause is used, the expression will be evaluated as a single-object expression. The on clause can be repeated to get the value for multiple separate objects. For example:

eval expr 'attribute[string-u] + "||" + attribute[color-u]' on
 bus t2 t2-1 0 on bus t2 t2-2 0;

You can also identify a collection of objects for the expression evaluation using the keywords set, query, temp set, temp query, or expand in the SEARCHCRITERIA. When any of these keywords are used, the expression will be evaluated as a collection, and must use one of these collection expressions: Count, sum, maximum, minimum, average, median, standarddeviation, correlation.

If you use a single-object expression and provide a searchcriteria that identifies a collection or vice-versa, there will be no output.

Expressions can be evaluated on these items:

• Relationships. For more information, see To and From Clauses.
• A specified business object.
• A collection of business objects,

The DUMP and recordseparator clauses can occur anywhere in the command, but the EXPRESSIONs must be given before any ON_ITEMs.

This command outputs the result of evaluating each expression for each ON_ITEM one after the other.

• The dump separator character (a comma, by default) separates each value for a single ON_ITEM.
• The recordseparator character (a new line, by default) separates the results of one ON_ITEM from the next.

Evaluating Saved Expressions

You can evaluate a saved expression as part of an expression string, or by using the Select Expression clause with print bus or print connection commands. For example:

eval expr ‘count expression[SAVEDEXPRESSION]’ on set myset; 
print bus OBJECTID select expression[SAVEDEXPRESSION]; 
print connection CONNECTID select expression[SAVEDEXPRESSION];

You can also do this in a webreport:

add expression PriceToCost value 'sum ( attribute[price] ) / sum ( attribute[cost] )';
temp webreport searchcriteria 'set Components' data object PriceToCost;

In this last example the expression object is being evaluated against a collection.

SEARCHCRITERIA Clause

SEARCHCRITERIA is defined recursively as one of:

set NAME

query NAME

temp set BO_NAME{,BO_NAME}

temp query businessobject TYPE NAME REVISION [TEMP_QUERY_ITEM {TEMP_QUERY_ITEM }]
 [casesensitive]

expand [businessobject] BO_NAME [EXPAND_ITEM {EXPAND_ITEM}] on SEARCHCRITERIA 
[EXPAND_ITEM {EXPAND_item}

query connection [type PATTERN] [vault PATTERN] [owner PATTERN] 
[where WHERE_CLAUSE] [limit N] [includeAdaplet]

SEARCHCRITERIA BINARY_OP SEARCHCRITERIA

 [( {( }]SEARCHCRITERIA [ ){ )}] 

TEMP_QUERY_ITEM is one of:

owner NAME

vault NAME

[!|not]expandtype

where WHERE_CLAUSE

limit NUMBER

includeAdaplet

over SEARCHCRITERIA

querytrigger

OBJECTID is the Type Name and Revision of the business object.

The values of TEMP_QUERY_ITEM have the same meaning in this context as they have for the temp query command. For more information, see Temporary Query.

EXPAND_ITEM is one of:

from

to

type PATTERN {,PATTERN}

relationship PATTERN {,PATTERN}

select businessobject

select relationship

where WHERE_CLAUSE

activefilters

reversefilters

filter PATTERN

view NAME

limit NUMBER

size NUMBER

recurse to N [leaf]

recurse to all [leaf]

The values of EXPAND_ITEM have the same meaning in this context as they have for the expand bus command, with a few exceptions:

• In the case of “select,” it indicates whether a subsequent ‘Where clause’ applies to business objects or to relationships. In expand bus select has an additional meaning that is not applicable here.
• Leaf can be used with recurse in a SEARCHCRITERIA expand to specify that only “leaf” nodes of the expand should be returned. What counts as a leaf node differs depending on whether “recurse to all” or “recurse to n” is issued. In the “all” case, a leaf node is one that has no children returning from the expand. With a recursion level indicated, a leaf node is one that is at that level.

Leaf is not allowed in the expand bus command, but is allowed here.

An “expand” searchcriteria does not make sure that returned objects are unique. That is, any objects that are connected more than once to the object(s) being expanded (or recursed to) will be listed more than once. This affects webreports, eval expression, and other commands using searchcriteria. For example, the following might return a list containing duplicate entries:

MQL<18>eval expres "count TRUE" on ( expand bus t2 t2-1 0 );

To avoid the duplication, you could change the searchcriteria to the following:

temp query * * * over expand bus t2 t2-1 0

The output from evaluating two expressions, E1 and E2, on two sets, A and B, with values V1A, V2A, V1B, and V2B would look like the following (using default separators):

V1A,V2A

V1B,V2B

SEARCHCRITERIAs can be linked with binary operators, which follow simple, intuitive rules:

• and—an object is in both collections
• or—an object is in one or the other collection
• less—an object is in the result if it is in the left-hand collection but not the right-hand one.

If there is more than one binary operator in a SEARCHCRITERIA , parentheses must be used to disambiguate the clause. (There is no implied order of operations.) You must include a space before and after each parenthesis. In addition, the number of left and right parentheses must match each other. For example:

( set A + set B ) - set C

would probably evaluate differently than:

set A + ( set B - set C )

Examples:

This list of examples shows the power of the SEARCHCRITERIA concept.

The following command returns the number of objects in the set Projects:

eval expression "count TRUE" on set Projects;

The same thing could be written as follows (you need to include the spaces around the parentheses):

eval expression "count TRUE" on ( set Projects );

The following command returns the number of objects returned by a query named “ask1” that are not Project 1029 0:

eval expression "count TRUE" on query ask1 less temp set Project 1029 0;

The following command returns the number of Jim’s open Projects that originated before the beginning of this year:

eval expression "count TRUE" on temp query bus Project * * owner Jim where 
"originated < ’1/1/99’ and current == open";

The following command returns the number of open, new Projects that do not belong to Jim:

eval expression "count TRUE" on ( set openprojects AND set newprojects ) 
less temp query bus Project * * owner Jim;

The following command returns the number of Projects that are either owned by Jim or included in the set “Q4 Projects”:

eval expression "count TRUE" on ( temp query bus Projects * * owner Jim ) OR set "Q4 Projects";