Miscellaneous Functions

Lookup

Returns the first matching value of the specified field from the specified dataset.

Syntax

Lookup(source, destination, result, dataset)

Arguments

source - an expression that evaluates in the current scope and specifies the key to look up.
destination - an expression that evaluates for each row in a dataset and specifies the key to match on.
result - an expression that evaluates for the row in the dataset where source = destination and specifies the value to return.
dataset - a string that indicates a dataset's name in the report.

Examples

The Get Started with Tabular Reports demo uses the following expression to display the category name from the Categories dataset within the table bound to the Products dataset.

{Lookup(categoryId, categoryId, categoryName, "Categories")}

LookupSet

Returns all the matchings value of the specified field from the specified dataset.

Syntax

LookupSet(source, destination, result, dataset)

Arguments

source - an expression that evaluates in the current scope and specifies the key to look up.
destination - an expression that evaluates for each row in a dataset and specifies the key to match on.
result - an expression that evaluates for the row in the dataset where source = destinationand specifies the value to return.
dataset - a string that indicates a dataset's name in the report.

Examples

You can use the following expression to find all the stores with the current scope's TerritoryID value.

{LookupSet(TerritoryID, TerritoryID, StoreName, "Stores")}

Returns the value of the specified expression in the previous instance of an item within the specified scope.

Syntax

Previous(expression, [scope])

Arguments

expression - the expression on which to calculate the summary value
scope - the optional calculation scope

Examples

You can use the following expression to return the previous value of the SalesCount field in the current scope.

{Previous(SalesCount)}

RowNumber

Returns a running count of the number of rows for the specified scope.

Syntax

RowNumber([scope])

Arguments

scope - the optional calculation scope

GroupIndex

Returns a zero-based index of a grouping instance

Syntax

GroupIndex([group name])

Arguments

group name—specifies the name of the group for which to return the index. If omitted, the function will default to returning the index of the closest grouping.

Join

Returns a string created by joining substrings contained in an array.

Syntax

Join(sourcearray, [ delimiter ])

Arguments

sourcearray - an array containing substrings to be joined.
delimiter - an optional character used to separate the substrings in the returned string. If omitted, the space character (" ") is used.

Examples

You can use the following expression to display the products list from the category with the current CategoryID value.

{Join(LookupSet(CategoryId, CategoryId, ProductName, "Products"), ",")}

IndexOf

Returns the first index at which a given element can be found in the array or string, or -1 if it is not present.

Syntax

IndexOf(source, searchElement)

Arguments

source – the source where the IndexOf function will search for the element. The source can be an array or a string. If the source is an array, the method will search for the element among the elements of the array. If the source is a string, the method will search for the element among the characters of the string.
searchElement – the element that the method will search for in the source.

Examples

The following expression determines if the current value of the ContinentName field is contained in the value of the multi-value parameter pContinent.

{IndexOf(@pContinent, ContinentName) >= 0}

Partition

Returns a string indicating where a number occurs within a calculated series of ranges.

Syntax

Partition(number, start, stop, interval)

Arguments

number - the number that you want to evaluate against the ranges.
start - the number that is the start of the overall range of numbers. The number can't be less than 0.
stop - the number that is the end of the overall range of numbers. The number can't be equal to or less than start.
interval - the number that is the difference between one range and the next. The number can't be less than 1.

Examples

You can use the following expression to display the range where the current value of the Freight field falls into

{Partition(Freight, 0, 500, 50)}

Level

Returns the current level of depth in a recursive hierarchy

Syntax

Level([scope])

Arguments

scope - the optional calculation scope.

Examples

The Interactive Hierarchy demo uses the following expression for the left padding of a textbox that displays the Store Name.

{CStr(Level() * 12 + 12)}pt