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” (UDFs), can accept arguments and return values in the MDX syntax. You can create UDFs using Component Object Model (COM) automation languages such as Microsoft® Visual Basic® or Microsoft Visual C++®. A UDF can be developed on any tool capable of generating Microsoft ActiveX® libraries.

Before you use a UDF, you must register the library (that is, file) in which it is compiled. You can register UDF libraries of the following types:

To register a UDF 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™ OLAP Services function library.

Hidden and restricted UDFs are not supported.


Note UDFs 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, UDFs are supported only if they return only string or numeric data types, or variant data types containing numeric values.

Multiple UDFs can reside in the same ActiveX library.


Calling a User-defined Function within MDX

After a user-defined function (UDF) is registered, it can be used anywhere in the MDX syntax that allows expressions. For example:

With Member Measures.[Forecasted Sales] As

     'Sale * ForecastedGrowthRate(SaleReps.CurrentMember.Name)'

Select TopCount(SalesReps, HowManyReps(), Sales) on Rows,

     {Sales, [Forecasted Sales] } on Columns

From Sales

The HowManyReps and ForecastedGrowthRate UDFs are defined as:

Public Function HowManyReps() as Integer

Public Function ForecastedGrowthRate(RepName as String) as Double

UDFs can also be used in the Calculated Member Builder.


Note When you call a UDF, 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 OLAP Services function library takes precedence. Excluding the OLAP 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,…)

Note that 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,…)

(c) 1988-1998 Microsoft Corporation. All Rights Reserved.