Useful Built-in functions of SSRS

The following functions are built-in to the reporting engine processor. If you don't see a function that provides the capability you want then you can add additional by creating or using your own user functions via the code defined in the report or the defined external classes.

Aggregate(field expr [,scope])
Returns an array containing the values of the grouped field. For example, =Code.AggrToString(Aggregate(Fields!Year.Value)) // In Code element Public Function AggrToString(o as object) As String Dim ar as System.Collections.ArrayList = o Dim sb as System.Text.StringBuilder = New System.Text.StringBuilder Dim n as Integer For n = 0 To ar.Count-1 sb.Append(ar(n)) If n <>

Asc(string)
Converts the first letter in the passed string to ANSI code.
Avg(field expr [,scope])
Returns the average value of the grouped field. Returns decimal if the argument type is decimal, otherwise double.

CBool(object)
Converts the passed argument to Boolean.

CByte(string)
Converts the passed argument to Byte.

CCur(string)
Converts the argument to type Currency (really Decimal).

Choose(number, expr1, expr2, ... exprn
Evaluates the number and return the result of the coorespodning exprn. For example, if number results in 3 then expr3 is returned.

CDate(string)
Converts a string to type DateTime

CDbl(object)
Converts the passed parameter to double.

Chr(int)
Converts the specified ANSI code to a character.

CInt(object)
Converts the argument to integer.

CLng(object)
Converts the argument to long.

Count(field expr [,scope])
Returns the number of values in the grouped field. Null values don't count.

Countrows([scope])
Returns the number of rows in the group.

Countdistinct(field expr [,scope])
Returns the number distinct values in the grouped field. Null values don't count.

CSng(object)
Converts the argument to Single.

CStr(object)
Converts the argument to String.

Day(datetime)
Returns the integer day of month given a date.

First(field expr [,scope])
Returns the first value in the group.

Format(string1 [,string2)
Format string1 using the format string2. Some valid formats include '#,##0', '$#,##0.00', 'MM/dd/yyyy', 'yyy-MM-dd HH:mm:ss'... string2 is a .NET Framework formatting string.

Hex(number)
Returns the hexadecimal value of a passed number.

Hour(datetime)
Returns the integer hour given a date/time variable.

Iif(bool-expr, expr2, expr3
The Iif function evaluates bool-expr and when true returns the result of expr2 otherwise the result of expr3. expr2 and expr3 must be the same data type.

InStr([ioffset,] string1, string2 [,icase])
1 based offset of string2 in string1. You can optionally pass an integer offset as the first argument. You can also optionally pass a 1 as the last argument if you want the search to be case insensitive.

InStrRev(string1, string2[,offset[,case]])
1 based offset of string2 (second argument) in string1 (first argument) starting from the end of string1. You can optionally pass an integer offset as the third argument. You can also optionally pass a 1 as the fourth argument if you want the search to be case insensitive.

Last(field expr [,scope])
Returns the last value in the group.

LCase(string)
Returns the lower case of the passed string.

Left(string)
Returns the left n characters from the string.

Len(string)
Returns the lenght of the string.

LTrim(string)
Removes leading blanks from the passed string.

Max(field expr [,scope])
Returns the maximum value in the group.

Mid
Returns the portion of the string (arg 1) denoted by the start (arg 2) and length (arg 3).

Min(field expr [,scope])
Returns the minimum value in the group.

Minute(datetime)
Returns the integer minute given a date/time variable.

Month(datetime)
Returns the integer month given a date.

MonthName(datetime)
Get the month name given a date. If the optional second argument is 'True' then the abbreviated month name will be returned.

Next(field expr [,scope])
Returns the value of the next row in the group.

Oct(number)
Returns the octal value of a specified number.

Previous(field expr [,scope])
Returns the value of the previous row in the group.

Replace
Returns a string replacing 'count' instances of the searched for text (optionally case insensitive) starting at position start with the replace text. The function form is Replace(string,find,replacewith[,start[,count[,compare]]]).

Right(string, number)
Returns a string of the rightmost characters of a string.

Rownumber()
Returns the row number.

RTrim(string)
Removes trailing blanks from string.

Runningvalue(field expr, string1 [,scope])
Returns the current running value of the specified aggregate function. string1 is an expression returning one of the following aggregate function: "sum", "avg", "count", "max", "min", "stdev", "stdevp", "var", "varp".

Second(datetime)
Returns the integer second given a date/time variable.

Space(number)
Returns a string containing the number of spaces requested.

Stdev(field expr [,scope])
Returns the standard deviation of the group.
Stdevp(field expr [,scope])
Returns the standard deviation of the group. Use stdevp instead of stdev when the group contains the entire population of values.

StrComp(string1, string2, compare)
Compares the strings; optionally with case insensitivity. When string1 < string1 =" string2:"> string2: 1

String(number, char)
Return string with the character repeated for the length.

StrReverse(string)
Returns a string with the characters reversed.

Sum(field expr [,scope])
Returns the total of the group.

Switch(bool-expr, result1 [, bool-expr-n, result-n])
The arguments are pairs of expression. When the bool-expr is true the result is returned. bool-expr-n is evaluated until one is results in true then the cooresponding result-n expression is returned.

Today()
Return the current date/time on the computer running the report.

Trim(string)
Removes whitespace from beginning and end of string.

UCase(string)
Returns the uppercase version of the string.
Var(field expr [,scope])
Returns the variance of the group.

Varp(field expr [,scope])
Returns the variance of the group. Use varp instead of var when the group contains the entire population of values.

Year(datetime)
Obtains the year from the passed date.

Weekday()
Returns the integer day of week: 1=Sunday, 2=Monday, ..., 7=Saturday given a date.

WeekdayName(iday [,abbr])
Returns the name of the day of week given the integer Weekday. The optional second argument will return the abbreviated day of week if 'True'.