CODEWITHSUNDEEP
sqlsql-server-2008-r2mdx
udaya726
udaya726

Reputation: 1020

MDX query dynamic where clause

SELECT NON EMPTY  {  [Measures].[Total  Value],[Measures].[Value less than 30], 
[Measures].[Value less than 60],[Measures].[Value less than 90],[Measures].[Value less than 150],
[Measures].[Value less than 180],[Measures].[Value less than 365],[Measures].[Value more than 365]}   
DIMENSION PROPERTIES CHILDREN_CARDINALITY,  PARENT_UNIQUE_NAME ON COLUMNS,   
NON EMPTY  {[Combined].[Drill Down Path 4].[Supplier Name].ALLMEMBERS }   
DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM [InventoryAge]  
WHERE ( [Calendar].[Report Days].[All Members].&[All].&[WantInReport].&[2].&[20141031] )

for the where clause I want to get the last element of my calender dimension. The calender dimension is as follows

enter image description here

What is the best way to achieve this

Upvotes: 0

Views: 331

Answers (1)

Rubik
Rubik

Reputation: 1471

Try something like this:

WHERE ( [Calendar].[Report Days].[All Members].[All].[WantInReport].[Last Days].LastChild )

You should be able to use the name of your members (but with removing & in front of them), that's why .&[WantInReport]. become .&[WantInReport]..

I'd rather use .[Last Days]. than .&[2]., easier to understand when you look later at the query.

Finally using .LastChild gives you last item of your selected branch.

Upvotes: 2

Related Questions