CODEWITHSUNDEEP
mysqlsqlmdx
user2871294
user2871294

Reputation: 51

Get record keys in MDX query

I have a query

WITH
MEMBER Measures.ProductKey as [Hand].[Name1].Currentmember.Member_Key
SELECT NON EMPTY
{ [Measures].[Netto], [Measures].[Cost], Measures.ProductKey } Dimension Properties CHILDREN_CARDINALITY,
PARENT_UNIQUE_NAME ON COLUMNS,
NON EMPTY
{ [Hand].[Nazwa1], [Hand].[Nazwa1].Children } Dimension Properties CHILDREN_CARDINALITY,
PARENT_UNIQUE_NAME ON ROWS
FROM [Bild]

This query return Netto, Cost and Key. But when Netto and Cost is null I don't want to show this record. Without Measures. ProductKey is good but I don't have key.

Upvotes: 0

Views: 281

Answers (1)

SouravA
SouravA

Reputation: 5243

Make a small change to the code for the calculated member ProductKey as below:

WITH
MEMBER Measures.ProductKey as 
IIF([Measures].[Netto] = NULL AND [Measures].[Cost] = NULL, NULL, [Hand].[Name1].Currentmember.Member_Key)

//Here have added a condition which first checks the values of other two measures. 
//If they are NULL, it sets the value of ProductKey as NULL as well. 
//The NON EMPTY clause then does its job of removing the row of data from output.

SELECT NON EMPTY
{ [Measures].[Netto], [Measures].[Cost], Measures.ProductKey } Dimension Properties CHILDREN_CARDINALITY,
PARENT_UNIQUE_NAME ON COLUMNS,
NON EMPTY
{ [Hand].[Nazwa1], [Hand].[Nazwa1].Children } Dimension Properties CHILDREN_CARDINALITY,
PARENT_UNIQUE_NAME ON ROWS
FROM [Bild]

Upvotes: 2

Related Questions