Wild Card Serach in MDX whiel querying cube

Question

Dear All,

 I want to put the user search function in  parameter section where our UI is connencted to SSAS cube. This couls be wildcard search as well.  I am aware that we can  search for a substring in a MDX query. But do we have an option to search for individual characters like the once we perform in SQL queries?

For example if my product names are :  MMA11, MMB21,MMC11, MMD51 and if my users want to search all of them with single input parameters like MM??1 where '??' are wild card replacement for single characters. This will give me all the products with the names 'MMxx1' without any other products with extra characters.

Is this is possible to make?

Thanks in advance,

Answer 1

Hello,

For this you can use the InStr or Filter function in MDX, see Wild Card in MDX Queries

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Hello,

Thanks for your quick reply. I know this is possible.

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

To search a specific character/string

But I want know if this possible to search with specific characters. Something like this:

InStr([Dim Grp].[Dim Name].MEMBER_NAME, "x?x" <> 0

Where this has to return anything with the string 'xxx' like the first one but dynamically replaces any charactes for '?'. In other words the character '?' itself is a wildcard search.Is this is possible?

Regards,

Answer 3

Hi PXPRXR,

You can still use the filter combined with left() and right() function to achieve this goal.

For example: get customer name which start with A and end with g from Adventure Works cube.

SELECT {} ON COLUMNS
    , FILTER([Customer].[Customer].[Customer]
        , Left([Customer].[Customer].MEMBER_NAME, 1) = "A" and Right([Customer].[Customer].MEMBER_NAME, 1) = "g") ON ROWS
FROM [Adventure Works];

Regards,

Pirlo Zhang 

---

MSDN Community Support&lt;br/&gt; Please remember to click &amp;quot;Mark as Answer&amp;quot; the responses that resolved your issue, and to click &amp;quot;Unmark as Answer&amp;quot; if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact &lt;a href=&quot;mailto:[e-mail]&quot;&gt;[e-mail]&lt;/a&gt;.