Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / MDX

Wild Card in MDX Queries

4.50/5 (2 votes)
26 Feb 2016CPOL1 min read 20.9K  
This tip discusses about the MDX-Wild Card expressions (like 'Like Operator in SQL) with examples.

Introduction

Today, 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
SQL
InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") <> 0

Example:

SQL
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
SQL
InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") = 0

Example:

SQL
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
SQL
InStr([Dim Grp].[Dim Name].MEMBER_NAME, "xxx") = 1

Example:

SQL
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:

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

Example:

SQL
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
SQL
Right([Dim Grp].[Dim Name].MEMBER_NAME, 3) = "xxx"

Example:

SQL
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.

Searching Tags

  1. Wildcard in MDX
  2. Contains in MDX
  3. Contain in MDX
  4. Wild_Card in MDX
  5. Like in MDX

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)