SSRS Functions

This article will show you the list of all the built-in functions available in SSRS or SQL Server Reporting Services with a brief description and a simple example. We have separated the SSRS functions into different sections, each dealing with distinct criteria. You can click any hyperlink to delve deeper into the corresponding function.

SSRS String or Text Functions

The following list of string or text functions in SSRS helps you deal with string columns. In real-time, you might get dates and integers in string format. In that case, you can use the following functions to format them to the proper data type. Apart from that, you can trim the extra spaces, extract a substring (portion of the actual string), split, and replace the text.

String FunctionsDescriptionExample
AscIt returns the ASCII code (integer value) of the character.Asc(Fields!Name.Value)
AscWIt returns an Integer value (ASCII Character code) of the corresponding character.AscW(Fields!Name.Value)
ChrIt returns the character associated with the ASCII code (integer value).Chr(84)
ChrWIt returns the character associated with the given integer character code (ASCII code).ChrW(100)
FilterIt formats the given field as a currency value and returns an expression that contains the system’s default currency symbol.Filter(Parameters!MultiItem.Value, “3”, True, CompareMethod.Binary)
FormatIt returns a string value formatted according to instructions given in a format String expression.Format(Fields!OrderDate.Value, “Short Date”)
FormatCurrencyIt returns the substring containing the given number of left-side characters.FormatCurrency(Fields!Sales.Value, 0)
FormatDateTimeIt returns a string expression representing a Date & Time value.FormatDateTime(Fields!OrderDate.Value, DateFormat.GeneralDate)
FormatNumberIt returns an expression formatted as a number.FormatNumber(Fields!Sales.Value, 1)
FormatPercentIt returns an expression formatted as a percentage with the % symbol.FormatPercent(Fields!Orders.Value / Sum(Fields!Orders.Value, “DataSet”), 2)
GetCharIt returns the character from the string field at the given index position.GetChar(Fields!ProductName.Value, 4)
InStrIt searches for the given substring inside a string field and returns the first occurring position (integer index position).InStr(Fields!ProductDescription.Value, “Bike”)
InStrRevIt searches for the given substring from right to left(reverse) inside a string field and returns the first occurring index position (integer).InStrRev(Fields!ProductDescription.Value, “Bike”)
JoinIt joins the given number of substrings in an array separated by the second argument and returns a string.Join(Parameters!Country.Value, “, “)
LCaseIt converts the given string or character field to Lowercase.LCase(Fields!ProductName.Value)
LeftIt returns a Left-side portion of the given string adjusted to the specified length.Left(Fields!ProductName.Value, 2)
LenIt returns either the total characters in a string or the number of bytes required to store a variable.Len(Fields!Name.Value)
LSetIt uses the last two arguments to extract and return a portion (substring) from the given string.LSet(Fields!ProductName.Value, 4)
LTrimIt trims or removes the leading spaces (Beginning) from the given field.LTrim(Fields!Address.Value)
MidIt returns the Right side portion of the given string adjusted to the specified length.Mid(Fields!ProductName.Value, 2, 6)
ReplaceIt helps to replace the matching text with another substring.Replace(Fields!ProductName.Value,”Bike”,”Cycle”)
RightIt extracts the given number of Right side characters (substring) from the string field.Right(Fields!ProductName.Value, 5)
RSetIt compares the string and returns -1, 0, or 1 based on the result.RSet(Fields!ProductName.Value, 5)
RTrimIt trims or removes the trailing spaces (Ending) from the given field.RTrim(Fields!Address.Value)
SpaceIt returns the given number of empty spaces.Space(2)
SplitIt splits the given string based on the second argument and returns the one-dimensional array.Split(Fields!Name.Value,” “)
StrCompIt compares the string and returns -1, 0, or 1 based on the result.StrComp(Fields!Country.Value, Fields!Territory.Value)
StrConvIt performs the string conversion.StrConv(Fields!FullName.Value, vbProperCase)
StrDupIt duplicates the given string for the specified number of times.StrDup(5, “TG”)
StrReverseIt performs the string Reverse (characters in reverse order).StrReverse(Fields!FullName.Value)
TrimIt removes the leading and trailing spaces (if any) from the given field.Trim(Fields!Address.Value)
UCaseIt converts the given string or character field to Uppercase.UCase(Fields!ProductName.Value)

SSRS Date and Time Functions

