MDX returning NULL

• Chris Schmidt

  Say Hey Kid

  Points: 686

  More actions

  September 28, 2013 at 10:49 am

  #280302

  I am working in SQL 2008 R2 however this is the only BI FORUM I saw.

  I really need a second set of eyes on an MDX I’m writing and would appreciate any help.

  For a cube project, the business has reuested some POU Calculated measures:

  POU Revenue

  POU Units

  The Product Hierarchy is straightforward:

  All

  Product Line

  Product Category

  Product Sub Category

  Product

  POU is defined as:

  Either Product Line 3 or 4, category 32.

  (category 32 exists in both product lines)

  The problem is, When I run the MDX for either "Product Line 3 Category 32 level" or "Product Line 4 Category 32 level" I get a NULL returned.

  (I have tried swapping out the NULL in my code with a dummy value already to make sure it wasn’t dropping into the NULL logic) I also have TEST logic to confirm that the case statement is working.

  I’m running out of ideas and would love to get your opinion on this one.

  /*

  -------------- POU

  product_line = '03' or product_line = '04'

  and category_code = '32'

  */

  WITH

  MEMBER [Measures].[POU Revenue] AS

  CASE

  WHEN [Product].CurrentMember IS [Product].[All]

  THEN ([Measures].[Total Revenue],[Product].[Product Category].&[03-32])

  + ([Measures].[Total Revenue],[Product].[Product Category].&[04-32])

  WHEN [Product].CurrentMember = [Product].[Product Line].&[03]

  THEN ([Measures].[Total Revenue],[Product].[Product Category].&[03-32])

  WHEN [Product].CurrentMember = [Product].[Product Line].&[04]

  THEN ([Measures].[Total Revenue],[Product].[Product Category].&[04-32])

  // stops working here

  WHEN [Product].CurrentMember = [Product].[Product Category].&[03-32]

  THEN ([Measures].[Total Revenue],[Product].[Product Category].&[03-32])

  WHEN [Product].CurrentMember = [Product].[Product Category].&[04-32]

  THEN ([Measures].[Total Revenue],[Product].[Product Category].&[04-32])

  WHEN [Product].CurrentMember.LEVEL.NAME = "Product Subcategory"

  OR [Product].CurrentMember.LEVEL.NAME = "Product"

  THEN iif(Ancestor([Product].CurrentMember, [Product Category]) IS [Product].[Product Category].&[03-32]

  OR Ancestor([Product].CurrentMember, [Product Category]) IS [Product].[Product Category].&[04-32]

  ,([Measures].[Total Revenue], [Product].CurrentMember)

  , NULL)

  ELSE NULL

  END

  MEMBER [Measures].[POU Units] AS

  CASE

  WHEN [Product].CurrentMember IS [Product].[All]

  THEN ([Measures].[Total Units],[Product].[Product Category].&[03-32])

  + ([Measures].[Total Units],[Product].[Product Category].&[04-32])

  WHEN [Product].CurrentMember = [Product].[Product Line].&[03]

  THEN ([Measures].[Total Units],[Product].[Product Category].&[03-32])

  WHEN [Product].CurrentMember = [Product].[Product Line].&[04]

  THEN ([Measures].[Total Units],[Product].[Product Category].&[04-32])

  // Seems to work up to here

  WHEN [Product].CurrentMember = [Product].[Product Category].&[03-32]

  THEN ([Measures].[Total Units],[Product].[Product Category].&[03-32])

  //THEN ([Measures].[Total Units],[Product].CurrentMember)

  WHEN [Product].CurrentMember = [Product].[Product Category].&[04-32]

  THEN ([Measures].[Total Units],[Product].[Product Category].&[04-32])

  WHEN [Product].CurrentMember.LEVEL.NAME = "Product Subcategory"

  OR [Product].CurrentMember.LEVEL.NAME = "Product"

  THEN iif(Ancestor([Product].CurrentMember, [Product Category]) IS [Product].[Product Category].&[03-32]

  OR Ancestor([Product].CurrentMember, [Product Category]) IS [Product].[Product Category].&[04-32]

  ,([Measures].[Total Units], [Product].CurrentMember)

  , NULL)

  ELSE NULL

  END

  MEMBER [Measures].[POU CASE TEST] AS

  CASE

  WHEN [Product].CurrentMember IS [Product].[All]

  THEN (111)

  WHEN [Product].CurrentMember = [Product].[Product Line].&[03]

  THEN (222)

  WHEN [Product].CurrentMember = [Product].[Product Line].&[04]

  THEN (333)

  WHEN [Product].CurrentMember = [Product].[Product Category].&[03-32]

  THEN (444)

  WHEN [Product].CurrentMember = [Product].[Product Category].&[04-32]

  THEN (555)

  WHEN [Product].CurrentMember.LEVEL.NAME = "Product Subcategory"

  OR [Product].CurrentMember.LEVEL.NAME = "Product"

  THEN iif(Ancestor([Product].CurrentMember, [Product Category]) IS [Product].[Product Category].&[03-32]

  OR Ancestor([Product].CurrentMember, [Product Category]) IS [Product].[Product Category].&[04-32]

  , (666)

  , (777))

  ELSE 888

  END

  member [Measures].[the tuple units] as

  ([Measures].[Total Units],[Product].[Product Category].&[03-32])

  member [Measures].[the tuple revenue] as

  ([Measures].[Total Revenue],[Product].[Product Category].&[03-32])

  Select {[Fiscal Period].[Fiscal Year].&[2013]} on columns

  , {[Measures].[POU CASE TEST]

  , [Measures].[the tuple units]

  , [Measures].[the tuple revenue]

  , [Measures].[POU Revenue]

  , [Measures].[POU Units]

  } on rows

  from [My Cube]

  where [Product].[Product Category].&[03-32]

  Result

  2013

  POU CASE TEST444

  the tuple units1,000

  the tuple revenue$50,000,000.22

  POU Revenue(null)

  POU Units(null)

  (numbers have been modified for privacy)

  What am I missing???

  Thanks in advance