Built-In Function Expressions in Bold Reports
Using Bold Reports Query Expression gives you access to a wide range of built-in functions that can help you manipulate and analyze your data effectively. These functions are organized into different categories, including Global, User, Text, Date & Time, Math function expressions, and more. In this article, we’ll explore these functions and provide you with their syntax and descriptions.
Math Expressions
These functions are primarily focused on mathematical operations, including trigonometric functions, logarithms, and basic arithmetic operations.
|
Functions |
Descriptions |
Syntax Example |
|
|
Returns the absolute value of a single-precision floating-point number. |
=Abs(Fields!YearlyIncome.Value - 80000) |
|
|
Returns the angle whose cosine is the specified number. |
=Acos(Fields!Angle.Value) |
|
|
Returns the angle whose sine is the specified number. |
=Asin(Fields!Angle.Value) |
|
|
Returns the angle whose tangent is the specified number. |
=Atan(Fields!Tangent.Value) |
|
|
Returns the angle whose tangent is the quotient of two specified number. |
=Atan2(Fields!CoordinateY.Value, Fields!CoordinateX.Value) |
|
|
Produces the full product of two 32-bit numbers. |
=BigMul(Fields!Int32Value.Value, Fields!Int32Value.Value) |
|
|
Returns the smallest integer that is greater than or equal to the specified integer. |
=Ceiling(Fields!YearlyIncome.Value / 7) |
|
|
Returns the cosine of the specified angle. |
=Cos(Fields!Angle.Value) |
|
|
Returns the hyperbolic cosine of the specified angle. |
=Cosh(Fields!Angle.Value) |
|
|
Returns e raised to the specified power. |
=Exp(Fields!IntegerCounter.Value) |
|
|
Returns an integer portion of a number. |
=Fix(Fields!YearlyIncome.Value / -3) |
|
|
Returns the largest integer less than or equal to the specified integer. |
=Floor(Fields!YearlyIncome.Value / 12) |
|
|
Returns an integer portion of a number. |
=Int(Fields!YearlyIncome.Value / 12) |
|
|
Returns the natural (base e) logarithm of a specified number. |
=Log(Fields!NumberValue.Value) |
|
|
Returns the base 10 logarithm of a specified number. |
=Log10(Fields!NumberValue.Value) |
|
|
Returns the maximum value from all non-null values of the specified expression. |
=Max(Fields!YearlyIncome.Value, “AdventureWorks”, Recursive) |
|
|
Returns the minimum value from all non-null values of the specified expression. |
=Min(Fields!YearlyIncome.Value, “AdventureWorks”, Recursive) |
|
|
Returns a specified number raised to the specified power. |
=Pow(Fields!YearlyIncome.Value, 2) |
|
|
Returns a random number of single type. |
=Rnd(-1) |
|
|
Rounds a double-precision floating-point value to the nearest integer. |
=Round(Fields!YearlyIncome.value / 12, 2) |
|
|
Returns a value indicating the sign of an 8-bit signed integer. |
=Sign(Fields!YearlyIncome.Value - 60000) |
|
|
Returns the sine of the specified angle. |
=Sin(Fields!Angle.Value) |
|
|
Returns the hyperbolic sine of the specified angle. |
=Sinh(Fields!Angle.Value) |
|
|
Returns the square root of a specified number. |
=Sqrt(Fields!Area.Value) |
|
|
Returns the tangent of the specified angle. |
Tan(Fields!Angle.Value) |
Global Expressions
Global expressions provide access to various global parameters related to the report, such as execution time and report server URL.
|
Functions |
Descriptions |
Syntax Example |
|
|
The date and time that the report starts to run. |
=Globals!ExecutionTime |
|
|
The current overall page number can be used only in the page header or footer. |
=Globals!OverallPageNumber |
|
|
The total number of pages in the report can be used only in the page header and footer. |
=Globals!OverallTotalPages |
|
|
The name of the current page in the report can be used only in the page header and footer. |
=Globals!PageName |
|
|
The current page number that can be reset through the use of page breaks. |
=Globals!PageNumber |
|
|
A boolean that indicates whether the current rendering request uses an interactive format. |
=Globals!RenderFormat.IsInteractive |
|
|
The name of the renderer as registered in the ReportServer configuration file. |
=Globals!RenderFormat.Name |
|
|
The full path to the folder containing the report does not include the report server URL. |
=Globals!ReportFolder |
|
|
The URL of the report server where the report is run. |
=Globals!ReportName |
|
|
The URL of the report server on which the report is being run. |
=Globals!ReportServerUrl |
|
|
The total number of pages in the current continuous page sequence can be used only in the page header and footer. The number can be reset by using page breaks. |
=Globals!TotalPages |
User Expressions
User expressions offer information about the client running the report, such as language and user ID.
|
Functions |
Descriptions |
Syntax Example |
|
|
The Language ID of the client running the report. |
User!Language |
|
|
The ID of the user running the report. |
User!UserID |
Text Expressions
Text expressions are geared towards manipulating and formatting text data, including functions to extract characters, format strings, and search within strings.
|
Functions |
Description |
Syntax Example |
|
|
Returns an integer value representing the character code corresponding to a character. |
=Asc(Fields!Description.Value) |
|
|
Returns an integer value representing the character code corresponding to a character. |
=AscW(Fields!Description.Value) |
|
|
Returns the character associated with the specified character code. |
=Chr(65) |
|
|
Returns the character associated with the specified character code. |
=ChrW(241) |
|
|
Returns a zero-based array containing a subset of a string array based on specified filter criteria. |
=Filter(Parameters!MultivalueParameter.Value, “3”, True, CompareMethod.Binary) |
|
|
Returns a formatted string according to the instructions in a format string expression. |
=Format(Globals!ExecutionTime, “Long Date”) |
|
|
Returns an expression formatted as a currency value using the currency symbol defined in the system control panel. |
=FormatCurrency(Fields!YearlyIncome.Value, 0) |
|
|
Returns a string expression representing a date. |
=FormatDateTime(Fields!BirthDate.Value, Date) |
|
|
Returns a string expression representing a date/time value. |
=FormatNumber(Fields!Weight.Value, 2) |
|
|
Returns an expression formatted as a percentage (that is multiplied by 100). |
=FormatPercent(Fields!Sales.Values/Sum(Fields!Sales.Value, “DataSet1”), 0) |
|
|
Returns a char value representing the character from the specified index in the supplied string. |
=GetChar(Fields!Description.Value, 5) |
|
|
Returns an integer specifying the start position of the first occurrence of one string within another. |
=InStr(Fields!Description.Value, “car”) |
|
|
Returns the position of the first occurrence of one string within another, starting from the right side of the string. |
=InStrRev(Fileds!Description.Value, “car”) |
|
|
Returns a string created by joining a number of substrings in an array. |
=Join(Parameters!MultivalueParameter.Value, “,”) |
|
|
Returns a string or character converted to lowercase. |
=LCase(Fields!Description.Value) |
|
|
Returns a string containing a specified number of characters from the left side of the string. |
=Left(Fields!Description.Value, 4) |
|
|
Returns an integer containing either the number of characters in a string or the number. |
=Len(Fields!Description.Value) |
|
|
Returns a left-aligned string containing the specified string adjusted to the specified length. |
=LSet(Fields!Description.Value, 4) |
|
|
Returns the string without left side trailing spaces in the given string. |
=LTrim(Fields!Description.Value) |
|
|
Returns a string containing a specified number of characters from a string. |
=Mid(Fields!Description.Value,3,4) |
|
|
Returns a string in which a specified substring has been replaced with another. |
=Replace(Fields!Description.Value, “tube”, “headlight”) |
|
|
Returns a string containing a specified number of characters from the right side of a string. |
=Right(Fields!Description.Value,4) |
|
|
Returns a right-aligned string containing the specified string adjusted to the specified length. |
=RSet(Feilds!Description.Value,4) |
|
|
Returns the string without right side trailing spaces in the given string. |
=RTrim(Fields!Description.Value) |
|
|
Returns a string consisting of the specified number of spaces. |
=Space(3) |
|
|
Returns a zero-based, one-dimensional array containing a specified number of substrings. |
=Split(Fields!ListWithCommas.Value,“,”) |
|
|
Returns -1, 0, or 1, based on the result of a string comparison. |
=StrComp(Fields!Description.Value,First(Fields!Description.Value)) |
|
|
Returns a string converted as specified. |
=StrConv(Fields!Description.Value, vbProperCase) |
|
|
Returns a string or object consisting of the specified character repeated the specified number of times. |
=StrDup(3, “M”) |
|
|
Returns a string in which the character order of a specified string is reversed. |
=StrReverse(Fields!Description.Value) |
|
|
Returns the string without trailing spaces in the given string. |
=Trim(Fields!Description.Value) |
|
|
Returns a string or character containing the specified string converted to uppercase. |
=UCase(Fields!Description.Value) |
Inspection Expressions
Inspection expressions serve various purposes related to inspecting data and conditions within the report.
|
Functions |
Description |
Syntax Example |
|
|
Returns a Boolean value indicating whether a variable points to an array. |
=IsArray(Parameters!Initials.Value) |
|
|
Returns a Boolean value indicating whether an expression represents a valid date. |
=IsDate(Fields!BirthDate.Value) |
|
|
Returns a Boolean value indicating whether an expression has no object. |
=IsNothing(Fields!MiddleInitial.Value) |
|
|
Returns a Boolean value indicating whether an expression can be evaluated as a number. |
=IsNumeric(Fields!YearlyIncome.Value) |
Program Flow Expressions
Program Flow expressions help control the flow of report logic based on conditions, such as using the IIf function for conditional branching
|
Functions |
Description |
Syntax Example |
|
|
Selects and returns a value from a list of arguments. |
=Choose(Datepart(“w”, Fields!BirthDate.Value), “First”, “Second”, “Third”, “Fourth”, “Fifth”, “Sixth”, “Seventh”) |
|
|
Returns one of two objects depending upon the evaluation of an expression. |
=IIf(Fields!YearlyIncome.Value >= 60000,“High”,“Low”) |
|
|
Evaluates a list of expressions and returns an object value corresponding to the first expression in the list that is true. |
=Switch(Fields!FirstName.Value = “Sue”, “Susan”,Fields!FirstName.Value = “Bob”, “Robert”) |
Aggregate Expression
Aggregate expressions are used for performing aggregate operations on data, such as calculating averages, sums, counts, and more.
|
Functions |
Description |
Syntax Example |
|
|
Returns the average of all non-null values from the specified expression. |
=Avg(Fields!YearlyIncome.Value, “GroupByGender”, Recursive) |
|
|
Returns a count of the values from the specified expression. |
=Count(Fields!FirstName.Value, “GroupByInitial”, Recursive) |
|
|
Returns a count of all distinct values from the specified expression. |
=CountDistinct(Fields!MiddleInitial.Value, “GroupInitial”, Recursive) |
|
|
Returns a count of rows within the specified scope. |
=CountRows(“GroupByInitial”,Recursive) |
|
|
Returns the first value from the specified expression |
=First(Fields!MiddleInitial.Value, “AdventureWorks”) |
|
|
Returns the last value from the specified expression. |
=Last(Fields!MiddleInitial.Value, “AdventureWorks”) |
|
|
Returns the maximum value from all non-null values of the specified expression. |
=Max(Fields!YearlyIncome.Value, “AdventureWorks”, Recursive) |
|
|
Returns the minimum value from all non-null values of the specified expression. |
=Min(Fields!YearlyIncome.Value, “AdventureWorks”, Recursive) |
|
|
Returns the standard deviation of all non-null values of the specified expression. |
=StDev(Fields!YearlyIncome.Value,“GroupByInitial”, Recursive) |
|
|
Returns the population standard deviation of all non-null values of the specified expression. |
=StDevP(Fields!YealryIncome.Value, “GroupByInitial”,Recursive) |
|
|
Returns a sum of the values of the specified expression. |
=Sum(Fields!YearlyIncome.Value,“GroupByInitial”,Recursive) |
|
|
Returns the variance of all non-null values of the specified expression. |
=Var(Fields!YearlyIncome.Value,“GroupByInitial”,Recursive) |
|
|
Returns the population variance of all non-null values of the specified expression. |
=VarP(Fields!YearlyIncome.Value,“GroupByInitial”) |
|
|
Uses a specified function to return a running aggregate of the specified expression. |
=RunningValue(Fields!YearlyIncome.Value,Sum,“AdventureWorks”) |
|
|
Returns a custom aggregate of the specified expression, as defined by the data provider. |
=Aggregate(Fields!Order_Count.Value) |
Financial Expression
Financial expressions are specialized functions for financial calculations, including depreciation, future value, and interest rate calculations.
|
Functions |
Description |
Syntax Example |
|
|
Returns a double value specifying the depreciation of an asset for a specific time period using the double-declining balance method or some other method you specify. |
=DDB(Fields!CostOfProperty.Value,Fields!Salvage.Value,Parameters!Life.Value,Parameters!Period.Value,2) |
|
|
Returns a double value specifying the future value of an annuity based on periodic fixed payments and a fixed interest rate. |
=FV(Parameters!Rate.Value,Parameters!NumberOfPayments.Value,Parameters!PaymentAmount.Value,Fields!PropertyCost.Value,DueDate.EndOfPeriod) |
|
|
Returns a double value specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate. |
=IPmt(Parameters!Rate.Value, Parameters!PaymentPeriod.Value, Parameters!NumberOfPayments.Value, Parameters!PresentValue.Value, 0,DueDate.EndOfPeriod) |
|
|
Returns a double value specifying the number of periods for an annuity based on periodic fixed payments and a fixed interest rate. |
=NPer(Parameters!Rate.Value,Parameters!PaymentAmount.Value,Parameters!PresentValue.Value,0,DueDate.EndOfPeriod) |
|
|
Returns a double value specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate. |
=Pmt(Parameters!Rate.Value,Parameters!NumberOfPayments.Value,Fields!PropertyCost.Value,0, DueDate.EndOfPeriod) |
|
|
Returns a double value specifying the principal payment for a given period of an annuity based on periodic fixed payments and a fixed interest rate. |
=PPmt(Parameters!Rate.Value,Parameters!Period.Value,Parameters!NumberOfPayments.Value,Fields!PropertyCost.Value,0,DueDate.EndOfPeriod) |
|
|
Returns a double value specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate. |
=PV(Parameters!Rate.Value,Parameters!NumberOfPayments.Value,Fields!PaymentAmount.Value,0,DueDate.EndOfPeriod) |
|
|
Returns a double value specifying the interest rate per period for an annuity. |
=Rate(Parameters!NumberOfPayments.Value,Parameters!PaymentAmount.Value,Parameters!PresentValue.Value,DueDate.EndOfPeriod,0.1) |
|
|
Returns a double value specifying the straight-line depreciation of an asset for a single period. |
=SLN(Fields!PropertyCost.Value,Parameters!Salvage.Value,Parameters!Life.Value) |
|
|
Returns a double value specifying the sum-of-years digits depreciation of an asset for a specified period. |
=SYD(Fields!PropertyCost.Value,Parameters!Salvage.Value,Parameters!Life.Value,Parameters!Period.Value) |
Miscellaneous Expressions
Miscellaneous expressions include functions for specific tasks like looking up values in datasets, determining scope, and retrieving values from previous rows.
|
Functions |
Description |
Syntax Example |
|
|
Returns true if the current instance is within the specified scope. |
=InScope(“table1_Group1”) |
|
|
Returns a zero-based integer representing the current depth level.of a recursive hierarchy. |
=Level(“GroupByInitial”) |
|
|
Use Lookup to retrieve the value from the specified dataset for a name-value pair where there is a 1-to-1 relationship. For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region. |
=Lookup(Fields!SaleProdId.Value, Fields!ProductID.Value, Fields!Name.Value, “Product”) |
|
|
Use LookupSet to retrieve a set of values from the specified dataset for a name-value pair where there is a 1-to-many relationship. For example, for a customer identifier in a table, you can use LookupSet to retrieve all the associated phone numbers for that customer from a dataset that is not bound to the data region. |
=LookupSet(Fields!TerritoryGroupID.Value, Fields!TerritoryID.Value, Fields!StoreName.value, “Stores”) |
|
|
Returns the value of the expression for the previous row of data. |
=Previous(Fields!FirstName.Value) |
|
|
Returns a running count of all rows in the specified scope. |
=RowNumber(“AdventureWorks”) |
Conversion Expressions
Conversion expressions are designed for data type conversions, allowing users to convert data from one type to another.
|
Functions |
Description |
Syntax Example |
|
|
Convert to Boolean. |
=CBool(Fields!HouseOwnerFlag.Value) |
|
|
Convert to byte. |
=CByte(Fields!Number.Value) |
|
|
Convert to char. |
=CChar(Fields!MaritalStatus.Value) |
|
|
Convert to date. |
=CDate(Fields!BirthDate.Value) |
|
|
Convert to double. |
=CDbl(Fields!YearlyIncome.Value) |
|
|
Convert to decimal. |
=CDec(Fields!YearlyIncome.Value) |
|
|
Convert to integer. |
=CInt(Fields!YearlyIncome.Value) |
|
|
Convert to long. |
=CLng(Fields!YearlyIncome.Value) |
|
|
Convert to object. |
=CObj(Fields!YearlyIncome.Value) |
|
|
Convert to short. |
=CShort(Fields!NumberCarsOwned.Value) |
|
|
Convert to single. |
=CSng(Fields!YearlyIncome.Value) |
|
|
Convert to string. |
=CStr(Fields!YearlyIncome.Value) |
|
|
Returns an integer portion of a number. |
=Fix(Fields!YearlyIncome.Value / -3) |
|
|
Returns a string representing the hexadecimal value of a number. |
=Hex(Fields!CellColor.Value) |
|
|
Returns an integer portion of a number. |
=Int(Fields!YearlyIncome.Value / 12) |
|
|
Returns a string representing the octal value of a number. |
=Oct(Fields!BitString.Value) |
|
|
Returns a string that represents a number. |
=Str(Fields!YearlyIncome.Value) |
|
|
Returns numbers in a string as a numeric value of appropriate type. |
=Val(Fields!AddressLine1.Value) |
Date & Time Expressions
Date & Time expressions provide functions for working with date and time data, including date arithmetic, formatting, and extraction.
|
Functions |
Description |
Syntax Example |
|
|
Convert to date. |
=CDate(Fields!BirthDate.Value) |
|
|
Returns a date value containing date and time values to which a specified time interval has been added. |
=DateAdd(“d”,3,Fields!BirthDate.Value) |
|
|
Returns a long value specifying the number of time intervals between two date values. |
=DateDiff(“yyyy”,Fields!BirthDate.Value,“1/1/2010”) |
|
|
Returns an integer value containing the specified component of a given date value. |
=DatePart(“q”,Fields!BirthDate.Value,0,0) |
|
|
Returns a date value representing a specified year, month, and day, with the time information set to midnight (00:00:00). |
=DateSerial(DatePart(“yyyy”,Fields!BirthDate.Value)-10, DatePart(“m”,Fields!BirthDate.Value)+3,DatePart(“d”,Fields!BirthDate.Value)-1) |
|
|
Returns or sets a string value representing the current date according to your system. |
=DatePart(“m”, DateString()) |
|
|
Returns a date value containing the date information represented by a string, with the time information. |
=DateValue(“January 15, 2010”) |
|
|
Returns an integer value from 1 through 31 representing the day of the month. |
=Day(Fields!BirthDate.Value) |
|
|
Returns a string expression representing date/time value. |
=FormatDateTime(Fields!BirthDate.Value,DateFormat.ShortDate) |
|
|
Returns an integer value from 0 through 23 representing the hour of the day. |
=Hour(Fields!BirthDate.Value) |
|
|
Returns an integer value from 0 through 59 representing the minute of the hour. |
=Minute(Fields!BirthDate.Value) |
|
|
Returns an integer value from 1 through 12 representing the month of the year. |
=Month(Fields!BirthDate.Value) |
|
|
Returns a string value containing the name of the specified month. |
=MonthName(10,True) |
|
|
Returns a date value containing the current date and time according to your system. |
="This time tomorrow is " & DateAdd(“d”, 1, Now()) |
|
|
Returns an integer value from 0 through 59 representing the second of the minute. |
=Second(Fields!BirthDate.Value) |
|
|
Returns or sets a date value containing the current time of day according to your system. |
="Time of the day is " & TimeOfDay() |
|
|
Returns a double value representing the number of seconds elapsed since midnight. |
="Number of seconds since midnight " & Timer() |
|
|
Returns a date value representing a specified hour, minute, and second, with the date information set relative to January 1 of the year 1. |
=TimeSerial(DatePart(“h”,Fields!BirthDate.Value),DatePart(“n”,Fields!BirthDate.Value),DatePart(“s”,Fields!BirthDate.Value)) |
|
|
Returns or sets a string value representing the current time of day according to your system. |
=TimeString() |
|
|
Returns a date value containing the time information represented by a string, with the date information set to January 1 of the year 1. |
= TimeValue(Fields!BirthDate.Value) |
|
|
Returns or sets a date value containing the current date according to your system. |
="Tomorrow is " & DateAdd(“d”, 1, Today()) |
|
|
Returns an integer value containing a number that represents the day of the week. |
=Weekday(Fields!BirthDate.Value,0) |
|
|
Returns a string value containing the name of the specified weekday. |
=WeekdayName(2,True,0) |
|
|
Returns an integer value from 1 through 9999 representing the year. |
=Year(Fields!BirthDate.Value) |
