Model Designer Functions

Article
12/03/2008

You can use Report Model Designer functions to calculate new attributes. For example, suppose you want to know the Total Sales per sales order. This figure is comprised of the sales amount plus the tax paid for the order. Using the Add function, you can add each sales total to the tax paid for each order. The list below describes the available functions, their requirements, and their behaviors.

Scalar Functions

Using scalar functions, you can perform calculations on a single argument to return a new single value for a field.

Function Name	Argument	DataType	Cardinality	Description
Add	Item1	Numeric	1	First item to add.
	Item2	Numeric	1	Second item to add.
	Return			Type is Float if either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.
Subtract	Item1	Numeric	1	Item from which to subtract.
	Item2	Numeric	1	Item to subtract.
	Return			Type is Float is either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.
Multiply	Item1	Numeric	1	First item to multiply.
	Item2	Numeric	1	Second item to multiply.
	Return			Type is Float if either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.
Divide	Item1	Numeric	1	Item to divide into.
	Item2	Numeric	1	Item to divide by.
	Return			Type is Float if either item is Float; otherwise, Decimal.
Power	Base	Numeric	1	Base to take to a power.
	Exponent	Numeric	1	The exponent.
	Return			Type is Float if either item is Float; otherwise, Decimal if either item is Decimal; otherwise, Integer.
Negate	Item	Numeric	1	Item to negate.
	Return			Type is the same as the Item.
Mod	Item1	Integer	1	Item to divide into.
	Item2	Integer	1	Item to divide by.
	Return	Integer		The remainder of the division.
Equals	Item1	Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey	1	First item to compare.
	Item2		1	Second item to compare. Item1 and Item2 must have the same data type.
	Return	Boolean		Indicates whether the items are the same.
NotEquals	Item1	Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey	1	First item to compare.
	Item2		1	Second item to compare. Item1 and Item2 must be the same data type.
	Return	Boolean		Indicates whether the items are not the same.
GreaterThan	Item1	DateTime, Integer, Decimal, Float, or String	1	First item to compare.
	Item2		1	Second item to compare. Item1 and Item2 must be the same data type.
	Return	Boolean		Indicates whether the first item is greater than the second item.
GreaterThanOrEquals	Item1	DateTime, Integer, Decimal, Float, or String	1	First item to compare.
	Item2		1	Second item to compare. Item1 and Item2 must be the same data type.
	Return	Boolean		Indicates whether the first item is greater than or equal to the second item.
LessThan	Item1	DateTime, Integer, Decimal, Float, or String	1	First item to compare.
	Item2		1	Second item to compare. Item1 and Item2 must be the same data type.
	Return	Boolean		Indicates whether the first item is less than the second item.
LessThanOrEquals	Item1	DateTime, Integer, Decimal, Float, or String	1	First item to compare.
	Item2		1	Second item to compare. Item1 and Item2 must be the same data type.
	Return	Boolean		Indicates whether the first item is less than or equal to the second item.
And	Item1	Boolean	1	First condition.
	Item2	Boolean	1	Second condition. If Item1 is false, Item2 is not evaluated.
	Return	Boolean		If both Item1 and Item2 are true, the Return value is true.
Or	Item1	Boolean	1	First condition.
	Item2	Boolean	1	Second condition. If Item1 is true, Item2 is not evaluated.
	Return	Boolean		If either Item1 or Item2 are true, the Return value is true.
Not	Item	Boolean	1	Condition to negate.
	Return	Boolean		If Item is false, the Return value is true.
Truncate	Item	Decimal or Float	1	Item to truncate.
	Digits	Integer	1	Number of decimal digits to which the item should be truncated. For example, specify three to truncate the item after the third digit the right of the decimal.
	Return			The Return value type is the same as the item being truncated.
Round	Item	Decimal or Float	1	Item to round.
	Digits	Integer	1	Number of decimal digits that the item should be rounded to. For example, specify three to round the item to the third digit to the right of the decimal.
	Return			The Return value type is the same as the item being rounded.
Integer	Item	Numeric or String	1	Item to cast. In Report Builder, Integer is listed on the Function tab as INT.
	Return	Integer		The item cast as an integer. If the item is a numeric value, it is truncated. Note that the invariant locale is used to cast numeric strings. A period is the decimal separator. The comma used to separate thousands is not allowed.
Decimal	Item	Numeric or String	1	Item to cast.
	Return	Decimal		The item cast as a decimal.
Float	Item	Numeric or String	1	Item to cast.
	Return	Float		The item cast as a float.
String	Item	Numeric	1	Item to cast. In Report Builder, String is listed on the Function tab as TEXT.
	Return	String		Item to cast as a string.
Length	String	String	1	String used to determine the length.
	Return	Integer		Length of the string, specified as the number of characters within the string.
Find	String	String	1	String that is searched for a contained string.
	Substring	String	1	The substring to search for.
	Return	Integer		Position of the first instance of the substring within the string. If the substring is not found, 0 is returned.
