Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX returning NULL


MDX returning NULL

Author
Message
Chris Schmidt
Chris Schmidt
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 120
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search