Searching substrings in MDX

Share this...

Twitter

Linkedin

Facebook

Pinterest

Reddit

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]

Update (25/08/2010)

You can also do the same using Stored Procedures. You can create your own Stored Procedures to achieve this or you can also download Analysis Services Stored Procedures project from:
http://www.codeplex.com/wikipage?ProjectName=ASStoredProcedures

After that, you can use the code like this:

SELECT 
{} ON 0
,ASSP.Like([Employee].[Employees].Members
,”%RA%”
,[Employee].[Employees].CurrentMember.Name)
ON 1
FROM [Adventure Works];

Update Courtesy – Charles Wang (MSDN Moderator)