The following list of SSRS Date and Time Functions helps you deal with the Date field or columns. You can use the below-mentioned functions to deal with the date field—for instance, converting to date, formatting, extracting the date or time part such as the hour, minute, day, year, etc. Apart from that, you can add or subtract days, calculate the difference between two dates, etc.

Date & Time FunctionsDescriptionExample
CDateIt converts the given field to date.CDate(Fields!HireDate.Value)
DateAddIt adds the number of days to the existing Date field to generate future date and time values.DateAdd(“d”, 30, Fields!HireDate.Value)
DateDiffIt returns the time interval difference between two date fields as the Long value.DateDiff(“yyyy”, Fields!HireDate.Value, Fields!ResignDate.Value)
DatePartIt constructs the date value using the given year, month, and day, where the time is set to midnight (00:00:00).DatePart(“m”, Fields!HireDate.Value, 0, 0)
DateSerialIt uses the given string format of date information and returns the Date value, where the time is set to midnight (00:00:00). DateSerial(2024,01,18)
DateStringIt returns the current date according to your system in a string format.DateString()
DateValueIt helps to print the Day from the Date field and returns an integer value from 1 to 31.DateValue(“January 01, 2024”)
DayIt returns the total number of seconds since midnight.Day(Fields!OrderDate.Value)
FormatDateTimeIt returns a string expression representing a Date & Time value.FormatDateTime (Fields!ShipDate.Value, DateFormat.LongDate)
HourIt helps to print the Hour from the Date field and returns the integer value from 0 to 23.Hour(Fields!OrderDate.Value)
MinuteIt helps to print the Minute value from the Date field and returns the integer value from 0 to 59.Minute(Fields!OrderDate.Value)
MonthIt helps to print the Month Number from the Date field and returns the integer value from 1 to 12.Month(Fields!OrderDate.Value)
MonthNameIt helps to print the Month Names from the Date field, January.MonthName(Fields!OrderDate.Value)
NowIt returns the current date and time according to your system.Now()
SecondIt helps to print the Second Value from the Date field and returns the integer value from 0 to 59.Second(Fields!OrderDate.Value)
TimeOfDayIt returns the current time of the day according to your system.TimeOfDay()
TimerIt returns the total number of seconds since midnight.Timer()
TimeSerialIt uses the given string format of Time information and returns the Date and Time value, where the Date value is set to 01/01/0001.TimeSerial(10,30,45)
TimeStringIt returns the current Time of the day according to your system in a string format.TimeString()
TimeValueIt returns the name of the Weekday Name as a string value. TimeValue(“10,30,45”)
TodayIt returns the current date according to your system.Today()
WeekdayIt helps to print the day of the week from the Date field and returns the integer value.Weekday(Fields!OrderDate.Value, 0)
WeekdayNameIt returns the name of the Weekday Name as a string value.WeekdayName (Fields!OrderDate.Value)
YearIt helps to print the Year from the Date field and returns the integer value from 1 to 9999.Year(Fields!OrderDate.Value)

SSRS Mathematical Functions

The following SSRS Math Functions list helps you perform Mathematical calculations on numeric fields or columns. You can use the functions mentioned below to find the trigonometric sine, cosine, tangent, hyperbolic version, and arc versions of them. Apart from that, you can also find the exponential, logarithmic, square root, power, maximum, minimum, floor, and ceiling values.

Math FunctionsDescriptionExample
AbsIt returns the absolute positive value of a floating-point number with single precision.Abs(-10.9)
AcosIt returns the Angle of the given Cosine value.Acos(0.25)
AsinIt returns the Angle of the given Sine value.Asin(0.25)
AtanIt returns the Angle of the given Tangent value.Atan(0.25)
Atan2It returns the sign of an 8-bit signed integer.Atan2(2, 5)
BigMulIt returns the product of two 32-bit integers. + or – 2147483647 is the Maximum and minimum integer range.BigMul(2147483647,-2147483647)
CeilingIt returns the smallest integer value that is greater than or equal to the given floating-point number.Ceiling(Fields!YearlyIncome.Value / 12)
CosIt returns the cosine value of the given angle.Cos(45)
CoshIt returns the Hyperbolic Cosine value of the given angle.Cosh(45)
ExpIt returns e raised to the power of the given number.Exp(2)
FixIt returns the integer portion of a number.Fix(Fields!Orders.Value * -0.5)
FloorIt returns the largest integer value that is less than or equal to the given floating-point number.Floor(Fields!YearlyIncome.Value / 12)
IntIt returns the integer portion of a number.Fix(Fields!Orders.Value * -2.5)
LogIt returns the natural logarithm value of base e for a given number.Log(2)
Log10It returns the logarithm value of base 10 for a given number.Log10(2)
MaxIt returns the Maximum value of all not null values from the given field.Max(Fields!Orders.Value)
MinIt returns the Minimum value of all not null values from the given field.Min(Fields!Orders.Value)
PowIt returns the given number raised to the power of the specified number.Pow(Fields!Income.Value,2)
RndIt returns a random number of the single datatype.Rnd()
RoundIt will round out the given double-precision floating-point value to the nearest integer.Round(Fields!Sales.Value, 2)
SignIt returns a sign of an 8-bit signed integer.Sign(Fields!ProductCost.Value – Fields!Sales.Value)
SinIt returns the Sine value of the given angle.Sin(45)
SinhIt returns the Hyperbolic Sine value of the given angle.Sinh(45)
SqrtIt returns the square root of a given number.Sqrt(25)
TanIt returns the Tangent value of the given angle.Tan(30)
TanhIt returns the Hyperbolic Tangent value of the given angle.Tanh(45)

