Implementing IN /LIKE in MDX

So following is a list of how IsIn funcationality can be implemented in MDX

1)you want to filter using in clause and show the members too. In this example I want to see the internet sales for bikes and clothing category from adventurewroks sample db

select 
[Measures].[Internet Sales Amount]
on columns,
{[Product].[Category].&[1]
,[Product].[Category].&[3]}
on rows 
from 
[Adventure Works]

Result

enter image description here

2)I want to filter by the IN clause but dont want to show the members In this example I want to see yearly the internet sales for bikes and clothing category from adventurewroks sample db. The result is to be broken by years.

select 
[Measures].[Internet Sales Amount]
on columns,
non empty 
[Date].[Calendar Year].[Calendar Year]
on rows 
from 
[Adventure Works]
where 
{[Product].[Category].&[1]
,[Product].[Category].&[2]}

Result

enter image description here

You achive the same by using subquery

select 
[Measures].[Internet Sales Amount]
on columns,
non empty 
[Date].[Calendar Year].[Calendar Year]
on rows 
from 
(select {[Product].[Category].&[1],[Product].[Category].&[2]} on 0 from [Adventure Works])

Result

enter image description here

3)When you want to implement the IN clause based on name In this example I want to see the internet sales for bikes and clothing category from adventurewroks sample db, but in this case I am using the caption

select 
[Measures].[Internet Sales Amount]
on columns,
filter(
[Product].[Category].[Category],
[Product].[Category].currentmember.name='Bikes' or [Product].[Category].currentmember.name='Clothing'
)
on rows 
from 
[Adventure Works]

Result :

enter image description here

4) When you implement IN clause based on name and your condition is looking for a particular text (Like Clause ) In this example I want to see the internet sales for bikes and clothing category from adventurewroks sample db, but in this case I am searching the the caption name for a piece of string.

select 
[Measures].[Internet Sales Amount]
on columns,
FILTER([Product].[Category].[Category],
Instr([Product].[Category].currentmember.name, 'Bik') > 0
or 
Instr([Product].[Category].currentmember.name, 'oth') > 0
)
on rows 
from 
[Adventure Works]

Result

enter image description here

Tags:

Ssas

Mdx