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
