Answered by:
MDX returning NULL in CASE Statement

-
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
Question
Answers
-
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
- Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, October 14, 2013 1:20 AM
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 UnitsThe Product Hierarchy is straightforward:
All
Product Line
Product Category
Product Sub Category
ProductPOU 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 hereWHEN [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
ENDMEMBER [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
ENDmember [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
- Merged by Elvis LongMicrosoft contingent staff, Moderator Monday, September 30, 2013 2:49 AM duplicate
-
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
- Marked as answer by Elvis LongMicrosoft contingent staff, Moderator Monday, October 14, 2013 1:20 AM