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
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
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
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 :
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