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
