ActiveReports 14
ActiveReports 14 User Guide / Concepts / Page Report/RDL Report Concepts / Expressions / Common Functions
In This Topic
    Common Functions
    In This Topic

    You can use a function in an expression to perform actions on data in data regions, groups and datasets. You can access these functions in the Expression Editor dialog. In any property that accepts expressions, you can drop down the property and select <Expression...> to open the dialog.

    Within the Expression Editor dialog, there is a tree view of Fields. Expand the Common Functions node to view the available functions. The following tables contain details about each of the functions included in ActiveReports for use in property expressions.

    Date & Time

    These are all methods from the DateAndTime class in Visual Basic. Please see the msdn DateAndTime Class topic for information on overloads for each method.

    These are all the available aggregate functions:

    Function Description Syntax and Example
    DateAdd Returns a date and time value that is the result of adding the interval to the date and time field of the specified unit.

    DateAdd(<DateInterval>,<Number>,<DateTime>)

    =DateAdd("d", 5, Fields!SaleDate.Value); =DateAdd(DateInterval.Day, 5, Fields!SaleDate.Value)

    DateDiff Returns the difference between the start date and time and end date and time of the specified unit. 

    DateDiff(<DateInterval>,<DateTime1>,<DateTime2>[,<DayOfWeek>[,WeekOfYear]]))

    =DateDiff("yyyy"), Fields!SaleDate.Value,"1/1/2015");=DateDiff(DateInterval.Year,Fields!SaleDate.Value,"1/1/2015")

    DatePart Returns the Integer value that represents the specified part of the given date.

    DatePart(<DateInterval>,<DateTime1>[,<FirstDayOfWeek>[,FirstWeekOfYear]]))

    =DatePart("m", Fields!SaleDate.Value)

    DateSerial Returns a Date value that represents a specified year, month, and a day, with the time information set to midnight (00:00:00).

    DateSerial(<Year Number>,<Month Number>,<Day Number>)

    =DateSerial(DatePart("yyyy", Fields!SaleDate.Value)-10, DatePart("m", Fields!SaleDate.Value)+5, DatePart("d", Fields!SaleDate.Value)-1)

    DateString Returns the String value that represents the current date in your system.

    DateString()

    =DateString()

    DateValue Returns a Date value that contains the information on date represented by a string, with the time set to midnight (00:00:00).

    DateValue(<StringDate>)

    =DateValue("December 12, 2015")

    Now Returns the current date and time in your system.

    Now()

    =Now()

    Today Returns a Date value that contains the current date in your system.

    Today()

    =Today()

    Day Returns an Integer value from 1 through 31 that represents the day of the month.

    Day(<DateTime>)

    =Day(Fields!SaleDate.Value)

    Hour Returns an Integer value from 0 through 23 that represents the hour of the day.

    Hour(<DateTime>)

    =Hour(Fields!SaleDate.Value)

    Minute Returns an Integer value from 0 through 59 that represents the minute of the hour.

    Minute(<DateTime>)

    =Minute(Fields!SaleDate.Value)

    Month Returns an Integer value from 0 through 12 that represents the month of the year.

    Month(<DateTime>)

    =Month(Fields!SaleDate.Value)

    MonthName Returns the name of the month specified in the date as a String.

    MonthName(<Month Number>[,<Abbreviate>])

    =MonthName(Fields!SaleDate.Value)

    Second Returns an Integer value from 0 through 59 that represents the second of the minute.

    Second(<DateTime>)

    =Second(Fields!SaleDate.Value)

    TimeSerial Returns a Date value that represents a specified hour, minute, and second, with the date information set relative to January 1 of the year 0001.

    TimeSerial(<Hour Number>, <Minute Number>, <Second Number>)

    =TimeSerial(DatePart("h", Fields!SaleDate.Value), DatePart("n", Fields!SaleDate.Value), DatePart("s", Fields!SalesDate.Value))

    TimeValue Returns a Date value that contains the information on time represented by a string, with the date set to January 1 of the year 0001.

    TimeValue(<StringTime>)

    =TimeValue("15:25:45"); TimeValue(Fields!SaleDate.Value)

    TimeOfDay Returns a Date value containing the current time of day in your system.

    TimeOfDay()

    =TimeOfDay()

    Timer Returns a Double value that represents the number of seconds elapsed since midnight.

    Timer()

    =Timer()

    TimeString Returns the String value that represents the current time of day in your system.

    TimeString()

    =TimeString()

    Weekday Returns an Integer value that contains a number representing the day of the week.

    Weekday(<DateTime[,<DayOfWeek>])

    =Weekday(Fields!SaleDate.Value,0)

    WeekdayName Returns a String value that contains the name of the specified weekday.

    WeekdayName(<WeekDay>[,<Abbreviate[, <FirstDayOfWeek>]])

    =WeekdayName(3, True, 0); =WeekDayName("w", Fields!SaleDate.Value), True, 0)

    Year Returns an Integer value from 1 through 9999 representing the year.

    Year(<DateTime>)

    =Year(Fields!SaleDate.Value)

    Quarter Returns an Integer value from 1 through 4 representing the quarter number.

    Quarter(<DateTime>)

    =Quarter(Fields!SaleDate.Value)

    QuarterName Returns a string value representing the quarter name.

    QuarterName(<DateTime>)

    =QuarterName(Fields!SaleDate.Value)

           

    Math

    These are all methods and fields from the System.Math class. Please see the msdn Math Class topic for information on overloads for each method.

    Function Description Syntax and Example
    Abs Returns the absolute or positive value of a single-precision floating-point number.

    Abs(<Number>)

    =Abs(-5.5);=Abs(Fields!YearlyIncome.Value-80000)

    Acos Returns the angle whose cosine is the specified number.

    Acos(<Number>)

    =Acos(.5); =Acos(Fields!Angle.Value)

    Asin Returns the angle whose sine is the specified number

    Asin(<Number>)

    =Asin(.5); =Asin(Fields!Angle.Value)

    Atan Returns the angle whose tangent is the specified number.

    Atan(<Number>)

    =Atan(.5); =Atan(Fields!Angle.Value)

    Atan2 Returns the angle whose tangent is the quotient of two specifed numbers.

    Atan2(<Number1>,<Number2>)

    =Atan2(3,7); =Atan2(Fields!CoordinateY.Value,Fields!CoordinateX.Value)

    BigMul Returns the multiplication of two 32-bit numbers.

    BigMul(<Number1>,<Number2>)

    =BigMul(4294967295,-2147483647); =BigMul(Fields!Int32Value.Value, Fields!Int32Value.Value)

    Ceiling Returns the smallest integer greater than or equal to the specified double-precision floating-point number.

    Ceiling(<Number>)

    =Ceiling(98.4331); =Ceiling(Fields!AnnualSales.Value /6)

    Cos Returns the smallest integer greater than or equal to the specifed double-precision floating-point number.

    Cos(<Number>)

    =Cos(60)

    Cosh Returns the hyperbolic cosine of the specified angle.

    Cosh(<Number>)

    =Cosh(60)

    E Returns the value of E, which is 2.71828182845905.

    =E*2

    Exp Returns e raised to the specified ^, where is Euler s number. It is the inverse of the Log function.

    Exp(<Number>)

    =Exp(3); =Exp(Fields!IntegerCounter.Value)

    Fix Returns the integer portion of a number.

    Fix(<Number>)

    =Fix(-7.15); =Fix(Fields!AnnualSales.Value /-5)

    Floor Returns the longest integer less than or equal to the specified double-precision floating-point number.

    Floor(<Number>)

    =Floor(4.67); =Floor(Fields!AnnualSales.Value/ 12)

    IEEERemainder Returns the remainder after division of one number by another according to IEEE satndards.

    IEEERemainder(<Number1>,<Number2>)

    =IEEERemainder(9,8)

    Log Returns the logarithm of the specified number.

    Log(<Number>)

    =Log(20.5); =Log(Fields!NunberValue.Value)

    Log10 Returns the logarithm of the specified number to the base 10.

    Log10(<Number>)

    =Log10(20.5); =Log10(Fields!NumberValue.Value)

    Max Returns the maximum non-null value from the specified expression.

    Max(<Value>)

    =Max(Fields!OrderTotal.Value)

    Min Returns the minimum non-null value from the specified expression.

    Min(<Value>)

    =Min(Fields!OrderTotal.Value)

    PI Returns the value of PI, which is 3.14159265358979.

    PI

    =2 * PI * Fields!Radius.Value

    Pow Returns one number raised to the ^ of another number.

    Pow(<Number1,<Number2>)

    =Pow(Fields!Quantity.Value, 2)

    Round Returns the round-off of a decimal number to the nearest integer or to the nearest decimal number up to the specified digits.

    Round(<Number>)

    =Round(12.456); =Round(Fields!AnnualSales.Value / 12.3)

    Sign Returns a value indicating the sign of an 8-bit signed integer.

    Sign(<Number>)

    =Sign(Fields!AnnualSales.Value-60000)

    Sin Returns the sine of the specified number.

    Sin(<Number>)

    =Sin(60)

    Sinh Returns the hyperbolic sine of the specified angle.

    Sinh(<Number>)

    =Sinh(60)

    Sqrt Returns the square root of the specified number.

    Sqrt(<Number>)

    =Sqrt(121)

    Tan Returns the tangent of the specified number.

    Tan(<Number>)

    =Tan(60)

    Tanh Returns the hyperbolic tangent of the specified angle.

    Tanh(<Number>)

    =Tanh(60)

           

    Inspection

    These are all methods from the DateAndTime class in Visual Basic. Please see the msdn DateAndTime Class topic for information on overloads for each method.

    Function Description Syntax and Example
    IsArray Returns True if the expression can be evaluated as an array.

    IsArray(<Expression>)

    =IsArray(Parameters!Initials.Value)

    IsDate Returns True if the expression represents a valid Date value.

    IsDate(<Expression>)

    =IsDate(Fields!BirthDate.Value); =IsDate("31/12/2010")

    IsDBNull Returns True if the expression evaluates to a null.

    IsDBNull(<Expression>)

    =IsDBNull(Fields!MonthlySales.Value)

    IsError Returns True if the expression evaluates to an error.

    IsError(<Expression>)

    =IsError(Fields!AnnualSales.Value = 80000)

    Isnothing Returns True if the expression evaluates to nothing.

    IsNothing(<Expression>)

    =IsNothing(Fields!MiddleInitial.Value)

    IsNumeric Returns True if the expression can be evaluated as a number.

    IsNumeric(<Expression>)

    =IsNumeric(Fields!AnnualSales.Value)

           

    ProgramFlow

    These are all methods from the Interaction class in Visual Basic. Please see the msdn Interaction Class topic for more information.

    Function Description Syntax and Example
    Choose Returns a value from a list of arguments.

    Choose(<Index>,<Value>[, <Value2>,...[, <Value N>]])

    =Choose(3, "10", "15", "20", "25")

    IIF Returns the value if the expression evaluates to True, and the second value if the expression evaluates to False.

    IIF(<Condition>, <TruePart>, <FalsePart>)

    =IIF(Fields!AnnualSales.Value >= 80000, "Above Average", "Below Average")

    Partition Returns a string (in the form x : y) that represents the calculated range based on the specified interval containing the specified number.

    Partition(<Value>, <Start>, <End>, <Interval>)

    =Partition(1999, 1980, 2000, 10)

    Switch Returns the value of the first expression that evaluates to True among a list of expressions.

    Switch(<Condition1>, <Value1>[, <Condition2>, <Value2>,...[,<ConditionN>, <ValueN>]])

    =Switch(Fields!FirstName.Value = "Abraham", "Adria", Fields!FirstName.Value = "Charelotte", "Cherrie")

           

    Aggregate

    You can use aggregate functions within report control value expressions to accrue data. ActiveReports supports aggregate functions from RDL 2005, plus some proprietary extended set of functions. For all of the functions, you can add an optional <Scope> parameter.

    These are all the available aggregate functions:

    Function Description Syntax and Example
    AggregateIf Decides whether to calculate a custom aggregate from the data provider of the values returned by the expression based on a Boolean expression.

    AggregateIf(<Condition>, <AggregateFunction>, <AggregateArguments>)

    =AggregateIf(Fields!Discontinued.Value=True, Sum, Fields!InStock.Value)

    Avg Calculates the average of the non-null values returned by the expression.

    Avg(<Values>)

    =Avg(Fields!Cost.Value, Nothing)

    Count Calculates the number of non-null values returned by the expression.

    Count(<Values>)

    =Count(Fields!EmployeeID.Value, Nothing)

    CountDistinct Calculates the number of non-repeated values returned by the expression.

    CountDistinct(<Values>)

    =CountDistinct(Fields!ManagerID.Value, "Department")

    CountRows Calculates the number of rows in the scope returned by the expression.

    CountRows()

    =CountRows("Department")

    CumulativeTotal Calculates the sum of page-level aggregates returned by the expression for current and previous pages.

    CumulativeTotal(<Expression>, <Aggregate>)

    =CumulativeTotal(Fields!OrderID.Value, Count)

    DistinctSum Calculates the sum of the values returned by an expression using only the rows when the value of another expression is not repeated.

    DistinctSum(<Values>, <Value>)

    =DistinctSum(Fields!OrderID.Value, Fields!OrderFreight.Value, "Order")

    First Shows the first value returned by the expression.

    First(<Values>)

    =First(Fields!ProductNumber.Value, "Category")

    Last Shows the last value returned by the expression.

    Last(<Values>)

    =Last(Fields!ProductNumber.Value, "Category")

    Max Shows the largest non-null value returned by the expression.

    Max(<Values>)

    =Max(Fields!OrderTotal.Value, "Year")

    Median Shows the value that is the mid-point of the values returned by the expression. Half of the values returned will be above this value and half will be below it.

    Median(<Values>)

    =Median(Fields!OrderTotal.Value)

    Min Shows the smallest non-null value returned by the expression

    Min(<Values>)

    =Min(Fields!OrderTotal.Value)

    Mode Shows the value that appears most frequently in the values returned by the expression.

    Mode(<Values>)

    =Mode(Fields!OrderTotal.Value)

    RunningValue Shows a running aggregate of values returned by the expression (Takes one of the other aggregate functions as a parameter),

    RunningValue(<Values>, <AggregateFunction>)

    =RunningValue(Fields!Cost.Value, Sum, Nothing)

    StDev Calculates the dispersion (standard deviation) of all non-null values returned by the expression.

    StDev(<Values>)

    =StDev(Fields!LineTotal.Value, "Order")

    StDevP Calculates the population dispersion (population standard deviation) of all non-null values returned by the expression.

    StDevP(<Values>)

    =StDevP(Fields!LineTotal.Value, "Order")

    Sum Calculates the sum of the values returned by the expression.

    Sum(<Values>)

    =Sum(Fields!LineTotal.Value, "Order")

    Var Calculates the variance (standard deviation squared) of all non-null values returned by the expression.

    Var(<Values>)

    =Var(Fields!LineTotal.Value, "Order")

    VarP Calculates the population variance (population standard deviation squared) of all non-null values returned by the expression.

    VarP(<Values>)

    =VarP(Fields!LineTotal.Value, "Order")

    Conversion

    These are all methods from the Convert class in the .NET Framework. Please see the msdn Convert Class topic for more information.

    Function Description Syntax and Example
    ToBoolean Converts the specified value to Boolean.

    ToBoolean(<Value>)

    =ToBoolean(Fields!HouseOwnerFlag.Value)

    ToByte Converts the specified value to Byte.

    ToByte(<Value>)

    =ToByte(Fields!ProductNumber.Value)

    ToDateTime Converts the specified value to a Date and Time value.

    ToDateTime(<Value>)

    =ToDateTime(Fields!SaleDate.Value); =ToDateTime("1 January, 2017")

    ToDouble Converts the specified value to Double.

    ToDouble(<Value>)

    =ToDouble(Fields!AnnualSales.Value); =ToDouble(535.85 * .2691 * 67483)

    ToInt16 Converts the specified value to a 16-bit signed Integer.

    ToInt16(<Value>)

    =ToInt16(Fields!AnnualSales.Value); =ToInt16(535.85)

    ToInt32 Converts the specified value to a 32-bit signed Integer.

    ToInt32(<Value>)

    =ToInt32(Fields!AnnualSales.Value)

    ToInt64 Converts the specified value to a 64-bit signed Integer.

    ToInt64(<Value>)

    =ToInt64(Fields!AnnualSales.Value)

    ToSingle Converts the specified value to a single-precision floating-point number.

    ToSingle(<Value>)

    =ToSingle(Fields!AnnualSales.Value); =ToSingle(15.857692134)

    ToUInt16 Converts the specified value to a 16-bit unsigned Integer.

    ToUInt16(<Value>)

    =ToUInt16(Fields!AnnualSales.Value)

    ToUInt32 Converts the specified value to a 32-bit unsigned Integer.

    ToUInt32(<Value>)

    =ToUInt32(Fields!AnnualSales.Value)

    ToUInt64 Converts the specified value to a 64-bit unsigned Integer.

    ToUInt64(<Value>)

    =ToUInt64(Fields!AnnualSales.Value)

           

    Miscelleneous

    ActiveReports also offers several functions which do not aggregate data, but which you can use with an IIf function to help determine which data to display or how to display it.

    The first four are miscellaneous functions from the RDL 2005 specifications. GetFields is a proprietary function to extend RDL specifications.

    Function Description Syntax and Example
    InScope Determines whether the current value is in the indicated scope. InScope(<Scope>)
    =InScope("Order")
    Level Returns the level of the current value in a recursive hierarchy. Level()
    =Level()
    Previous Returns the previous value within the indicated scope. Previous(<Value>)
    =Previous(Fields!OrderID.Value)
    RowNumber Shows a running count of all the rows in the scope returned by the expression. RowNumber()
    =RowNumber()
    GetFields

    Returns an IDictionary<string,Field> object that contains the current contents of the Fields collection. Only valid when used within a data region. This function makes it easier to write code that deals with complex conditionals. To write the equivalent function without GetFields() would require passing each of the queried field values into the method which could be prohibitive when dealing with many fields.

    GetFields()
    =Code.DisplayAccountID(GetFields())
    Custom function. Paste in the Code tab.
    Copy Code
    'Within the Code tab, add this function.
    
    Public Function DisplayAccountID( flds as Object) as Object
    
      If flds("FieldType").Value = "ParentAccount" Then
    
        Return flds("AccountID").Value
    
      Else
    
        Return flds("ParentAccountID").Value
    
      End If
    
    End Function
    
    
    Lookup Returns the first matching value for the specified name from the dataset with pairs of name and value. For more information, see 
    Report Builder Functions - Lookup Function.

    Lookup(<SourceExpression>, <DestinationExpression>, <ResultExpression>, <LookupDataset>)

    =Lookup(Fields!ProductID.Value, Fields!ProductID.Value, Fields!Quantity.Value, "DataSet2")

    LookupSet Returns multiple row values from a specified dataset and can be used for the 1-to-many relationship. For more information, see 
    Report Builder Functions - LookupSet Function.

    LookupSet(source_expression, destination_expression, result_expression, dataset)

    =LookupSet(Fields!CategoryID.Value, Fields!CategoryID.Value, Fields!UnitsInStock.Value, "Products")

    MapPoint Allows displaying simple data directly on the Map as a map Point Layer.

    MapPoint(<Latitude>, <Longitude>)

    =MapPoint(Fields!Latitude.Value, Fields!Longitude.Value)

    Scope

    All functions have a Scope parameter which determines the grouping, data region, or dataset to be considered when calculating the aggregate or other function. Within a data region, the Scope parameter's default value is the innermost grouping to which the report control belongs. Alternately, you can specify the name of another grouping, dataset, or data region, or you can specify Nothing, which sets it to the outermost data region to which the report control belongs.

    The Scope parameter must be a data region, grouping, or dataset that directly or indirectly contains the report control using the function in its expression. If the report control is outside of a data region, the Scope parameter refers to a dataset. If there is only one dataset in the report, you can omit the Scope parameter. If there are multiple datasets, you must specify which one to use to avoid ambiguity.

    Note: You cannot set the Scope parameter to Nothing outside of a data region.

    See Also