Hi

Has anyone used SUMPRODUCT in a calculated member in analysis services?
Apparantly, according to MS documentation, its possible, but I can see
nothing in the books online - or on the web.

I am using the syntax below, which I would have thought would have worked.

SUMPRODUCT({[Accounts].&[123],[Accounts].&[456]},{[Accounts].&[111],[Accounts].&[321]})

So does anyone know how to use SUMPRODUCT with AS?

Thank you for your help

Jeremy

Since the arguments to SumProduct() are arrays, use the MDX SetToArray()
function to generate them. The second argument to SetToArray() is the
numerical value to use:

SetToArray({[Accounts].&[123],[Accounts].&[456]},
[Measures].[Sales]),
SetToArray({[Accounts].&[111],[Accounts].&[321]},
[Measures].[Sales]))

From SQL Server BOL>>
SetToArray

Converts one or more sets to an array for use in a user-defined
function.

Syntax

SetToArray(«Set»[, «Set»...][, «Numeric Expression»])

Remarks

This function converts one or more sets to an array for use in a
user-defined function. The number of dimensions in the resulting array
is the same as the number of sets specified.

The optional numeric expression can be used to provide the values in the
array cells. If omitted, the default value of the set member is used for
the array cell value.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!