SUM and multiple IIF Function condition in MDX

Question

I have written MDX query below Here what i am doing try to getting result of tom based on the multiple condition applying in IIF function :

WITH 
  SET [kpi_study] AS 
    {[study].[study].[BHC June12]} 
  SET [geographic] AS 
    {[territory.market_hierarchy].[state].[MP]} 
  SET [brand] AS 
    {[brand.brand_hierarchy].[brand].[Gold Flake (Unspecified)]} 
  SET [edu12] AS 
    IIF
    (
      'All' = 'All'
     ,[education].[education].MEMBERS
     ,[education].[education].[All]
    ) 
     SET [town] as
    IIF(
        'All' = 'All'
        ,[territory.market_hierarchy].[town_class].MEMBERS
        ,[territory.market_hierarchy].[town_class].[All]
        )
    SET [occp] as
    IIF(
         'All' = 'All'
            ,[occupation].[occupation].MEMBERS
            ,[occupation].[occupation].[All]
       )
    MEMBER [Measures].[t] AS
    SUM(([edu12],[town],[occp]),[Measures].[tom])
SELECT 
  NON EMPTY 
    {[Measures].[t]} ON COLUMNS
FROM [funnel_analysis]
WHERE 
  {[kpi_study]*[geographic]*[brand]}

but getting some error.For single iif function its working fine ie: **(SUM([edu12],[Measures].[tom]))** unable to find out where i am doing wrong for multiple.

Answer 1

I would do an explicit cross join. Also please get rid of those single member custom sets that you're creating - this is not standard practice - just put them straight in your WHERE clause.

WITH 
  SET [edu12] AS 
    IIF(
     'All' = 'All'
     ,{[education].[education].MEMBERS}
     ,[education].[education].[All]
    ) 
  SET [town] as
    IIF(
      'All' = 'All'
      ,{[territory.market_hierarchy].[town_class].MEMBERS}
      ,[territory.market_hierarchy].[town_class].[All]
     )
  SET [occp] as
    IIF(
      'All' = 'All'
      ,{[occupation].[occupation].MEMBERS}
      ,[occupation].[occupation].[All]
     )
  MEMBER [Measures].[t] AS
    SUM(
       [edu12]
      *[town]
      *[occp]
      ,[Measures].[tom]
    )
SELECT 
  NON EMPTY 
    {[Measures].[t]} ON COLUMNS
FROM [funnel_analysis]
WHERE 
  (
   [study].[study].[BHC June12]
  ,[territory.market_hierarchy].[state].[MP]
  ,[brand.brand_hierarchy].[brand].[Gold Flake (Unspecified)]
  )

I'd prefer to try something like the following using Aggregate:

WITH 
  MEMBER [education].[education].[All].[edu12] AS 
    AGGREGATE(IIF(
     'All' = 'All'
     ,{[education].[education].MEMBERS}
     ,[education].[education].[All]
    )) 
  MEMBER [territory.market_hierarchy].[town_class].[All].[town] as
    AGGREGATE(IIF(
      'All' = 'All'
      ,{[territory.market_hierarchy].[town_class].MEMBERS}
      ,[territory.market_hierarchy].[town_class].[All]
     ))
  MEMBER [occupation].[occupation].[All].[occp] as
    AGGREGATE(IIF(
      'All' = 'All'
      ,{[occupation].[occupation].MEMBERS}
      ,[occupation].[occupation].[All]
     ))
  MEMBER [Measures].[t] AS
    (
       [education].[education].[All].[edu12]
      ,[territory.market_hierarchy].[town_class].[All].[town]
      ,[occupation].[occupation].[All].[occp]
      ,[Measures].[tom]
    )
SELECT 
  NON EMPTY 
    {[Measures].[t]} ON COLUMNS
FROM [funnel_analysis]
WHERE 
  (
   [study].[study].[BHC June12]
  ,[territory.market_hierarchy].[state].[MP]
  ,[brand.brand_hierarchy].[brand].[Gold Flake (Unspecified)]
  )

Exploratory script example - does this give you what you'd expect? If it is ok then move on to another bit of your more complex script:

WITH 
  SET [edu12] AS 
    IIF(
     'All' = 'All'
     ,{[education].[education].MEMBERS}
     ,[education].[education].[All]
    ) 
SELECT 
  [edu12] ON ROWS,
 {[Measures].[tom]} ON COLUMNS
FROM [funnel_analysis]
WHERE 
  (
   [study].[study].[BHC June12]
  ,[territory.market_hierarchy].[state].[MP]
  ,[brand.brand_hierarchy].[brand].[Gold Flake (Unspecified)]
  )