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 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. |
See Also
Concepts
Model Designer Object Properties
Model Designer Query Properties
Model Designer Data Types
Using Functions (Report Builder)