Model Designer Functions
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 consists 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 (1based). 

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 (112) for the date. 

Day 
Integer 
1 
Day (131) 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 (112) for the date. 

Day 
Integer 
1 
Day (131) for the date which must be a valid day of the specified month and year. 

Hour 
Integer 
1 
Hour (023) for the time. 

Minute 
Integer 
1 
Minute (059) for the time. 

Second 
Decimal 
1 
Second (060) for the time. 

Return 
DateTime 
A datetime with the specified year, month, day, hour, minute, and second. 

Time 
DateTime 
DateTime 
1 
Datetime from which to extract the time. Returns the hour, minute, and second from a datetime value. 
Return 
Time 
Time from the datetime. 

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 (14) of the datetime. 

Month 
DateTime 
DateTime 
1 
Date from which the month is extracted. 
Return 
Integer 
Month (112) of the datetime. 

Day 
DateTime 
DateTime 
1 
Date from which the day is extracted. 
Return 
Integer 
Day (131) of the datetime. 

Hour 
DateTime 
DateTime or Time 
1 
Date or time from which the hour is extracted. 
Return 
Integer 
Hour (023) of the datetime. 

Minute 
DateTime 
DateTime or Time 
1 
Date or time from which the minute is extracted. 
Return 
Integer 
Minute (059) of the datetime. 

Second 
DateTime 
DateTime or Time 
1 
Date or time from which the second is extracted. 
Return 
Integer 
Second (060) of the datetime. 

DayofYear 
DateTime 
DateTime 
1 
Date from which the day of the year is extracted. 
Return 
Integer 
Day of year (1366) of the datetime. 

Week 
DateTime 
DateTime 
1 
Date from which the week is extracted. 
Return 
Integer 
Week (153) 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 (17) 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. If the formula calculates the difference in a start time and an end time, the interval unit may only be HOUR, MINUTE, or SECOND. 
Start 
DateTime or Time 
1 
The start date or time. 

End 
DateTime or Time 
1 
The end date or time. Must have the same data type as the start time. 

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 or time addition. Must be one of the following: Year, Quarter, Month, Day, Hour, Minute, Second, or Week. Must be a literal. If the formula adds a number of time interval units to a start time, the interval unit may only be HOUR, MINUTE, or SECOND. 
Number 
Integer 
1 
The number of units of Interval to add to the date or time. 

DateTime 
DateTime or Time 
1 
The date or time to add to. 

Return 
DateTime 
The date or time that is the result of adding the specified number of Interval units to the original date or time. Must have the same data type as the original start datetime or time field. 
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 nonnull 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 nonnull 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 nonnull 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 nonnull 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 nonnull values of the Items. 

StDev 
Items 
Numeric 
N 
Items used to determine the standard deviation. 
Return 
Float 
1 
The standard deviation of nonnull values of the Items. 

StDevP 
Items 
Numeric 
N 
Items used to determine the population standard deviation. 
Return 
Float 
1 
The population standard deviation of nonnull values of the Items. 

Var 
Items 
Numeric 
N 
Items used to determine the variance. 
Return 
Float 
1 
The variance of nonnull values of the Items. 

VarP 
Items 
Numeric 
N 
Items used to determine the population variance. 
Return 
Float 
1 
The population of variance of nonnull 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 nonanchored expression or one or more nested passthrough functions (which must take any data type), where the innermost passthrough argument has a nonanchored ExpressionNode. For example, Aggregate([customer>order]Filter([order>product]Sum(UnitPrice),=(Shipped, "True")). 
Return 
1 
Type is the same as the expression. 