MDX Sum syntax problem

Hi All,

I'm extremely new to MDX, and I'm having some trouble getting one of my
expressions to work. I would appreciate any help with this hopefully
easy problem.

I have a cube like this:

Subcription_CruiseId
HomeAirportId
AccountId

With dimensions like this:

(related to Subcription_CruiseId)
SubscriptionStatusId
SubscriptionStatus

(related to HomeAirportId)
AirportId
AirportCode

The account id is a measure that has a distinct count for its aggregate
function property.

I am trying to find the total number of accounts that are subscribed to
recieve cruise info and have a home airport of Seattle or Houston.
This is my MDX statement:

select
sum (
{ [HomeAirportCode_Dim].[SEA]
, [HomeAirportCode_Dim].[IAH] }
, Measures.[Account Id] )
on columns,
{ [SubCruise_Dim].[Subscribed]}
on rows
from Account_Fact

I get an error: Unable to open cell set - Formula Error - cannot
convert expression to set - in a statistical set 1 function.

Would someone tell me what I'm doing wrong?

TIA,
Nedra

Nedra,

The SELECT statement is always expecting a set on rows and columns and even though you placed curly braces {} around your SUM calculation you are not returning a set. The SUM function returns a value and not a member that could be used to build a set. The other thing to note is that if you are using a distinct count function you do not really want a SUM, but the correct count for the selected members combined. In other words if I was counting unique visitors to a web site and I had:

4 visitors in January

7 visitors in February

The total number of unique visitors in both months is not 7 + 4. The total may be 11 if all visitors only hit the web site once, but it is more likely that the value is something like 8 due to the fact that 3 of the visitors in January also visited in February. I hope this makes sense. Fortunately there is another MDX function called "Aggregate" that will work with distinct count measures to give you the correct result. The following MDX uses a query scoped member created using a WITH clause to produce the result I think you are looking for:

WITH MEMBER [Total Value] AS

Aggregate( {[HomeAirportCode_Dim].[SEA]
, [HomeAirportCode_Dim].[IAH] }

, Measures.[Account Id]

SELECT

{[Total Value]} ON COLUMNS,

{ [SubCruise_Dim].[Subscribed] } ON ROWS

FROM

[Account_Fact]

HTH,

- Steve

|||

Thank you so much! It works now. I understand what you are saying about returning a set versus a single value now. That really helped clarify things.

Nedra