Built-in functions in Web Report Designer expressions

Bold Reports^® Web Report Designer provides built-in functions that you use in RDL expressions. The functions are grouped into text, date time, math, inspection, program flow, aggregate, financial, conversion, and miscellaneous functions. Each table lists the function, what it returns, and an example expression.

Text Functions

Text functions in Web Report Designer expressions work with strings and characters, such as trimming, searching, joining, and formatting text.

References	Description	Example
`Asc`	Returns an integer value representing the character code corresponding to a character.	`=Asc(Fields!Description.Value)`
`AscW`	Returns an integer value representing the character code corresponding to a character.	`=AscW(Fields!Description.Value)`
`Chr`	Returns the character associated with the specified character code.	`=Chr(65)`
`ChrW`	Returns the character associated with the specified character code.	`=ChrW(241)`
`Filter`	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)`
Format`	Returns a formatted string according to the instructions in a format string expression.	`=Format(Globals!ExecutionTime, "Long Date")`
`FormatCurrency`	Returns an expression formatted as a currency value using the currency symbol defined in the system control panel.	`=FormatCurrency(Fields!YearlyIncome.Value,0)`
`FormatDateTime`	Returns a string expression representing a date/time value.	`=FormatDateTime(Fields!BirthDate.Value,DateFormat.ShortDate)`
`FormatNumber`	Returns an expression formatted as a number.	`=FormatNumber(Fields!Weight.Value,2)`
`FormatPercent`	Returns an expression formatted as a percentage (that is, multiplied by 100).	`=FormatPercent(Fields!Sales.Value/Sum(Fields!Sales.Value, "DataSet1"),0)`
`GetChar`	Returns a char value representing the character from the specified index in the supplied string.	`=GetChar(Fields!Description.Value, 5)`
`InStr`	Returns an integer specifying the start position of the first occurrence of one string within another.	`=InStr(Fields!Description.Value, "car")`
`InStrRev`	Returns the position of the first occurrence of one string within another, starting from the right side of the string.	`=InStrRev(Fields!Description.Value, "car")`
`Join`	Returns a string created by joining a number of substrings in an array.	`=Join(Parameters!MultivalueParameter.Value,",")`
`LCase`	Returns a string or character converted to lowercase.	`=LCase(Fields!Description.Value)`
`Left`	Returns a string containing a specified number of characters from the left side of a string.	`=Left(Fields!Description.Value,4)`
Len`	Returns an integer containing either the number of characters in a string or the number.	`=Len(Fields!Description.Value)`
`LSet`	Returns a left-aligned string containing the specified string adjusted to the specified length.	`=LSet(Fields!Description.Value,4)`
`LTrim`	Returns the string without left side trailing spaces in the given string.	`=LTrim(Fields!Description.Value)`
`Mid`	Returns a string containing a specified number of characters from a string.	`=Mid(Fields!Description.Value,3,4)`
`Replace`	Returns a string in which a specified substring has been replaced with another.	`=Replace(Fields!Description.Value,"tube","headlight")`
`Right`	Returns a string containing a specified number of characters from the right side of a string.	`=Right(Fields!Description.Value,4)`
`RSet`	Returns a right-aligned string containing the specified string adjusted to the specified length.	`=RSet(Fields!Description.Value,4)`
`RTrim`	Returns the string without right side trailing spaces in the given string.	`=RTrim(Fields!Description.Value)`
`Space`	Returns a string consisting of the specified number of spaces.	`=Space(3)`
`Split`	Returns a zero-based, one-dimensional array containing a specified number of substrings.	`=Split(Fields!ListWithCommas.Value,",")`
`StrComp`	Returns -1, 0, or 1, based on the result of a string comparison.	`=StrComp(Fields!Description.Value,First(Fields!Description.Value))`
`StrConv`	Returns a string converted as specified.	`=StrConv(Fields!Description.Value,vbProperCase)`
`StrDup`	Returns a string or object consisting of the specified character repeated the specified number of times.	`=StrDup(3,"M")`
`StrReverse`	Returns a string in which the character order of a specified string is reversed.	`=StrReverse(Fields!Description.Value)`
`Trim`	Returns the string without trailing spaces in the given string.	`=Trim(Fields!Description.Value)`
`UCase`	Returns a string or character containing the specified string converted to uppercase.	`=UCase(Fields!Description.Value)`

Date Time Functions

Date time functions in Web Report Designer expressions return or calculate date and time values and their parts.

References	Description	Example
`CDate`	Convert to date.	`=CDate(Fields!BirthDate.Value)`
`DateAdd`	Returns a long value specifying the number of time intervals between two date values.	`=DateAdd("d",3,Fields!BirthDate.Value)`
`DatePart`	Returns an integer value containing the specified component of a given date value.	`=DatePart("q",Fields!BirthDate.Value,0,0)`
`DateSerial`	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)`
`DateString`	Returns or sets a string value representing the current date according to your system.	`=DatePart("m",DateString())`
`DateValue`	Returns a date value containing the date information represented by a string, with the time information.	`=DateValue("January 15, 2010")`
`Day`	Returns an integer value from 1 through 31 representing the day of the month.	`=Day(Fields!BirthDate.Value)`
`FormatDateTime`	Returns a string expression representing date/time value.	`=FormatDateTime(Fields!BirthDate.Value,DateFormat.ShortDate)`
`Hour`	Returns an integer value from 0 through 23 representing the hour of the day.	`=Hour(Fields!BirthDate.Value)`
`Minute`	Returns an integer value from 0 through 59 representing the minute of the hour.	`=Minute(Fields!BirthDate.Value)`
`Month`	Returns an integer value from 1 through 12 representing the month of the year.	`=Month(Fields!BirthDate.Value)`
`MonthName`	Returns a string value containing the name of the specified month.	`=MonthName(10,True)`
`Now`	Returns a date value containing the current date and time according to your system.	`="This time tomorrow is " & DateAdd("d", 1, Now())`
`Second`	Returns an integer value from 0 through 59 representing the second of the minute.	`=Second(Fields!BirthDate.Value)`
`TimeOfDay`	Returns or sets a date value containing the current time of day according to your system.	`="Time of the day is " & TimeOfDay()`
`Timer`	Returns a double value representing the number of seconds elapsed since midnight.	`="Number of seconds since midnight " & Timer()`
`TimeSerial`	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))`
`TimeString`	Returns or sets a string value representing the current time of day according to your system.	`=TimeString()`
`TimeValue`	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)`
`Today`	Returns or sets a date value containing the current date according to your system.	`="Tomorrow is " & DateAdd("d", 1, Today())`
`Weekday`	Returns an integer value containing a number that represents the day of the week.	`=Weekday(Fields!BirthDate.Value,0)`
`WeekdayName`	Returns a string value containing the name of the specified weekday.	`=WeekdayName(2,True,0)`
`Year`	Returns an integer value from 1 through 9999 representing the year.	`=Year(Fields!BirthDate.Value)`

