Tuesday, November 3, 2015

Sql "Like" in MDX

If you are looking for a Like keyword in MDX then you are not going to get that but if you want to write MDX which should work like "LIKE" keyword in MDX then you can achieve that by writing MDX in the following way;

Just consider an example wherein you want to show all Members from Calendar hierarchy like "March",  in that case you can write mdx in following way;

WITH
SET CalendarMembers
AS
FILTER(
      DESCENDANTS([Date].[Calendar]),
      vbamdx!INSTR([Date].[Calendar].CURRENTMEMBER.Name,'March',1 >= 1 )
)
MEMBER [Measures].[Caption] AS
    [Date].[Calendar].CURRENTMEMBER.NAME  
SELECT
{[Measures].[Caption]} ON COLUMNS
,
CalendarMembers 
ON ROWS
FROM [Admin]

Output : Above MDX will return all members with caption like "March"