Built-in Functions of SSRS
In Reporting Services, expressions are used to provide dynamic flexibility for controlling
the content and appearance of a report. Some expressions are created for you automatically.
For example, when you create a new report and drag fields from datasets onto report
items, the values of text boxes are automatically set to expressions that refer
to the dataset fields by name. During report processing, each expression evaluates
to a single value that replaces the expression when a report is rendered. Expressions
are also used throughout the report definition to specify or calculate values for
report item properties, text box properties, parameters, queries, bookmarks, document
maps, filters, and group and sort definitions.
You create expressions on report items by entering text in text boxes, in properties,
or through a dialog box. An expression is written in Microsoft Visual Basic. An
expression begins with an equal sign (=) and consists of references to constants,
operators, functions, and built-in report global collections including fields and
parameters. You can also create references to .NET Framework classes, custom code,
and assemblies external to report processing.
Examples
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'.