3

I am new to mdx.

Is there any way to search and return matches from two keywords using MDX like we use in MySQL using LIKE '%iphone' AND LIKE '%samsung'?

My MDXcode is:

SELECT
NON EMPTY {[Measures].[Average Price]} ON COLUMNS,
Filter({[Product Names].MEMBERS},
[Product Name].CurrentMember.Name MATCHES '(?i)apple.*' or [Product Name].CurrentMember.Name MATCHES '(?i)screen.*') ON ROWS
FROM [Price History]

Here in my code, any search is satisfied, but I want both conditions to be satisfied.

Is there any way, please help?

Andrei Luksha
  • 1,020
  • 1
  • 9
  • 13
MidhunKrishna
  • 611
  • 7
  • 18

1 Answers1

3

MDX does not have the LIKE operator, but you can use the InStrfunction to check if the check if the keywords are part of the name.

with member measures.iPhoneInName as
VBA!InStr([Product Name].CurrentMember.Name, "iPhone")

member measures.SamsungInName as
VBA!InStr([Product Name].CurrentMember.Name, "Samsung")

SELECT
NON EMPTY {[Measures].[Average Price]} 
ON COLUMNS,
[Product Names].MEMBERS
HAVING 
   measures.iPhoneInName >= 1 
   OR 
   measures.SamsungInName >= 1 
ON ROWS 
FROM [Price History]

EDIT: If you want only those members whose name end with iPhone OR Samsung, then a small addition to the code:

(
    VBA!InStr([Product Name].CurrentMember.Name) + len("Samsung") + 1
    = LEN([Product Name].CurrentMember.Name)
    AND measures.SamsungInName >= 1
)
OR 
(
    VBA!InStr([Product Name].CurrentMember.Name) + len("iPhone") + 1
    = LEN([Product Name].CurrentMember.Name)
    AND measures.iPhoneInName >= 1
)

Mondrian version

    with member measures.iPhoneInName as
    VBA!InStr([Product Name].CurrentMember.Name, "iPhone")

    member measures.SamsungInName as
    VBA!InStr([Product Name].CurrentMember.Name, "Samsung")

    SELECT
    NON EMPTY {[Measures].[Average Price]} 
    ON COLUMNS,
    FILTER
    (
     [Product Names].MEMBERS,
        (
            VBA!InStr([Product Name].CurrentMember.Name) + len("Samsung") + 1
            = LEN([Product Name].CurrentMember.Name)
            AND measures.SamsungInName >= 1
        )
        OR 
        (
            VBA!InStr([Product Name].CurrentMember.Name) + len("iPhone") + 1
            = LEN([Product Name].CurrentMember.Name)
            AND measures.iPhoneInName >= 1
        )
    )
    ON ROWS 
    FROM [Price History]
SouravA
  • 5,147
  • 2
  • 24
  • 49
  • hi thanks for the reply, i tried your code but it says: MondrianException: Mondrian Error:Syntax error at line 11, column 1, token 'HAVING' – MidhunKrishna Dec 09 '15 at 13:51
  • So `MONDRIAN`doesn't have `HAVING` clause! Wait, will add one more flavor. – SouravA Dec 09 '15 at 14:01
  • @MidhunKrishna - added. Please check. – SouravA Dec 09 '15 at 14:04
  • @SouravA- Hi, thanks for the edit, i tried again but it still says MondrianException: Mondrian Error:No function matches signature 'InStr()'. what is the error here, didn’t understand. – MidhunKrishna Dec 10 '15 at 05:26