User-Defined Functions with MDX Syntax
You can create and register your own functions that operate on multidimensional data. These functions, called user-defined functions, can accept arguments and return values in the
Before you use a user-defined function, you must register the library (that is, file) in which it is compiled. You can register user-defined function libraries of the following types:
- Type libraries (*.olb, *.tlb, *.dll)
- Executable files (*.exe, *.dll)
- ActiveX controls (*.ocx)
To register a user-defined function library, issue a USE LIBRARY statement. Its syntax is:
USE LIBRARY "<library_path_and_file_name>" | <program_ID>
[,"<library_path_and_file_name>" | <program_ID>...]
Example:
USE LIBRARY "c:\functions\mylib.dll"
To register multiple libraries, issue a USE LIBRARY statement with multiple parameters in a comma-separated list. Example:
USE LIBRARY "c:\functions\mylib.dll","c:\functions\johnslib.dll"
A USE LIBRARY statement with no parameters unregisters all function libraries except the Microsoft SQL Server™ 2000 Analysis Services function library.
Hidden and restricted user-defined functions are not supported.
Note User-defined functions are supported only if they accept as arguments only string or numeric data types, or array or variant data types containing string or numeric values. In addition, user-defined functions are supported only if they return only string or numeric data types, or variant data types containing numeric values.
Multiple user-defined functions can reside in the same ActiveX library.
Calling a User-Defined Function within MDX
After a user-defined function is registered, it can be used anywhere in the MDX syntax that allows expressions. For example:
With Member Measures.[Forecasted Sales] As
'Sales * ForecastedGrowthRate(SaleReps.CurrentMember.Name)'
Select TopCount(SalesReps, HowManyReps(), Sales) on Rows,
{Sales, [Forecasted Sales] } on Columns
From Sales
The HowManyReps and ForecastedGrowthRate user-defined functions are defined as:
Public Function HowManyReps() as Integer
Public Function ForecastedGrowthRate(RepName as String) as Double
User-defined functions can also be used in Calculated Member Builder.
Note When you call a user-defined function, you can omit an optional argument only if you also omit all arguments that follow it.
Function Precedence and Qualification
If multiple function libraries contain a function with the same name, the Analysis Services function library takes precedence. Excluding the Analysis Services function library, precedence is resolved in order of registration by the USE LIBRARY statement.
You can override precedence or call functions from specific libraries by using the following syntax when you invoke the function:
programid!functionname(argument1,argument2,...)
The function name is preceded by the function library's program ID and an exclamation point (!). This syntax ensures that the correct function is called in cases where a function name is not unique among libraries.
If a library includes multiple interfaces, you can use the following syntax to specify the library and interface:
programid!interfaceid!functionname(argument1,argument2,...)