Searching substrings in MDX

Reposted from Jason Thomas blog with the author's permission.

A quick tip for the beginners. Most of you would be familiar with substring searches in SQL. Today, we will see how to replicate the same scenario in MDX.

1) Suppose we have to find all the employee names having ‘David’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount]
FROM Employee
WHERE EmployeeName LIKE ‘%David%’

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on columns,
filter([Employee].[Employees].allmembers,
instr([Employee].[Employees].currentmember.member_caption,'David')>0) on ROWS
from [Adventure Works]

2) To find all the employee names not having ‘David’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount]
FROM Employee
WHERE EmployeeName NOT LIKE ‘%David%’

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on columns,
filter([Employee].[Employees].allmembers,
instr([Employee].[Employees].currentmember.member_caption,'David')=0) on ROWS
from [Adventure Works]

3) You can write multiple conditions also. For e.g., to find all the employee names having ‘David’ as a substring but not having ‘am’ as a substring, we write the following SQL query

SELECT EmployeeName, [Internet Sales Amount]
FROM Employee
WHERE EmployeeName LIKE ‘%David%
AND ’EmployeeName NOT LIKE ‘%am%’ 

This same query can be replicated in MDX as below

SELECT [Measures].[Internet Sales Amount] on columns,
filter([Employee].[Employees].allmembers,
instr([Employee].[Employees].currentmember.member_caption,'David')>0 and
instr([Employee].[Employees].currentmember.member_caption,'am')=0) on ROWS
from [Adventure Works]