Inspection Functions

The following list of SSRS Inspection Functions helps you check whether the field is array, date, numeric, or nothing.

Inspection FunctionsDescriptionExample
IsArrayIt checks whether the given variable is an Array or not and returns the Boolean value.IsArray(Parameters!Country.Value)
IsDateIt checks whether the given expression can be evaluated as a Date and returns the Boolean value.IsDate(Fields!OrderDate.Value)
IsNothingIt checks whether the given expression has an object assigned to it or not and returns the Boolean value.IsNothing(Fields!MiddleName.Value)
IsNumericIt checks whether the given expression can be evaluated as a Number or not and returns the Boolean value.IsNumeric(Fields!Orders.Value)

Program Flow Functions

The following list of SSRS Program Flow Functions helps you alter the flow. Like any other programming language, the IIF acts as the IF ELSE statement. The switch will deal with multiple conditions, and the choice will pick the required one from multiple choice.

Program Flow FunctionsDescriptionExample
ChooseIt checks the expression and, based on the result, returns one of the two given objects.Choose(2, “100”, “200”, “300”)
IIfIt checks the expression, and based on the result, it returns one of the two given objects.IIf(Fields!Sales.Value >= 3000, “Good”, “Bad”)
SwitchIt helps to evaluate a list of expressions; if any expression is True, it returns the corresponding Object value.

SSRS Aggregate Functions

The following list of SSRS Aggregate Functions helps you perform all kinds of aggregations on numeric fields or columns. You can use the below-mentioned functions to find the total count, average, minimum, maximum, sum, variance, standard deviation, and first and last values. They also allow you to find the running total and custom aggregations.

Aggregate FunctionsDescriptionExample
AvgIt returns the Average of all not null values from the given field.Avg(Fields!Sales.Value)
CountIt counts all the values from the given field and returns the number.Count(Fields!Name.Value)
CountDistinctIt counts all the distinct (unique) values from the given field and returns the number.CountDistinct(Fields!Name.Value)
CountRowsIt counts the total rows in a given scope.CountRows()
FirstIt returns the first value from the given expression.First(Fields!MiddleName.Value = “T”)
LastIt returns the last value from the given expression.Last(Fields!MiddleName.Value = “G”)
MaxIt uses the specified aggregate function to return the running aggregate of the specified expression, such as the running total. Max(Fields!Sales.Value)
MinIt returns the Minimum value from all the not-null values in the given field.Min(Fields!Sales.Value)
StDevIt returns the Standard Deviation of all not null values from the given field.StDev(Fields!Sales.Value)
StDevPIt returns the Population Standard Deviation of all not null values from the given field.StDevP(Fields!Sales.Value)
SumIt returns the Sum of all values from the given field.Sum(Fields!Sales.Value)
VarIt returns the Variance of all not null values from the given field.Var(Fields!Sales.Value)
VarPIt returns the Population Variance of all not null values from the given field.Var(Fields!Sales.Value)
RunningValueIt uses the specified aggregate function to return the running aggregate of the specified expression, such as the running total.RunningValue(Fields!SalesAmount.Value, Sum, “AdventureWorks”)
AggregateIt returns the Custom Aggregate from the given field, as defined by the data provider.Aggregate(Fields!Orders.Value)
PreviousIt returns the previous value or text of the given field.Previous(Fields!Orders.Value)

