ActiveReports 11
Common Functions
ActiveReports 11 > ActiveReports User Guide > Concepts > Page Report/RDL Report Concepts > Expressions > Common Functions
Show All
Hide All

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.

  • DateAdd
  • DateDiff
  • DatePart
  • DateSerial
  • DateString
  • DateValue
  • Now
  • Today
  • Day
  • Hour
  • Minute
  • Month
  • MonthName
  • Second
  • TimeSerial
  • TimeValue
  • TimeOfDay
  • Timer
  • TimeString
  • Weekday
  • WeekdayName
  • Year

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.

  • Abs
  • Acos
  • Asin
  • Atan
  • Atan2
  • BigMul
  • Ceiling
  • Cos
  • Cosh
  • E
  • Exp
  • Fix
  • Floor
  • IEEERemainder
  • Log
  • Log10
  • Max
  • Min
  • PI
  • Pow
  • Round
  • Sign
  • Sin
  • Sinh
  • Sqrt
  • Tan
  • Tanh

Inspection

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

  • IsArray
  • IsDate
  • IsDBNull
  • IsError
  • IsNothing
  • IsNumeric

Program Flow

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

  • Choose
  • IIf
  • Switch
  • Partition

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 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(Fields!Discontinued.Value=True, Sum, Fields!InStock.Value)
Avg Calculates the average of the non-null values returned by the expression. =Avg(Fields!Cost.Value, Nothing)
Count Calculates the number of non-null values returned by the expression. =Count(Fields!EmployeeID.Value, Nothing)
CountDistinct Calculates the number of non-repeated values returned by the expression. =CountDistinct(Fields!ManagerID.Value, "Department")
CountRows Calculates the number of rows in the scope returned by the expression. =CountRows("Department")
CumulativeTotal Calculates the sum of page-level aggregates returned by the expression for current and previous pages. =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(Fields!OrderID.Value, Fields!OrderFreight.Value, "Order")
First Shows the first value returned by the expression. =First(Fields!ProductNumber.Value, "Category")
Last Shows the last value returned by the expression. =Last(Fields!ProductNumber.Value, "Category")
Max Shows the largest non-null value returned by the expression. =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(Fields!OrderTotal.Value)
Min Shows the smallest non-null value returned by the expression =Min(Fields!OrderTotal.Value)
Mode Shows the value that appears most frequently in the values returned by the expression. =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(Fields!Cost.Value, Sum, Nothing)
StDev Calculates the dispersion (standard deviation) of all non-null values returned by the expression. =StDev(Fields!LineTotal.Value, "Order")
StDevP Calculates the population dispersion (population standard deviation) of all non-null values returned by the expression. =StDevP(Fields!LineTotal.Value, "Order")
Sum Calculates the sum of the values returned by the expression. =Sum(Fields!LineTotal.Value, "Order")
Var Calculates the variance (standard deviation squared) of all non-null values returned by the expression. =Var(Fields!LineTotal.Value, "Order")
VarP Calculates the population variance (population standard deviation squared) of all non-null values returned by the expression. =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.

  • ToBoolean
  • ToByte
  • ToDateTime
  • ToDouble
  • ToInt16
  • ToInt32
  • ToInt64
  • ToSingle
  • ToUInt16
  • ToUInt32
  • ToUInt64

Miscellaneous

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. Please see the msdn Built-in Functions for ReportViewer Reports topic for more information. 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(optional <Scope>)
=Level()
Previous Returns the previous value within the indicated scope. 
=Previous(<Expression>)
=Previous(Fields!OrderID.Value)
RowNumber Shows a running count of all the rows in the scope returned by the expression. 
=RowNumber(optional <Scope>)
=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. More  pair ore information, see 
Report Builder Functions - Lookup Function.		 
=Lookup(<SourceExpression>, <DestinationExpression>, <ResultExpression>, <LookupDataset>)
=Lookup(Fields!ProductID.Value, Fields!ProductID.Value, Fields!Quantity.Value, "DataSet2")

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
Expressions

How To

Data Visualizers