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.
| SSRS String Functions | Description | Example |
|---|---|---|
| Asc | It returns the ASCII code (integer value) of the character. | Asc(Fields!Name.Value) |
| AscW | It returns an Integer value (ASCII Character code) of the corresponding character. | AscW(Fields!Name.Value) |
| Chr | It returns the character associated with the ASCII code (integer value). | Chr(84) |
| ChrW | It returns the character associated with the given integer character code (ASCII code). | ChrW(100) |
| Filter | It 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) |
| Format | It returns a string value formatted according to instructions given in a format String expression. | Format (Fields!OrderDate.Value, “Short Date”) |
| FormatCurrency | It returns the substring containing the given number of left-side characters. | FormatCurrency (Fields!Sales.Value, 0) |
| FormatDateTime | It returns a string expression representing a Date & Time value. | FormatDateTime (Fields!OrderDate.Value, DateFormat.GeneralDate) |
| FormatNumber | It returns an expression formatted as a number. | FormatNumber (Fields!Sales.Value, 1) |
| FormatPercent | It returns an expression formatted as a percentage with the % symbol. | FormatPercent (Fields!Orders.Value / Sum(Fields!Orders.Value, “DataSet”), 2) |
| GetChar | It returns the character from the string field at the given index position. | GetChar(Fields!ProductName.Value, 4) |
| InStr | It searches for the given substring inside a string field and returns the first occurring position (integer index position). | InStr(Fields!ProductDescription.Value, “Bike”) |
| InStrRev | It 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”) |
| Join | It joins the given number of substrings in an array separated by the second argument and returns a string. | Join(Parameters!Country.Value, “, “) |
| LCase | It converts the given string or character field to Lowercase. | LCase(Fields!ProductName.Value) |
| Left | It returns a Left-side portion of the given string adjusted to the specified length. | Left(Fields!ProductName.Value, 2) |
| Len | It returns either the total characters in a string or the number of bytes required to store a variable. | Len(Fields!Name.Value) |
| LSet | It uses the last two arguments to extract and return a portion (substring) from the given string. | LSet(Fields!ProductName.Value, 4) |
| LTrim | It trims or removes the leading spaces (Beginning) from the given field. | LTrim(Fields!Address.Value) |
| Mid | It returns the Right side portion of the given string adjusted to the specified length. | Mid(Fields!ProductName.Value, 2, 6) |
| Replace | It helps to replace the matching text with another substring. | Replace(Fields!ProductName.Value, “Bike”, “Cycle”) |
| Right | It extracts the given number of Right side characters (substring) from the string field. | Right (Fields!ProductName.Value, 5) |
| RSet | It uses the last two arguments to extract and return a portion (substring) from the given string. | RSet(Fields!ProductName.Value, 5) |
| RTrim | It trims or removes the trailing spaces (Ending) from the given field. | RTrim(Fields!Address.Value) |
| Space | It returns the given number of empty spaces. | Space(2) |
| Split | It splits the given string based on the second argument and returns the one-dimensional array. | Split(Fields!Name.Value,” “) |
| StrComp | It compares the string and returns -1, 0, or 1 based on the result. | StrComp(Fields!Country.Value, Fields!Territory.Value) |
| StrConv | It performs the string conversion. | StrConv(Fields!FullName.Value, vbProperCase) |
| StrDup | It duplicates the given string for the specified number of times. | StrDup(5, “TG”) |
| StrReverse | It performs the string Reverse (characters in reverse order). | StrReverse(Fields!FullName.Value) |
| Trim | It removes the leading and trailing spaces (if any) from the given field. | Trim(Fields!Address.Value) |
| UCase | It converts the given string or character field to Uppercase. | UCase(Fields!ProductName.Value) |
SSRS Date and Time Functions
The following SSRS Date and Time Functions list 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.
| SSRS Date & Time Functions | Description | Example |
|---|---|---|
| CDate | It converts the given field to date. | CDate(Fields!HireDate.Value) |
| DateAdd | It adds the number of days to the existing Date field to generate future date and time values. | DateAdd(“d”, 30, Fields!HireDate.Value) |
| DateDiff | It returns the time interval difference between two date fields as the Long value. | DateDiff(“yyyy”, Fields!HireDate.Value, Fields!ResignDate.Value) |
| DatePart | It 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) |
| DateSerial | It 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) |
| DateString | It returns the current date according to your system in a string format. | DateString() |
| DateValue | It helps to print the Day from the Date field and returns an integer value from 1 to 31. | DateValue(“January 01, 2024”) |
| Day | It returns the total number of seconds since midnight. | Day(Fields!OrderDate.Value) |
| FormatDateTime | It returns a string expression representing a Date & Time value. | FormatDateTime (Fields!ShipDate.Value, DateFormat.LongDate) |
| Hour | It helps to print the Hour from the Date field and returns the integer value from 0 to 23. | Hour(Fields!OrderDate.Value) |
| Minute | It helps to print the Minute value from the Date field and returns the integer value from 0 to 59. | Minute(Fields!OrderDate.Value) |
| Month | It helps to print the Month Number from the Date field and returns the integer value from 1 to 12. | Month(Fields!OrderDate.Value) |
| MonthName | It helps to print the Month Names from the Date field, January. | MonthName(Fields!OrderDate.Value) |
| Now | It returns the current date and time according to your system. | Now() |
| Second | It helps to print the Second Value from the Date field and returns the integer value from 0 to 59. | Second(Fields!OrderDate.Value) |
| TimeOfDay | It returns the current time of the day according to your system. | TimeOfDay() |
| Timer | It returns the total number of seconds since midnight. | Timer() |
| TimeSerial | It 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) |
| TimeString | It returns the current Time of the day according to your system in a string format. | TimeString() |
| TimeValue | It returns the name of the Weekday Name as a string value. | TimeValue(“10,30,45”) |
| Today | It returns the current date according to your system. | Today() |
| Weekday | It helps to print the day of the week from the Date field and returns the integer value. | Weekday(Fields!OrderDate.Value, 0) |
| WeekdayName | It returns the name of the Weekday Name as a string value. | WeekdayName (Fields!OrderDate.Value) |
| Year | It 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, and arc versions. Apart from that, you can also find the exponential, logarithmic, square root, power, maximum, minimum, floor, and ceiling values.
| SSRS Math Functions | Description | Example |
|---|---|---|
| Abs | It returns the absolute positive value of a floating-point number with single precision. | Abs(-10.9) |
| Acos | It returns the Angle of the given Cosine value. | Acos(0.25) |
| Asin | It returns the Angle of the given Sine value. | Asin(0.25) |
| Atan | It returns the Angle of the given Tangent value. | Atan(0.25) |
| Atan2 | It returns the sign of an 8-bit signed integer. | Atan2(2, 5) |
| BigMul | It returns the product of two 32-bit integers. + or – 2147483647 is the Maximum and minimum integer range. | BigMul(2147483647,-2147483647) |
| Ceiling | It returns the smallest integer value that is greater than or equal to the given floating-point number. | Ceiling(Fields!YearlyIncome.Value / 12) |
| Cos | It returns the cosine value of the given angle. | Cos(45) |
| Cosh | It returns the Hyperbolic Cosine value of the given angle. | Cosh(45) |
| Exp | It returns e raised to the power of the given number. | Exp(2) |
| Fix | It returns the integer portion of a number. | Fix(Fields!Orders.Value * -0.5) |
| Floor | It returns the largest integer value that is less than or equal to the given floating-point number. | Floor(Fields!YearlyIncome.Value / 12) |
| Int | It returns the integer portion of a number. | Fix(Fields!Orders.Value * -2.5) |
| Log | It returns the natural logarithm value of base e for a given number. | Log(2) |
| Log10 | It returns the logarithm value of base 10 for a given number. | Log10(2) |
| Max | It returns the Maximum value of all not null values from the given field. | Max(Fields!Orders.Value) |
| Min | It returns the Minimum value of all not null values from the given field. | Min(Fields!Orders.Value) |
| Pow | It returns the given number raised to the power of the specified number. | Pow(Fields!Income.Value,2) |
| Rnd | It returns a random number of the single datatype. | Rnd() |
| Round | It will round out the given double-precision floating-point value to the nearest integer. | Round(Fields!Sales.Value, 2) |
| Sign | It returns a sign of an 8-bit signed integer. | Sign(Fields!ProductCost.Value – Fields!Sales.Value) |
| Sin | It returns the Sine value of the given angle. | Sin(45) |
| Sinh | It returns the Hyperbolic Sine value of the given angle. | Sinh(45) |
| Sqrt | It returns the square root of a given number. | Sqrt(25) |
| Tan | It returns the Tangent value of the given angle. | Tan(30) |
| Tanh | It 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.
| SSRS Inspection Functions | Description | Example |
|---|---|---|
| IsArray | It checks whether the given variable is an Array or not and returns the Boolean value. | IsArray(Parameters!Country.Value) |
| IsDate | It checks whether the given expression can be evaluated as a Date and returns the Boolean value. | IsDate(Fields!OrderDate.Value) |
| IsNothing | It checks whether the given expression has an object assigned to it or not and returns the Boolean value. | IsNothing(Fields!MiddleName.Value) |
| IsNumeric | It 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.
| SSRS Program Flow Functions | Description | Example |
|---|---|---|
| Choose | It checks the expression and, based on the result, returns one of the two given objects. | Choose(2, “100”, “200”, “300”) |
| IIf | It checks the expression, and based on the result, it returns one of the two given objects. | IIf(Fields!Sales.Value >= 3000, “Good”, “Bad”) |
| Switch | It 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.
| SSRS Aggregate Functions | Description | Example |
|---|---|---|
| Avg | It returns the Average of all not null values from the given field. | Avg(Fields!Sales.Value) |
| Count | It counts all the values from the given field and returns the number. | Count(Fields!Name.Value) |
| CountDistinct | It counts all the distinct (unique) values from the given field and returns the number. | CountDistinct(Fields!Name.Value) |
| CountRows | It counts the total rows in a given scope. | CountRows() |
| First | It returns the first value from the given expression. | First(Fields!MiddleName.Value = “T”) |
| Last | It returns the last value from the given expression. | Last(Fields!MiddleName.Value = “G”) |
| Max | It uses the specified aggregate function to return the running aggregate of the specified expression, such as the running total. | Max(Fields!Sales.Value) |
| Min | It returns the Minimum value from all the not-null values in the given field. | Min(Fields!Sales.Value) |
| StDev | It returns the Standard Deviation of all not null values from the given field. | StDev(Fields!Sales.Value) |
| StDevP | It returns the Population Standard Deviation of all not null values from the given field. | StDevP(Fields!Sales.Value) |
| Sum | It returns the Sum of all values from the given field. | Sum(Fields!Sales.Value) |
| Var | It returns the Variance of all not null values from the given field. | Var(Fields!Sales.Value) |
| VarP | It returns the Population Variance of all not null values from the given field. | Var(Fields!Sales.Value) |
| RunningValue | It 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”) |
| Aggregate | It returns the Custom Aggregate from the given field, as defined by the data provider. | Aggregate(Fields!Orders.Value) |
| Previous | It returns the previous value or text of the given field. | Previous(Fields!Orders.Value) |
The RunningValue function is useful for calculating the running totals, averages, etc.
- Running Total
- Running Average
- Distinct Running Count
- Running Count
- Running Standard Deviation
- Percentage of Total
- Running Percentage
- Running Variance
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.
| SSRS Financial Functions | Description |
|---|---|
| DDB | It uses the periodic, fixed interest rate and fixed payments to calculate the future value of an annuity and returns Double. |
| FV | It uses periodic fixed payments and fixed interest rates to calculate the total number of periods for an annuity and returns Double. |
| IPmt | It uses periodic fixed payments and fixed interest rates to calculate the Principal payment for a given period of an annuity and returns Double. |
| Nper | It uses periodic, fixed payments, and fixed interest rates to calculate the interest payment for a given period of an annuity and returns Double. |
| Pmt | It 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. |
| PPmt | It returns the straight-line depreciation of an asset for a single period and returns Double. |
| PV | It uses periodic, fixed payments and fixed interest rates to calculate the payment for an annuity and returns Double. |
| Rate | It returns the interest rate per period for an annuity and returns Double. |
| SLN | It uses periodic fixed payments and fixed interest rates to calculate the Principal payment for a given period of an annuity and returns Double. |
| SYD | It 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 Functions | Description | Example |
|---|---|---|
| CBool | It converts the given field to Boolean. | CBool(Fields!Flag.Value) |
| CByte | It converts the given field to a Byte. | CByte(Fields!CarsOwned.Value) |
| CChar | It converts the given field to Char. | CChar(Fields!MariitalStatus.Value) |
| CDate | It converts the given field to Date. | CDate(Fields!HireDate.Value) |
| CDbl | It converts the given field to Double. | CDbl(Fields!Sales.Value) |
| CDec | It converts the given field to Decimal. | CDec(Fields!Sales.Value) |
| CInt | It converts the given field to an Integer. | CInt(Fields!Income.Value) |
| Clng | It converts the given field to Long. | Clng(Fields!Sales.Value) |
| CObj | It converts the given field to an Object. | CObj(Fields!Sales.Value) |
| CShort | It converts the given field to Short. | CShort(Fields!CarsOwned.Value) |
| CSng | It converts the given field to a Single. | CSng(Fields!Income.Value) |
| CStr | It converts the given field to a String. | CStr(Fields!HireDate.Value) |
| Fix | It returns the integer portion of a number. | Fix(Fields!Orders.Value / -0.5) |
| Hex | It returns the Hexadecimal value of a number in a string format. | Hex(45) |
| Int | It returns the integer portion of a number. | Int(Fields!Income.Value / 12) |
| Oct | It returns the Octal value of a number in a string format. | Oct(10) |
| Str | It returns the String Representation of a number. | Str(Fields!Sales.Value) |
| Val | It 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 Functions | Description | Example |
|---|---|---|
| InScope | It returns true if the current instance is within the given scope. | InScope(“GroupName”) |
| Level | It returns an integer representing the current depth of a recursive hierarchy level. | Level() |
| Lookup | It combines multiple datasets using common fields and fields from the secondary dataset. | Lookup(Fields!DepID.Value, Fields!DeptID.Value, Fields!DepartmentName.Value, “DepartmentDS”) |
| LookupSet | It 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”) |
| MultiLookup | It 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 | It 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) |
| RowNumber | It returns a running count of all rows in the given scope. | RowNumber(“DSet”) |