Financial Functions

The following list of SSRS Financial Functions helps you deal with financing data such as asset depreciation, interest rates, present and future values, etc.

Financial FunctionsDescription
DDBIt uses the periodic, fixed interest rate and fixed payments to calculate the future value of an annuity and returns Double.
FVIt uses periodic fixed payments and fixed interest rates to calculate the total number of periods for an annuity and returns Double.
IPmtIt uses periodic fixed payments and fixed interest rates to calculate the Principal payment for a given period of an annuity and returns Double.
NperIt uses periodic, fixed payments, and fixed interest rates to calculate the interest payment for a given period of an annuity and returns Double.
PmtIt uses periodic, fixed payments to be paid in the future and a fixed interest rate to calculate the present value of an annuity and returns Double.
PPmtIt returns the straight-line depreciation of an asset for a single period and returns Double.
PVIt uses periodic, fixed payments and fixed interest rates to calculate the payment for an annuity and returns Double.
RateIt returns the interest rate per period for an annuity and returns Double.
SLNIt uses periodic fixed payments and fixed interest rates to calculate the Principal payment for a given period of an annuity and returns Double.
SYDIt returns the Sum of Years Digits depreciation of an asset for a given period and returns Double.

SSRS Conversion Functions

The following list of SSRS Conversion Functions helps you convert the data type of one field to another. You can use the below-mentioned functions to convert the given field to boolean, byte, character, date, decimal, double, integer, long, object, short, single, string, etc. Apart from that, you can get the string from the hexadecimal and octal values.

Conversion FunctionsDescriptionExample
CBoolIt converts the given field to Boolean.CBool(Fields!Flag.Value)
CByteIt converts the given field to a Byte.CByte(Fields!CarsOwned.Value)
CCharIt converts the given field to Char.CChar(Fields!MariitalStatus.Value)
CDateIt converts the given field to Date.CDate(Fields!HireDate.Value)
CDblIt converts the given field to Double.CDbl(Fields!Sales.Value)
CDecIt converts the given field to Decimal.CDec(Fields!Sales.Value)
CIntIt converts the given field to an Integer.CInt(Fields!Income.Value)
ClngIt converts the given field to Long.Clng(Fields!Sales.Value)
CObjIt converts the given field to an Object.CObj(Fields!Sales.Value)
CShortIt converts the given field to Short. CShort(Fields!CarsOwned.Value)
CSngIt converts the given field to a Single.CSng(Fields!Income.Value)
CStrIt converts the given field to a String.CStr(Fields!HireDate.Value)
FixIt returns the integer portion of a number.Fix(Fields!Orders.Value / -0.5)
HexIt returns the Hexadecimal value of a number in a string format.Hex(45)
IntIt returns the integer portion of a number.Int(Fields!Income.Value / 12)
OctIt returns the Octal value of a number in a string format.Oct(10)
StrIt returns the String Representation of a number.Str(Fields!Sales.Value)
ValIt returns the numbers from a string field with the appropriate data type. Val(Fields!Address.Value)

SSRS Miscellaneous Functions

The following list of miscellaneous functions in SSRS is the most important and useful for many cases. For instance, Lookup trios help merge two datasets in a single report, finding the hierarchy level(depth) and scope of the object. Apart. From that, you can get the previous values and add row numbers.

Misc FunctionsDescriptionExample
InScopeIt returns true if the current instance is within the given scope.InScope(“GroupName”)
LevelIt returns an integer representing the current depth of a recursive hierarchy level.Level()
LookupIt combines multiple datasets using common fields and fields from the secondary dataset.Lookup(Fields!DepID.Value, Fields!DeptID.Value, Fields!DepartmentName.Value, “DepartmentDS”)
LookupSetIt retrieves a set of values from the required dataset. It helps when there is a 1-to-many relationship.LookupSet(Fields!TerGroupID.Value, Fields!TerritoryID.Value, Fields!StoreNames.value, “StoresDS”)
MultiLookupIt retrieves a set of values from the required dataset where each pair has a 1-to-1 relationship. MultiLookup is the equivalent of calling Lookup for a set of names or keys.
PreviousIt retrieves a set of values from the required dataset where each pair has a 1-to-1 relationship. MultiLookup is the equivalent of calling Lookup for a set of names or keys.Previous(Fields!Name.Value)
RowNumberIt returns a running count of all rows in the given scope.RowNumber(“DSet”)