Substring	String	String	1	String from which a substring is extracted.
	Start	Integer	1	Start position within the string (1-based).
	Length	Integer	1	Number of characters.
	Return	String		The substring extracted from the string that contains the characters from Start to Start+Length.
Left	String	String	1	String from which to obtain the leftmost characters.
	Length	Integer	1	Number of characters.
	Return	String		The substring of the string that contains the characters from 1 to Length.
Right	String	String	1	String from which to obtain the rightmost characters.
	Length	Integer	1	Number of characters.
	Return	String		The substring of the string from Length(String)-Length+1 to Length(String).
Concat	String1	String	1	First string to concatenate.
	String2	String	1	Second string to concatenate.
	Return	String		The second string concatenated to the end of the first string.
Lower	String	String	1	String to convert to lowercase.
	Return	String		String with all uppercase characters converted to lowercase.
Upper	String	String	1	String to convert to upper case.
	Return	String		String with all lowercase characters converted to uppercase.
LTrim	String	String	1	String from which to trim leading spaces.
	Return	String		String with all leading spaces removed.
RTrim	String	String	1	String from which to trim trailing spaces.
	Return	String		String with trailing spaces removed.
Replace	String	String	1	String in which to replace all instances of one substring with another.
	Find	String	1	The substring to search for.
	Replace	String	1	The substring that replaces the Find string.
	Return	String		String with all instances of Find replaced with Replace.
Date	Year	Integer	1	Year for the date.
	Month	Integer	1	Month (1-12) for the date.
	Day	Integer	1	Day (1-31) for the date which must be a valid day of the specified month and year.
	Return	DateTime		A datetime with the given year, month, and day at 00:00:00.
DateTime	Year	Integer	1	Year for the date.
	Month	Integer	1	Month (1-12) for the date.
	Day	Integer	1	Day (1-31) for the date which must be a valid day of the specified month and year.
	Hour	Integer	1	Hour (0-23) for the time.
	Minute	Integer	1	Minute (0-59) for the time.
	Second	Decimal	1	Second (0-60) for the time.
	Return	DateTime		A datetime with the specified year, month, day, hour, minute, and second.
Year	DateTime	DateTime	1	Date from which the year is extracted.
	Return	Integer		Year of the datetime.
Quarter	DateTime	DateTime	1	Date from which the quarter is extracted.
	Return	Integer		Quarter (1-4) of the datetime.
Month	DateTime	DateTime	1	Date from which the month is extracted.
	Return	Integer		Month (1-12) of the datetime.
Day	DateTime	DateTime	1	Date from which the day is extracted.
	Return	Integer		Day (1-31) of the datetime.
Hour	DateTime	DateTime	1	Date from which the hour is extracted.
	Return	Integer		Hour (0-23) of the datetime.
Minute	DateTime	DateTime	1	Date from which the minute is extracted.
	Return	Integer		Minute (0-59) of the datetime.
Second	DateTime	DateTime	1	Date from which the second is extracted.
	Return	Integer		Second (0-60) of the datetime.
DayofYear	DateTime	DateTime	1	Date from which the day of the year is extracted.
	Return	Integer		Day of year (1-366) of the datetime.
Week	DateTime	DateTime	1	Date from which the week is extracted.
	Return	Integer		Week (1-53) of the datetime. The first day of the week is determined by the default first day of week associated with the culture of the semantic model.
DayofWeek	DateTime	DateTime	1	Date from which the day of the week is extracted.
	Return	Integer		Day of week (1-7) of the datetime. Values start with Monday=1 through Sunday=7.
Date	DateTime	DateTime	1	Date from which to remove the time. In Report Builder, this Date function is listed on the Function tab as DATEONLY.
	Return	Integer		Datetime with the time cleared (00:00:00).
Now	Return	DateTime		Current date/time. Now is a static function.
Today	Return	DateTime		Current date/time with the time cleared (00:00:00). Today is a static function.
DateDiff	Interval	String	1	The units used to specify the date difference. Must be one of the following: Year, Quarter, Month, Day, Hour, Minute, Second, or Week. Must be a literal.
	Start	DateTime	1	The start datetime.
	End	DateTime	1	The end datetime.
	Return	Integer		The difference between the Start datetime and the End datetime, in the same units specified in the Interval. If the start datetime is after the end datetime, the result is negative.
DateAdd	Interval	String	1	The units used to specify the date addition. Must be one of the following: Year, Quarter, Month, Day, Hour, Minute, Second, or Week. Must be a literal.
	Number	Integer	1	The number of units of Interval to add to the datetime.
	DateTime	DateTime	1	The datetime to add to.
	Return	DateTime		The datetime that is the result of adding the specified number of Interval units to the original datetime.

Aggregate Functions

Using aggregate functions, you can perform calculations on a set of values or a single value, and then return a single value for an expression.

