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"



2 comments:

  1. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training

    ReplyDelete