Enables you to register other products’ function libraries or user-defined function libraries.
User-defined functions enable client applications to extend the Microsoft® SQL Server™ OLAP Services internal function library. The OLAP Services internal functions always take precedence over registered function libraries. A user-defined function can be developed on any tool capable of generating Microsoft ActiveX® libraries.
You can register user-defined function libraries of the following types:
The order of the libraries in the list is important. It defines the precedence of function name resolution in cases of conflicts (two libraries containing the same function name). The first library takes precedence.
OLAP Services automatically registers the Visual Basic for Applications Expression Services function library (VBA332.dll) and the library that supports the Microsoft Excel worksheet functions. However, the Excel library must be installed separately from OLAP Services.
To add a function library
The program IDs associated with the function library appear in the list box.
To remove a program in a function library
To complete registration
The program IDs of the libraries now appear in the Functions box of the Calculated Member Builder. You can return to this dialog box and add or remove other libraries.
Note 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, functions are supported only if they return only string or numeric data types, or variant data types containing numeric values.
After a user-defined function is registered, it is treated as any other OLAP Services internal function. It can be used anywhere in the MDX syntax that allows for 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 user-defined functions are defined as:
Public Function HowManyReps() as Integer
Public Function ForecastedGrowthRate(RepName as String) as Double
Note There may be multiple user-defined functions residing in the same ActiveX library.