Function Name	Argument	DataType	Cardinality	Description
Sum	Items	Numeric	N	Items to sum.
	Return		1	The sum of values for all of the items. The return value is the same data type as the items data type.
Avg	Items	Numeric	N	Items to average. In Report Builder, Avg is listed on the Function tab as AVERAGE.
	Return		1	The average of non-null values of the Items. The data type is Decimal if the items are Decimal or Integer, otherwise, Float.
Max	Items	DateTime, Integer, Decimal, Float, or String	N	Items sorted to determine the maximum.
	Return		1	The maximum of non-null values of the item. The return value is the same data type as the items data type.
Min	Items	DateTime, Integer, Decimal, Float, or String	N	Items sorted to determine the minimum.
	Return		1	The minimum of non-null values of the Items. The return value is the same data type as the items data type.
Count	Items	Any	N	Items to count.
	Return	Integer	1	The count of non-null values of the Items.
CountDistinct	Items	Any	N	Items to count. The data type of the items cannot be EntityKey.
	Return	Integer	1	The count of distinct non-null values of the Items.
StDev	Items	Numeric	N	Items used to determine the standard deviation.
	Return	Float	1	The standard deviation of non-null values of the Items.
StDevP	Items	Numeric	N	Items used to determine the population standard deviation.
	Return	Float	1	The population standard deviation of non-null values of the Items.
Var	Items	Numeric	N	Items used to determine the variance.
	Return	Float	1	The variance of non-null values of the Items.
VarP	Items	Numeric	N	Items used to determine the population variance.
	Return	Float	1	The population of variance of non-null values of the Items.

Information Functions

Using the Information functions, you can get basic information about your users.

Function Name	Argument	DataType	Cardinality	Description
GetUserID	Return	String	1	The user's user ID. GetUserID is a static function.
GetUserCulture	Return	Language	1	The user's language or locale. GetUserCulture is a static function.

Other Functions

In addition to the functions discussed above, the following functions are used in Semantic Model Definition Language.

Function Name	Argument	DataType	Cardinality	Description
Filter	Filter Items	Any	N	The values to be filtered. To filter items, use the Filter dialog box.
	Filter Condition	Boolean	1	Indicates whether to include the corresponding instance.
	Return		N	The returned value data type is the same as the Filter Items.
In	Item	Boolean, DateTime, Integer, Decimal, Float, String, or EntityKey	1	The item to check for set membership.
	Set		N	This must be a Literal expression with no Path. The Item and the Set must have the same data type.
	Return	Boolean	1	Indicates whether the item is in the set.
If	Condition	Boolean	1	Condition to test.
	TrueCase	Any	1	Value to return if the condition is true. If the condition is false, TrueCase is not evaluated. Cannot be an EntityKey data type.
	FalseCase	Any	1	Value to return if the condition is false. FalseCase must have the same value type as TrueCase. If the condition is true, FalseCase is not evaluated.
	Return		1	The type must be the same as TrueCase.
Switch	Condition1	Boolean	1	Condition to test. Switch can have zero or more additional Condition/Value pairs:
	Value1	Any	1	Value to return if condition1 is true.
	Condition/N	Boolean	1	Condition to test. Not evaluated if any earlier condition is true.
	Value/N	Boolean	1	Value to return if Condition/N is true. It must have the same data type as Value1. Not evaluated if Condition/N is not evaluated or if Condition/N is false.
	Return	Boolean	1	Type is the same as the Value1. Returns Null if all conditions are false.
Evaluate	Expression	Any	N	The expression to evaluate. This function is used for controlling the entity context in which an expression is evaluated. For example, the following expression averages the prices for each distinct product the customer ordered: Avg([customer->order->product]Price). The following expression averages the prices (obtained from the product entity) for each order the customer placed: Avg([customer->order]Evaluate([order->product]Price)). There are multiple points along a path where evaluation location can control distinctness of an aggregate, multiple independent uses of Evaluate may be possible. The following is an example of multiple independent uses of Evaluate in a model where each product can be produced by multiple manufacturers: Average([customer->order]Evaluate([order->product]Evaluate([product->manufacturer->city]population)))
	Return		N	Type is the same as the expression.
Aggregate	Expression	Any	N	The aggregate expression to evaluate. This function is used to control the entity context in which an aggregation is evaluated. For example, consider a TotalSales attribute defined on an Order entity. The expression to show the total sales within the context of an order is simply: TotalSales. The expression to calculate the total sales within the context of a customer would be: Aggregate([customer->order]TotalSales). The expression argument must contain a non-anchored expression or one or more nested passthrough functions (which must take any data type), where the innermost passthrough argument has a non-anchored ExpressionNode. For example, Aggregate([customer->order]Filter([order->product]Sum(UnitPrice),=(Shipped, "True")).
	Return		1	Type is the same as the expression.

Model Designer Functions

Scalar Functions

Aggregate Functions

Information Functions

Other Functions

See Also

Concepts

Help and Information

Additional resources