Expressions
Use expressions to perform tasks including math, string manipulation and retrieving data. Expressions are used in the record box element and record area element. Expressions in Designer consists of operations, functions, aggregate functions, and conditions.
Using Expressions
Use expressions in a record box or record area element's dataName property. Elements with a color or textColor property can also use expressions to dynamically change that element's color.
- Use expressions in the
dataNameproperty of record boxes or record areas. - Use expression in the
colorortextColorproperty to change an element's color.
dataName
Expressions can be used by a record box or a record area in the element's dataName property. These expressions are placed between pound signs and are replaced with the evaluated value in the report output.
Hello #FullName#. Your order total is #Add(SubTotal, Freight)#.
The dataName property of a record box can contain an expression. Although the property can contain only one expression, that expression can be nested.
color or textColor
Expressions can be used by any element containing a color or textColor property to dynamically change that element's color
IF(LTE(num,0),"red",LT(num,10),"yellow")
Operators
| Operator | Examples | Description |
|---|---|---|
| a + b | SubTotal + Freight | Adds two numbers together or concatenates two strings. |
| a - b | Total - Tax | Subtracts b from a. |
| a / b | Total / 12 | Divides a by b. |
| a * b | UnitPrice * Quantity | Multiplies two numbers together. |
| a % b | QuantityA % QuantityB | Computes the remainder after dividing a by b. |
| - a | - Price | Numeric negation of a |
| a == b | QuantityA == QuantityB | Returns true if a is equal to b. |
| a > b | QuantityA > QuantityB | Returns true if a is greater than b. |
| a < b | QuantityA < QuantityB | Returns true if a is less than b. |
| a >= b | QuantityA >= QuantityB | Returns true if a is greater or equal to b. |
| a <= b | QuantityA <= QuantityB | Returns true if a less than or equal to b. |
| a != b | QuantityA != QuantityB | Returns true if a is not equal to b. |
| !bool | !BoolA | Returns boolean negation (returns true if operand is false). |
| a | b | QuantityA | QuantityB | Returns the logical disjunction on two Boolean expressions, or a bitwise disjunction on two numeric expressions. |
| a & b | QuantityA & QuantityB | Returns the logical conjunction on two Boolean expressions, or a bitwise conjunction on two numeric expressions. |
| a ~ b | QuantityA ~ QuantityB | Returns the bitwise complement operation on its operand, which has the effect of reversing each bit. |
| a ^ b | QuantityA ^ QuantityB | Returns the logical exclusion on two Boolean expressions, or a bitwise exclusion on two numeric expressions. |
Functions
Functions allow specifying the dataName associated with the JSON field. For example Sum(Multiply(A,B), SubReportName).
| Function | Examples | Description |
|---|---|---|
| Abs( a ) | Abs( Total ) | Returns the absolute value of a. |
| Add( a, b ) | Add( SubTotal, Freight ) | Adds two numbers together. |
| And( a, b ) | And( QuantityA, QuantityB ) | Returns the logical conjunction on two Boolean expressions, or a bitwise conjunction on two numeric expressions. |
| Ceiling( a ) | Ceiling( Total ) | Returns the smallest integer that is not less than a. |
| Concat( a, b ) | Concat( FirstName, LastName ) | Returns the concatenated string. |
| CurrentDateTime( ) | CurrentDateTime( ) | Returns the current date and time. |
| DateAdd( datepart, number, date ) | DateAdd( Year, 1, OrderDate ) | Returns a new datetime value based on adding an interval to the specified date. |
| DateDiff( datepart, startdate, enddate ) | DateDiff( Day, OrderDate, ShipDate ) | Returns the number of date and time boundaries crossed between two specified dates. |
| Day( a ) | Day( OrderDate ) | Returns the day from the date. |
| Divide( a, b ) | Divide( Total, 12 ) | Divides a by b. |
| EQ( a, b ) | EQ( QuantityA, QuantityB ) | Returns true if a is equal to b. |
| Floor( a ) | Floor( Total ) | Returns the nearest integer that is less than or equal to a. |
| Format( a, b ) | Format( UnitPrice, "0.00" ) | Formats a using the b format. |
| FV( a, b, c[, d[, e&cd; ) | FV( Rate, Period, Payment, PresentValve, DueDate ) | Returns the future value of an investment. |
| GT( a, b ) | GT( QuantityA, QuantityB ) | Returns true if a is greater than b. |
| GTE( a, b ) | GTE( QuantityA, QuantityB ) | Returns true if a is greater or equal to b. |
| Hour( a ) | Hour( "12/12/2006 4:11:05 PM") | Returns the hour from the date. |
| IIF( a[, b[, c&cd; ) | IIF(GTE( 2, 0 ), FirstName, LastName) | Returns one of two values depending on the value of 'a'. |
| IsNull( a[, b] ) | IsNull( FirstName ) | Determines whether a specified value is null. |
| Left( a, b ) | Left( Name, 1 ) | Returns the first b letters from a. |
| Len( a ) | Len( Name ) | Returns the length of the string. |
| LT( a, b ) | LT( QuantityA, QuantityB ) | Returns true if a is less than b. |
| LTE( a, b ) | LTE( QuantityA, QuantityB ) | Returns true if a less than or equal to b. |
| Minute( a ) | Minute( OrderDate ) | Returns the minute from the date. |
| Mod( a, b ) | Mod( QuantityA, QuantityB ) | Returns the remainder of a divided by b. |
| Month( a ) | Month( "12/12/2006 4:11:05 PM" ) | Returns the month from the date. |
| Multiply( a, b ) | Multiply( UnitPrice, Quantity ) | Multiplies two numbers together. |
| Negate( a ) | Negate( Total ) | Returns the negative of a. |
| Not( a ) | Not( QuantityA ) | Returns the logical negation on a Boolean expression. |
| NotEq( a, b ) | NotEq( OrderTotal1, OrderTotal2 ) | Returns a true if a and b are not equal, otherwise returns a false. |
| Or( a, b ) | Or( QuantityA, QuantityB ) | Returns the logical disjunction on two Boolean expressions, or a bitwise disjunction on two numeric expressions. |
| PMT( a, b, c[, d[, e&cd; ) | PMT( Rate, Period, PresentValue, FutureValve, DueDate ) | Returns a value specifying the payment for an annuity. |
| Pow( a, b ) | Pow( 2, 3 ) | Returns the value of a raised to the power b. |
| PV( a, b, c[, d[, e&cd; ) | PV( Rate, Period, Payment, FutureValve, DueDate ) | Returns the present value of an investment. |
| Rate( a, b, c[, d[, e[, f&cd;] ) | Rate( Period, Payment, PresentValue, FutureValve, DueDate, Guess ) | Returns a value specifying the interest rate per period for an annuity. |
| Replace( a, b ) | Replace( FirstName, "a", "b" ) | Replaces all occurrences of a specified character or String with another specified Unicode character or String. |
| Round( a, b ) | Round( Total, Count ) | Returns number a rounded to b decimal places. |
| Right( a, b ) | Right( Name, 1 ) | Returns the last b letters from a. |
| Second( a ) | Second( "12/12/2006 4:11:05 PM") | Returns the second from the date. |
| Sqrt( a ) | Sqrt( Number ) | Returns the square root of the specified numeric expression. |
| StrComp( a, b ) | StrComp( FirstName, LastName ) | Returns a value indicating the result of a string comparison. |
| StrReverse(a) | StrReverse( Name ) | Returns a string in which the character order 'a' is reversed. |
| SubString( a, b, c ) | SubString( Name, 1, 4 ) | Returns 'c' characters from string 'a' starting from position 'b'. |
| Subtract( a, b ) | Subtract( Total, Tax ) | Subtracts b from a. |
| Tilde( a ) | Tilde( QuantityA, QuantityB ) | Returns the bitwise complement operation on its operand, which has the effect of reversing each bit. |
| Trim( a ) | Trim( Name ) | Removes the white space before and after the string. |
| ToLower( a ) | ToLower( Name ) | Changes all letters to lowercase. |
| ToUpper( a ) | ToUpper( Name ) | Changes all letters to uppercase. |
| WeekDayName( a ) | WeekDayName( "12/12/2006 4:11:05 PM" ) | Returns a string indicating the specified day of the week. |
| Xor( a, b ) | Xor( QuantityA, QuantityB ) | Returns the logical exclusion on two Boolean expressions, or a bitwise exclusion on two numeric expressions. |
| Year( a ) | Year( OrderDate ) | Returns the year from the date. |
Functions (Fonts and Colors)
Fonts and colors have an IF statement that allows conditionally formatting fonts or colors.
| function | example | description |
|---|---|---|
| IF(e1, c1, [e2, c2, ...] cd) | IF(LTE(num,0),"red","green") | If expression, then display color, else display color. |
| IF(LTE(num,0),"red",LT(num,10),"yellow","green") | If expression, then display color, else if expression, then display color, else display default color. | |
| IF(e1, f1, [e2, f2, ...] fd) | IF(LTE(num,0),"Courier","Helvetica") | If expression, then display font, else display font. |
| IF(LTE(num,0),"Courier",LT(num,10),"Helvetica","TimesRoman") | If expression, then use font, else if expression, then use font, else use default font. |
The [e2, c2, ...] portion of the function means that the else if statement can be repeated multiple times. For example,
IF(LTE(num,0),"Courier",LT(num,10),"Helvetica",GT(num,100),"HelveticaBold","TimesRoman")
translates to:
if num < 0 then Courier, else if num < 10 then Helvetica else if num >100 then HelveticaBold else TimesRoman.
Aggregate Functions
| Function | Examples | Description |
|---|---|---|
| Count( expression ) | Count( Fax ) or Count( * ) | Counts all of the non null items of expression. Use a "*" for counting all items of the record set. |
| Sum( expression ) | Sum( LineTotal ) or Sum( Multiply( UnitPrice, Quantity ) ) | Sums all of the expression values. |
| First( expression ) | First( Fax ) | Returns the first expression value of all items of the record set. |
| Last( expression ) | Last( Fax ) | Returns the last expression value of all items of the record set. |
| Mean( expression ) | Mean( Quantity ) | Returns the average of all of the expression values. |
| Median( expression ) | Median( ProductName ) | Returns the median expression value. |
| Mode( expression ) | Mode( Marks ) | Returns the least most frequent expression value. |
| Max( expression ) | Max( Quantity ) | Returns the maximum expression value. |
| Min( expression ) | Min( Quantity ) | Returns the minimum expression value. |
| StDev( expression ) | StDev( Quantity ) | Returns the standard deviation of a numeric expression evaluated over a set. |
The following examples illustrate using expressions in Designer.
Figure 1. Two expressions in a dlex file.
Figure 2. An aggregate expression in a dlex file.
Page Level Aggregate Functions
Any aggregate function can be executed at the page level and the calculation use the number of records on the page.
Page level aggregate function calculations are based on the number of records in the page.
By default, an aggregate function uses the entire report to calculate the result of that function. To use the page as the scope for the function you specify either CurrentPage or PreviousPage as the scope for that aggregate function. This is useful for instance if you wanted to keep a page total of a particular field for each page. Page level aggregate functions take the expression and the scope. Valid scopes are CurrentPage and PreviousPage.
Current Page
Specifying CurrentPage scope gives the aggregate value of the current page. For example, the following sums the unit price and quantity to provide a total for the current page.
Sum(UnitPrice*Quantity, CurrentPage)
Figure 3. Specifying CurrentPage scope in a footer.
Previous Page
Specifying PreviousPage scope provides the aggregate value of the previous page.
Page level aggregate functions cannot be used on fixed pages. Also, page level aggregate functions can only to be used in the Header and Footer of a Report or Subreport. Using in a Detail section results in an exception.