Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MDX returning NULL Expand / Collapse
Author
Message
Posted Saturday, September 28, 2013 10:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 16, 2014 7:52 AM
Points: 22, Visits: 119
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 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
Post #1499698
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse