CODEWITHSUNDEEP
ssasmdx
sqluser
sqluser

Reputation: 5672

How can I get NULL values returned in MDX

I have two dimensions named T and L

When I run this:

SELECT
  {measures.[value]} on 0,
  {nonempty([T].[Hierarchy Items].[T1])} on 1
FROM [Cube]
WHERE 
  (
   [L].[L3].&[49733]
  )

I get all the T1 members and the where clause applies

But when I remove the nonempty function

SELECT
  {measures.[value]} on 0,
  {[T].[Hierarchy Items].[T1]} on 1
FROM [Cube]
WHERE 
  (
   [L].[L3].&[49733]
  )

I will get all T1 members and the where clause does not apply.

Is there any way I get all null values also and the filter(where) applies too?

Upvotes: 1

Views: 2564

Answers (2)

whytheq
whytheq

Reputation: 35557

Maybe something like this:

WITH
SET S AS
EXISTS( 
  [T].[Hierarchy Items].[T1].members,
  { [L].[L3].&[49733] },
  "MeasureGroupName"
)
SELECT
  {measures.[value]} on 0,
  S on 1
FROM [Cube];

Upvotes: 0

MoazRub
MoazRub

Reputation: 2911

You need to understand how nonempty works. For example I want to see the InternetSalesAmount for 2011, consider the query below 

select [Measures].[Internet Sales Amount] on 0 ,
[Product].[Category].[Category]
on 1 
from 
[Adventure Works]
where 
[Date].[Calendar Year].&[2011]

Result:

enter image description here

Now the query return all products that were sold or not sold in 2011. For products that were sold it returns a sales amount. However the other three products were returned as a result of cartesian product between 2011 and product(MDX's where behavior is diffrent from SQL's where). Now I want to see only the products that were sold in 2011. 

select [Measures].[Internet Sales Amount] on 0 ,
nonempty(
[Product].[Category].[Category],
[Measures].[Internet Sales Amount]) on 1 
from 
[Adventure Works]
where 
[Date].[Calendar Year].&[2011]

Result

enter image description here

For this purpose I can use {non empty} or {nonempty} keywords to tell MDX only to retrive those combinations which have a valid entry in the fact table. Since the fact table has no row for 2011 that has the other three products therefore it will not return them.

Upvotes: 1

Related Questions