Databases Reference
In-Depth Information
loaded when a query uses a function in the DLL. For backward
compatibility reasons the USE LIBRARY syntax is accepted by
Analysis Services 2005, but it is just a NO OP (no operations are
executed when the statement is encountered).
Accessing COM UDFs in MDX Queries
In order to retrieve the cumulative profit along with profit for various years,
you need to use the MDX function SettoArray to translate a set of members in
Analysis Services to an array that can be passed to a UDF. The following
query calculates the cumulative profit across various years of ship date:
WITH
Member [Measures].[Year] AS '[Ship
Date].[CalendarYear - CalendarSemesterCalendarQuarter -
EnglishMonthName].Currentmember.UniqueName'
member [Measures].[Cum Profit] AS '
PartialSum (
SettoArray ([Ship Date].[CalendarYear -
CalendarSemester - CalendarQuarter -
EnglishMonthName].[CalendarYear],[Measures].[Profit]),
SetToArray ( [Ship Date].[CalendarYear -
CalendarSemester - CalendarQuarter -
EnglishMonthName].[CalendarYear],[Measures].[Year] ),
[Ship Date].[CalendarYear - CalendarSemester -
CalendarQuarter -
EnglishMonthName].CurrentMember.Uniquename)'
SELECT { [Measures].[Profit], [Measures].[Cum
Profit]} on columns,
[Ship Date].[CalendarYear - CalendarSemester -
CalendarQuarter -
EnglishMonthName].[CalendarYear] ON ROWS
from [Adventure Works DW]
You will see the results shown in the table below when you execute this query
against the Adventure Works DW database.
Search WWH ::
Custom Search