CODEWITHSUNDEEP
mdxmondrian
wessman
wessman

Reputation: 92

MDX: How to make a WHERE when a member from the same Hierarchy already is in rows or cols?

I have a dimension called Date, with one hierarchy in it (default).

From that I want Months on rows and filter on Year = 2014.

This doesn't work:

SELECT 
  [Date].[Month].Members ON 0,
  Measures.Amount ON 1
FROM [GL]
WHERE [Date].[Year].[2014]

I have tried with FILTER, and tuples also but nothing works. In SSAS (Im using Mondrian) I would use a Sub Query but there is no support for that in Mondrian.

Does anyone know how to accomplish this?

Upvotes: 0

Views: 175

Answers (1)

whytheq
whytheq

Reputation: 35557

Try the DESCENDANTSfunction:

SELECT 
  DESCENDANTS(
    [Date].[Year].[2014] //<<this should be a member
    ,[Date].[Month]      //this should be a level name
  ) ON 0,
  Measures.Amount ON 1
FROM [GL];

It should give you the descendants of the member you specify (2014) at the level specified (Month). If you swapped Month for the Date level then you should get all the dates (1st Jan, 2nd Jan...) of 2014.

Upvotes: 1

Related Questions