You can use functions in any report item property expression to perform actions on data in data regions, groupings, and datasets.

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 item 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 item belongs.

The Scope parameter must be a data region, grouping, or dataset that directly or indirectly contains the report item using the function in its expression. If the report item 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.

Aggregate Functions

You can use aggregate functions within report item value expressions to accrue data. Here are the aggregate functions supported by Data Dynamics Reports:

Function	Description
Aggregate	Calculates a custom aggregate from the data provider of the values returned by the expression.
Avg	Calculates the average of the non-null values returned by the expression.
Count	Calculates the number of non-null values returned by the expression.
CountDistinct	Calculates the number of non-repeated values returned by the expression.
CountRows	Calculates the number of rows in the scope returned by the expression.
DistinctSum	Calculates the sum of the values returned by an expression using only the rows when the value of another expression is not repeated.
First	Shows the first value returned by the expression.
Last	Shows the last value returned by the expression.
Max	Shows the largest non-null value returned by the expression.
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.
Min	Shows the smallest non-null value returned by the expression
Mode	Shows the value that appears most frequently in the values returned by the expression.
RowNumber	Shows a running count of all the rows in the scope returned by the expression.
RunningValue	Shows a running aggregate of values returned by the expression. (Takes one of the other aggregate functions as a parameter.)
StDev	Calculates the dispersion (standard deviation) of all non-null values returned by the expression.
StDevP	Calculates the population dispersion (population standard deviation) of all non-null values returned by the expression.
Sum	Calculates the sum of the values returned by the expression.
Var	Calculates the variance (standard deviation squared) of all non-null values returned by the expression.
VarP	Calculates the population variance (population standard deviation squared) of all non-null values returned by the expression.

Examples:

Aggregate:

Aggregate(Fields!LineTotal.Value, "Order")

Average:

Avg(Fields!Cost.Value, Nothing)

Count:

Count(Fields!EmployeeID.Value, Nothing)

Count Distinct:

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

Count Rows:

CountRows("Department")

Distinct Sum:

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

First Value:

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

Last Value:

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

Maximum Value:

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

Minimum Value:

Min(Fields!OrderTotal.Value)

Running Count of Rows:

RowNumber(Nothing)

Running Value:

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

Standard Deviation:

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

Population Standard Deviation:

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

Summary:

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

Variation:

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

Population Variation:

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

Other Functions

Data Dynamics Reports 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. 

Function	Description
InScope	Determines whether the current value is in the indicated scope.
Level	Returns the level of the current value in a recursive hierarchy. For more information, see Grouping and Sorting Data.
Previous	Returns the previous value within the indicated scope.
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.

Examples:

In Scope:

InScope("Order")

Level:

Level("EmployeeID")

Previous:

Previous(Fields!OrderID.Value)

GetFields:

=Code.DisplayAccountID( GetFields() )

'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

Other Ways to Use Functions

Custom Code

Learn to use custom code and custom assemblies in your reports.

See Also