Creating and Using User-Defined Functions in MDX
Multidimensional Expressions (MDX) supplies a great deal of intrinsic functions, designed to accomplish everything from standard statistical calculation to member traversal in a hierarchy. But, as with any other complex and robust product, there is always the need to extend the functionality of such a product further.
To this end, MDX provides the ability to add user-defined function references to MDX statements. This ability is already in common use in MDX; the functionality supplied by external libraries, such as the Microsoft® Excel and Microsoft Visual Basic® for Applications libraries, takes advantage of this capability.
Using a User-Defined Function in MDX
Calling a user-defined function in MDX is done in the same manner as calling an intrinsic MDX function. For a function that takes no parameters, the name of the function and an empty pair of parentheses are used, as shown here:
MyNewFunction()
If the user-defined function takes one or more parameters, then the parameters are supplied, in order, separated by commas. The following example demonstrates a sample user-defined function with three parameters:
MyNewFunctionWithParms("Parameter1", 2, 800)
USE LIBRARY Statement
Before employing a user-defined function in an MDX statement, however, the external library that contains the user-defined function must first be loaded into memory. Loading an external library is performed with the USE LIBRARY statement.
All user-defined functions must be associated with a Component Object Model (COM) class in order to be used, usually supplied in the form of a Microsoft ActiveX® dynamic link library (DLL).
If, for example, the user-defined function is part of an ActiveX DLL named MyFunc.dll, located in the C:\Winnt\System path, you can use the USE LIBRARY statement to load it by the library name, as demonstrated here:
USE LIBRARY "C:\WINNT\SYSTEM\MyFunc.dll"
The USE LIBRARY statement can also load user-defined functions by class name, as each class must be registered in order to work correctly. So, if your example function is located in the example ActiveX DLL and associated with the class "MyFuncClass", the library can be loaded using the following example:
USE LIBRARY "MyFunc.MyFuncClass"
This method is recommended when referring to libraries that may be in different locations on different servers. As ActiveX DLL components must be registered on server and client machines, referring to the class name ensures that the library is loaded from the correct location, regardless of that location.
Multiple libraries can be loaded at the same time with a single USE LIBRARY statement, by separating the library names or class names with commas, as demonstrated here:
USE LIBRARY "C:\WINNT\SYSTEM\MyFunc.dll", "C:\WINNT\SYSTEM\NewFuncs.dll"
A USE LIBRARY statement with no parameters unregisters all function libraries except the Microsoft SQL Server™ 2000 Analysis Services function library.
PivotTable® Service supports the USE LIBRARY statement. For more information about the USE LIBRARY statement, see USE LIBRARY Statement.
DROP LIBRARY Statement
The DROP LIBRARY statement can be used to unload a specific library or to unload all libraries. As with the USE LIBRARY statement, the DROP LIBRARY syntax can accept either the file name or the class name, as demonstrated in the following statement:
DROP LIBRARY "MyFunc.MyFuncClass"
PivotTable Service supports the DROP LIBRARY statement. For more information about the DROP LIBRARY statement, see DROP LIBRARY Statement.
Creating User-Defined Functions
User-defined functions can be created in any programming language that supports COM interfaces.
Parameter and Return Values
A user-defined function can accept any parameter that can be coerced into strings, numbers, or arrays of strings or numbers. User-defined types or object references cannot be used as a parameter. If the parameter data type is explicitly declared as part of the function prototype, such as a double or an integer, Microsoft SQL Server™ 2000 Analysis Services will first coerce values passed into the parameter to the explicitly declared data type. For example, long integer values may be coerced into double precision floating point values if the parameter accepts a Double data type. Date data types are coerced into string representations of a date. PivotTable Service also attempts to coerce strings passed directly into a numeric parameter into numeric values. If coercion fails for any reason, PivotTable Service returns an error condition.
Arrays can also be used as parameters; Analysis Services supports the use of arrays through such functions as SetToArray. As with other parameters, if the data type of the array parameter is explicitly declared as part of the function prototype, PivotTable Service will coerce array values into the explicitly declared data type.
If the data type of the array parameter is not explicitly declared or is declared as a variant array, PivotTable Service will also attempt to coerce the elements of the array. However, PivotTable Service handles the coercion of variables in an array a bit differently; the coerced data type is dependent upon the first element of the array, and all other array elements are expected to conform to the same data type. If, for example, the first element in an array is a string, then it is expected that all of the elements in the array are strings, and PivotTable Service will attempt to coerce the other elements into a string data type. If the first element in an array, such as an empty cell, evaluates as empty, then an empty variant is passed to the array parameter instead of a variant array whose first element is empty. If other elements in the array evaluate as empty, the array element is coerced into a zero. You are recommended to explicitly declare the data types of arrays to be used as parameters in user-defined functions.
Similarly, a user-defined function can return any data type that can be coerced into a number, a string, or a variant. The return values are more restrictive; arrays are not allowed. Additionally, PivotTable Service assumes that if a variant is returned, it contains numeric data. If a string, array, or other non-numeric data is returned through a variant, PivotTable Service returns an error condition for the calculation.
Optional parameters in a function are not supported; PivotTable Service requires all parameters in a user-defined function to be populated.
Functions that return void values (for example, subroutines in Visual Basic) can also be used, but are employed with the CALL keyword. If, for example, you wanted to use the function MyVoidFunction() in an MDX statement, the following syntax would be employed:
CALL(MyVoidFunction)
Other Considerations
As with any other MDX function, an external function must be resolved before an MDX session can continue; external functions lock MDX sessions while executing. Unless a specific reason exists to halt an MDX session pending user interaction, it is strongly recommended that any user interaction, such as dialog boxes, be discouraged.
External function libraries can duplicate the function names of the Analysis Services function library or other external function libraries. Normally, if an external function library contains a function with the same name as a function in the Analysis Services function library, the Analysis Services function library takes precedence. If two external function libraries contain a function with the same name, the registration order of the external function libraries determines precedence.
However, if you want to override precedence or call a function from a specific external function library, the external function can be preceded by the program ID, delimited with an exclamation point character, as demonstrated here:
«ProgramID»!«FunctionName»(«Argument1», «Argument2», ...)
If an external function library supports multiple interfaces, the interface ID can also be used to additionally specify the function, as demonstrated here:
«ProgramID»!«InterfaceID»!«FunctionName»(«Argument1», «Argument2», ...)