Wild Card in MDX Queries

This tip discusses about the MDX-Wild Card expressions (like 'Like Operator in SQL) with examples.

Introduction

In this article we are going to learn about WILD CARD expressions in MDX queries. Like T-SQL, there are many ways to achieve this. But I am going to discuss the most easy way to do it.

Background

While working in any programming languages, we usually use *regular expression *for searching a pattern in a text. When it comes to MDX, we find ourselves struggling to write filter expressions because it's not easy as SQL-LIKE operator. There are some expression/techniques which might help you in writing better filter expressions.

Discussion

I will discuss the following features in this tip:

1. Like - searching a pattern of string/character in a text
2. Not like - searching a pattern of string/character which is not available in a text
3. Starts with - searching a pattern of string/character at the beginning of a text
4. Ends with - searching a pattern of string/character at the end of a text

Syntaxes with Examples

1. Like

To search a specific character/string

InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") <> 0

Example:

SELECT {[Measures].[Sales]} ON COLUMNS
    , FILTER([Dim Grp].[Dim Name].[Dim Name]
        , InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") <> 0) ON ROWS
FROM [Cube]
WHERE [Dim Time].[Year].&[2016]

2. Not Like

To not match a specific character/string

InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") = 0

Example:

SELECT {[Measures].[Sales]} ON COLUMNS
    , FILTER([Dim Grp].[Dim Name].[Dim Name]
        , InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") = 0) ON ROWS
FROM [Cube]
WHERE [Dim Time].[Year].&[2016]

3. Starts with

To search a specific character/string at the beginning

a, 
  InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") = 1

Example:

SELECT {[Measures].[Sales]} ON COLUMNS
    , FILTER([Dim Grp].[Dim Name].[Dim Name]
        , InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") = 1) ON ROWS
FROM [Cube]
WHERE [Dim Time].[Year].&[2016]

or:

b, 
  Left([Dim Grp].[Dim Name].MEMBER_NAME, 3) = "xxx"

Example:

SELECT {[Measures].[Sales]} ON COLUMNS
    , FILTER([Dim Grp].[Dim Name].[Dim Name]
        , Left([Dim Grp].[Dim Name].MEMBER_NAME, 3) = "xxx") ON ROWS
FROM [Cube]
WHERE [Dim Time].[Year].&[2016]

4. Ends with

To search a specific character/string at the end

Right([Dim Grp].[Dim Name].MEMBER_NAME, 3) = "xxx"

Example:

SELECT {[Measures].[Sales]} ON COLUMNS
    , FILTER([Dim Grp].[Dim Name].[Dim Name]
        , Right([Dim Grp].[Dim Name].MEMBER_NAME, 3) = "xxx") ON ROWS
FROM [Cube]
WHERE [Dim Time].[Year].&[2016]

Conclusion

I believe this tip is very useful for the free search filter scenarios and I hope you found it useful. I am waiting for your valuable thoughts and comments. Your feedback is always welcome.