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:
- Like - searching a pattern of string/character in a text
- Not like - searching a pattern of string/character which is not available in a text
- Starts with - searching a pattern of string/character at the beginning of a text
- 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
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:
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.
Searching Tags
- Wildcard in MDX
- Contains in MDX
- Contain in MDX
- Wild_Card in MDX
- Like in MDX