none
MDX returning NULL in CASE Statement

    Question

  • I am working in SQL 2008 R2

    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 TEST 444
    the tuple units 1,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
    Saturday, September 28, 2013 5:32 PM

Answers

All replies

  • I am working in SQL 2008 R2.

    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 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 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])


    Select {[Fiscal Period].[Fiscal Year].&[2013]} on columns
    , {[Measures].[POU CASE TEST]
    , [Measures].[the tuple units]
    , [Measures].[POU Units]
    } on rows
    from [My Cube]
    where [Product].[Product Category].&[03-32]

    Result"

                    2013
    POU CASE TEST   444
    the tuple units 1,000
    POU Units      (null)


    What am I missing???

    Thanks in advance 

    Saturday, September 28, 2013 5:39 PM
  • I later figured out that the test statements in the case were different types.

    I had "IS" mixed in with "=".  I made all of the tests "IS" and the problem was solved

    Friday, October 11, 2013 1:35 PM