SQL Server Analysis services MDX: How to write a query that works as LIKE operator in SQL?

Standard

Problem Statement:

In MDX, How to search for a sub-string using something similar to LIKE operator in SQL?

Solution:

here’s an example using Contoso Sales Cube, please use this to get started for your scenario:

Note the use of INSTR function.


select
[Measures].[Sales Amount]
on 0,
Filter(
[Geography].[Region Country Name].[Region Country Name]
,instr([Geography].[Region Country Name].CURRENTMEMBER.MEMBER_CAPTION,'United')>0)
on 1
from
[Sales]

SSAS MDX analysis services like SQL

Also,
if you are looking for NOT LIKE then replace >0 with =0 and you should get the desired results.

Conclusion:
In this post, you saw how to write a MDX query that does sub-string search for you similar to LIKE and NOT LIKE operators in SQL.

One thought on “SQL Server Analysis services MDX: How to write a query that works as LIKE operator in SQL?

What do you think? Leave a comment below.