ssas calculated member with several member properties

Question

I'm wondering if there's any better way in writing a calculated member like this:

(
  [Measures].[Number of accounts]
 ,[Account Status].[Account Status].&[Anonymous]
 ,[Account Status].[Account Status].&[Closed]
 ,[Account Status].[Account Status].&[Closed due to Fraud]
 ,[Account Status].[Account Status].&[To be closed]
 ,[Account Status].[Account Status].&[<unknown>]
)

Answer 1

This is a tuple - and a very efficient way of locating a part of the cube:

(
  [Measures].[Number of accounts]
 ,[Account Status].[Account Status].&[Anonymous]
 ,[Account Status].[Account Status].&[Closed]
 ,[Account Status].[Account Status].&[Closed due to Fraud]
 ,[Account Status].[Account Status].&[To be closed]
 ,[Account Status].[Account Status].&[<unknown>]
)

...BUT it needs to be a specific point in the cube - but you've included several members from the same hierarchy [Account Status].[Account Status] so this is not a single point in your cube and it will therefore error.

Here is an example of a valid tuple:

WITH 
  MEMBER [exampleTuple] AS 
    (
      [Measures].[Internet Sales Amount]
     ,[Date].[Calendar Year].&[2007]
    ) 
SELECT 
  [exampleTuple] ON 0
 ,{
    [Product].[Category].[Bikes]
   ,[Product].[Category].[Clothing]
  } ON 1
FROM [Adventure Works];

So this gives back internet sales but only for the year 2007:

If I do what you've done and add say another member from the year hierarchy into the tuple then it gets confused as unsure which bit of the cube I'me referring to - 2006 or 2007!

WITH 
  MEMBER [exampleTuple] AS 
    (
      [Measures].[Internet Sales Amount]
     ,[Date].[Calendar Year].&[2007]
     ,[Date].[Calendar Year].&[2006]
    ) 
SELECT 
  [exampleTuple] ON 0
 ,{
    [Product].[Category].[Bikes]
   ,[Product].[Category].[Clothing]
  } ON 1
FROM [Adventure Works];

gives:

double-clicking on the word #Error tells us the exception:

Exactly the exception we'd expect.

A way around this exception is to pre-aggregate the members from the same hierarchy into a single member so the processor knows exactly which part of the cube space to go to:

WITH 
  MEMBER [Date].[Calendar Year].[All].[2006+2007] AS 
    Aggregate({[Date].[Calendar Year].&[2007],[Date].[Calendar Year].&[2006]}) 
  MEMBER [exampleTuple] AS 
    (
      [Measures].[Internet Sales Amount]
     ,[Date].[Calendar Year].[All].[2006+2007]
    ) 
SELECT 
  [exampleTuple] ON 0
 ,{
    [Product].[Category].[Bikes]
   ,[Product].[Category].[Clothing]
  } ON 1
FROM [Adventure Works];

Now we get what we want:

We can then use this initial aggregation to do whatever we want - you mention excluding some member - this is possible:

WITH 
  MEMBER [Date].[Calendar Year].[All].[2006+2007] AS 
    Aggregate
    (
      Except
      (
        [Date].[Calendar Year].[Calendar Year].MEMBERS
       ,{
          [Date].[Calendar Year].&[2007]
         ,[Date].[Calendar Year].&[2006]
        }
      )
    ) 
  MEMBER [exampleTuple] AS 
    (
      [Measures].[Internet Sales Amount]
     ,[Date].[Calendar Year].[All].[2006+2007]
    ) 
SELECT 
  [exampleTuple] ON 0
 ,{
    [Product].[Category].[Bikes]
   ,[Product].[Category].[Clothing]
  } ON 1
FROM [Adventure Works];