Math Functions

Math functions in Web Report Designer expressions perform numeric calculations such as rounding, logarithms, and trigonometry.

References	Description	Example
`Abs`	Returns the absolute value of a single-precision floating-point number.	`=Abs(Fields!YearlyIncome.Value - 80000)`
`Acos`	Returns the angle whose cosine is the specified number.	`=Acos(Fields!Angle.Value)`
`Asin`	Returns the angle whose sine is the specified number.	`=Asin(Fields!Angle.Value)`
`Atan`	Returns the angle whose tangent is the specified number.	`=Atan(Fields!Tangent.Value)`
`tan2`	Returns the angle whose tangent is the quotient of two specified numbers.	`=Atan2(Fields!CoordinateY.Value,Fields!CoordinateX.Value)`
`BigMul`	Produces the full product of two 32-bit numbers.	=`BigMul(Fields!Int32Value.Value, Fields!Int32Value.Value)
`Ceiling`	Returns the smallest integer that is greater than or equal to the specified integer.	`=Ceiling(Fields!YearlyIncome.Value / 7)`
`Cos`	Returns the cosine of the specified angle.	`=Cos(Fields!Angle.Value)`
`Cosh`	Returns the hyperbolic cosine of the specified angle.	`=Cosh(Fields!Angle.Value)`
`Exp`	Returns e raised to the specified power.	`=Exp(Fields!IntegerCounter.Value)`
`Fix`	Returns an integer portion of a number.	`=Fix(Fields!YearlyIncome.Value /-3)`
`Floor`	Returns the largest integer less than or equal to the specified integer.	`=Floor(Fields!YearlyIncome.Value / 12)`
`Int`	Returns an integer portion of a number.	`=Int(Fields!YearlyIncome.Value / 12)`
`Log`	Returns the natural (base e) logarithm of a specified number.	`=Log(Fields!NumberValue.Value)`
`Log10`	Returns the base 10 logarithm of a specified number.	`=Log10(Fields!NumberValue.Value)`
`Max`	Returns the maximum value from all non-null values of the specified expression.	`=Max(Fields!YearlyIncome.Value, "AdventureWorks", Recursive)`
`Min`	Returns the minimum value from all non-null values of the specified expression.	`=Min(Fields!YearlyIncome.Value, "AdventureWorks", Recursive)`
`Pow`	Returns a specified number raised to the specified power.	`=Pow(Fields!YearlyIncome.Value,2)`
`Rnd`	Returns a random number of single type.	`=Rnd(-1)`
`Round`	Rounds a double-precision floating-point value to the nearest integer.	`=Round(Fields!YearlyIncome.Value / 12, 2)`
`Sign`	Returns a value indicating the sign of an 8-bit signed integer.	`=Sign(Fields!YearlyIncome.Value - 60000)`
`Sin`	Returns the sine of the specified angle.	`=Sin(Fields!Angle.Value)`
`Sinh`	Returns the hyperbolic sine of the specified angle.	`=Sinh(Fields!Angle.Value)`
`Sqrt`	Returns the square root of a specified number.	`=Sqrt(Fields!Area.Value)`
`Tan`	Returns the tangent of the specified angle.	`=Tan(Fields!Angle.Value)`
`Tanh`	Returns the hyperbolic tangent of the specified angle.	`=Tanh(Fields!Angle.Value)`

Inspection Functions

Inspection functions in Web Report Designer expressions return a Boolean value that describes the type or state of an expression.

References	Description	Example
`IsArray`	Returns a Boolean value indicating whether variable points to an array.	`=IsArray(Parameters!Initials.Value)`
`IsDate`	Returns a Boolean value indicating whether an expression represents a valid.	`=IsDate(Fields!BirthDate.Value)`
`IsNothing`	Returns a Boolean value indicating whether an expression has no object.	`=IsNothing(Fields!MiddleInitial.Value)`
`IsNumeric`	Returns a Boolean value indicating whether an expression can be evaluated as a number.	`=IsNumeric(Fields!YearlyIncome.Value)`

Program Flow Functions

Program flow functions in Web Report Designer expressions return a value based on the evaluation of conditions or a list of arguments.

References	Description	Example
`Choose`	Selects and returns a value from a list of arguments.	`=Tanh(Fields!Angle.Value)=Choose(Datepart("w", Fields!BirthDate.Value), "First", "Second", "Third", "Fourth", "Fifth", "Sixth", "Seventh")`
`IIf`	Returns one of two objects depending upon the evaluation of an expression.	`=Tanh(Fields!Angle.Value) =IIf(Fields!YearlyIncome.Value >= 60000,"High","Low")`
`Switch`	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 Functions

Aggregate functions in Web Report Designer expressions calculate summary values, such as sums, counts, and averages, over a scope.

References	Description	Example
`Avg`	Returns the average of all non-null values from the specified expression.	`=Avg(Fields!YearlyIncome.Value, "GroupByGender", Recursive)`
`Count`	Returns a count of the values from the specified expression.	`=CountDistinct(Fields!MiddleInitial.Value, "GroupByInitial", Recursive)`
`CountDistinct`	Returns a count of all distinct values from the specified expression.	`=CountDistinct(Fields!MiddleInitial.Value, "GroupByInitial", Recursive)`
`CountRows`	Returns a count of rows within the specified scope.	`=CountRows("GroupByInitial", Recursive)`
`First`	Returns the first value from the specified expression.	`=First(Fields!MiddleInitial.Value, "AdventureWorks")`
`Last`	Returns the last value from the specified expression.	`=Last(Fields!MiddleInitial.Value, "AdventureWorks")`
`Max`	Returns the maximum value in the given expression.	`=Max(Fields!YearlyIncome.Value, "AdventureWorks", Recursive)`
`Min`	Returns the minimum value in the given expression.	`=Min(Fields!YearlyIncome.Value, "AdventureWorks", Recursive)`
`StDev`	Returns the standard deviation of all non-null values of the specified expression.	`=StDev(Fields!YearlyIncome.Value,"GroupByInitial",Recursive)`
`StDevP`	Returns the population standard deviation of all non-null values of the specified expression.	`=StDevP(Fields!YearlyIncome.Value,"GroupByInitial",Recursive)`
`Sum`	Returns a sum of the values of the specified expression.	`=Sum(Fields!YearlyIncome.Value,"GroupByInitial",Recursive)`
`Var`	Returns the variance of all non-null values of the specified expression.	`=Var(Fields!YearlyIncome.Value,"GroupByInitial",Recursive)`
`VarP`	Returns the population variance of all non-null values of the specified expression.	`=VarP(Fields!YearlyIncome.Value,"GroupByInitial")`
`RunningValue`	Uses a specified function to return a running aggregate of the specified expression.	`=RunningValue(Fields!YearlyIncome.Value,Sum,"AdventureWorks")`
`Aggregate`	Returns a custom aggregate of the specified expression, as defined by the data provider.	`=Aggregate(Fields!Order_Count.Value)`

Financial Functions

Financial functions in Web Report Designer expressions calculate depreciation, annuity payments, interest, and related values.

References	Description	Example
`DDB`	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)`
`FV`	Returns 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)`
`IPmt`	Returns 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)`
`NPer`	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)`
`Pmt`	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)`
`PPmt`	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)`
`PV`	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)`
`Rate`	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)`
`SLN`	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)`
`SYD`	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)`

Conversion Functions

Conversion functions in Web Report Designer expressions convert a value to another data type or representation.

References	Description	Example
`CBool`	Convert to Boolean.	`=CBool(Fields!HouseOwnerFlag.Value)`
`CByte`	Convert to byte.	`=CByte(Fields!Number.Value)`
`CChar`	Convert to char.	`=CChar(Fields!MaritalStatus.Value)`
`CDate`	Convert to date.	`=CDate(Fields!BirthDate.Value)`
`CDbl`	Convert to double.	`=CDbl(Fields!YearlyIncome.Value)`
`CDec`	Convert to decimal.	`=CDec(Fields!YearlyIncome.Value)`
`CInt`	Convert to integer.	`=CInt(Fields!YearlyIncome.Value)`
`CLng`	Convert to lone.	`=CLng(Fields!YearlyIncome.Value)`
`CObj`	Convert to object.	`=CObj(Fields!YearlyIncome.Value)`
`CShort`	Convert to short.	`=CShort(Fields!NumberCarsOwned.Value)`
`CSng`	Convert to single.	`=CSng(Fields!YearlyIncome.Value)`
`CStr`	Convert to string.	`=CStr(Fields!YearlyIncome.Value)`
`Fix`	Returns an integer portion of a number.	`=Fix(Fields!YearlyIncome.Value / -3)`
`Hex`	Returns a string representing the hexadecimal value of a number.	`=Hex(Fields!CellColor.Value)`
`Int`	Returns an integer portion of a number.	`=Int(Fields!YearlyIncome.Value / 12)`
`Oct`	Returns a string representing the octal value of a number.	`=Oct(Fields!BitString.Value)`
`Str`	Returns a string that represents a number.	`=Str(Fields!YearlyIncome.Value)`
`Val`	Returns numbers in a string as a numeric value of appropriate type.	`=Val(Fields!AddressLine1.Value)`

Miscellaneous Functions

Miscellaneous functions in Web Report Designer expressions return scope, level, lookup, previous row, and row number information.

References	Description	Example
`InScope`	Returns true if the current instance is within the specified scope.	`=InScope("table1_Group1")`
`Level`	Returns a zero-based integer representing the current depth level.	`=Level("GroupByInitial")`
`Lookup`	Use Lookup to retrieve the value from the specified dataset for a name-value pair where there is a 1-to-1 relationship.	`=Lookup(Fields!SaleProdId.Value, Fields!ProductID.Value, Fields!Name.Value, "Product")`
`LookupSet`	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.	`=LookupSet(Fields!TerritoryGroupID.Value, Fields!TerritoryID.Value, Fields!StoreName.value, "Stores")`
`Previous`	Returns the value of the expression for the previous row of data.	`=Previous(Fields!FirstName.Value)`
`RowNumber`	Returns a running count of all rows in the specified scope.	`=RowNumber("AdventureWorks")`