Pages

Sunday, April 11, 2010

Searching substrings in MDX

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]

Query result

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)

Title MDX

No comments:

Post a Comment