User-Defined Functions

OLE DB Programmer's Reference

OLE DB for OLAP allows the user to create and register functions that operate on multidimensional data. User-defined functions (UDFs) can accept arguments and return values within the context of an MDX statement.

To register one or more UDFs, client applications execute the statement USE LIBRARY. Its syntax is as follows:

USE LIBRARY <program_id> [, <program_id> ]

where <program_id> is a ProgId that identifies either a type library or an individual class.

To unload the library, client applications issue the DROP LIBRARY statement. Its syntax is as follows:

DROP LIBRARY <program_id> [, <program_id> ] | ALL

When used with the ALL flag, DROP LIBRARY will unload all libraries loaded for that user session. Otherwise DROP LIBRARY expects a list of <program_id> values identifying the libraries to be unloaded.

Calling a User-Defined Function in MDX

OLE DB for OLAP defines a standard mechanism for passing data types between the OLE DB for OLAP provider and the library containing the user-defined functions.

When MDX objects are to be passed to a user-defined function, the object must first be explicitly converted to a VARIANT data type using one of the string and array manipulation functions, such as SETTOSTR or SETTOARRAY.

The string manipulation functions convert an MDX set, tuple, or member to a BSTR of the same format. For example,

SETTOSTR({[Seattle], [Tacoma], [Stowe]})

would return the following string:

"{[Geography].[City].[Seattle], [Geography].[City].[Tacoma], [Geography].[City].[Stowe]}"

Note that the unique names were returned.

Cell values are passed to the UDF using one of the VARIANT data types. This table lists the supported values.

VARIANT type	Description
VT_I2	16-bit integer
VT_I4	32-bit integer
VT_BOOL	Boolean
VT_UI1	Single character
VT_R4	Single precision floating point
VT_R8	Double precision floating point
VT_CY	Currency
VT_DATE	Date
VT_BSTR	String
VT_VARIANT	Variant
VT_ARRAY | <type>	Safe Array of any of the other types listed in this table

User-defined functions can take any number of parameters, and optional parameters are supported. Optional parameters are specified using the optional tag in the IDL file.

Overloading functions based on type or return value is not allowed.

When resolving function names, the OLE DB for OLAP provider will give standard MDX functions highest precedence. UDFs are then evaluated according to precedence rules that are provider-specific. Use of the fully qualified function name will avoid ambiguous references. Function names are fully qualified by using the syntax <library_name>!<interface_name>!<function_name>.

USE LIBRARY CurrencyLib.ConversionClass WITH MEMBER Measures.ProfitInPounds AS    'CurrencyLib!ICurrencyConvert!ConvertToGBP([Profit])' SELECT {Measures.ProfitInPounds} ON COLUMNS, [Product Family].MEMBERS ON ROWS FROM Sales

Note the use of the syntax <library_name>!<interface_name>!<function_name> in the query above. The formal syntax is as follows:

[<library_name>!][<interface_name>!]<function_name>(<parameters>)

The <library_name> and <interface_name> modifiers can be used independently or together.

The SETTOARRAY function can be used to convert a set to an array of the set's evaluated values. The values are consistent with the context of the entire MDX statement. For example, the following would return a table of the weighted average of unit sales for the year 1998:

SELECT MyLibrary!WeightedAverage(SETTOARRAY({[Seattle], [Tacoma]})) ON COLUMNS, [Product Family].MEMBERS ON ROWS WHERE [1998], [Measures].[Unit Sales] FROM Sales

The value passed to the WeightedAverage UDF would be a SAFEARRAY of the appropriate type. For the preceding example, if we made certain assumptions about the data types and contents of the cube, we would get back a two-dimensional safe-array. One dimension of the array would be defined by {Seattle, Tacoma}, the other dimension defined by [Product Family].MEMBERS. The values in the array would be a numeric type such as VT_R4 and would be the unit sales for 1998 at that coordinate.

Handling Return Values

The following table indicates allowable UDF return types. Of course, for an Automation interface, the actual return value of the function is an HRESULT. The return value discussed here is in fact a parameter to the function, specified using retval in the IDL file.

VARIANT type	Description
VT_I2	16-bit integer
VT_I4	32-bit integer
VT_BOOL	Boolean
VT_UI1	Single character
VT_R4	Single precision floating point
VT_R8	Double precision floating point
VT_CY	Currency
VT_DATE	Date
VT_BSTR	String
VT_VARIANT	Variant
VT_ARRAY | <type>	Safe Array of any of the other types listed in this table

If the return type is an MDX object such as a set, tuple, or member, the UDF return value should be a string. The appropriate string manipulation function should be used to convert to the correct type.

1998-2001 Microsoft Corporation. All rights reserved.