MDX Wildcard search in parameters dropdown -- SSRS

 

Recently I got a request from my insurance client to filter the SSRS MDX report based on the Policy holder, one choice is to give them the complete list but as we have more than 5,000 policy holders(that is only commercial insurance), it is really impossible for them to choose even with alphabetical ordered list

As the Google search has become the norm, we want to implement wildcard dropdown boxes where user can type in the partial name of the policy holder and then choose one/multiple items. Unfortunately SSRS dropdown parameter list doesn't support the wild card search. So we implemented it via cascaded parameters.

To mimic the problem let us search the Employee dimension in the Adventureworks cube

let us first implement the wild card search in MDX.

There is no direct equivalent of SQL LIKE operator is available in MDX but we can use one of the below methods to achieve wildcard search

• Using Custom library .
• Check the below link . You can use the LIkeMember function
• http://asstoredprocedures.codeplex.com/
• Other easy method is to use VBA instr function to search the member value and filter the dimension list

So if i want to filter all the Employees whose first/middle/last name is David then the query would like below

  SELECT  { } ON COLUMNS
       ,  FILTER(
              [Employee].[Employees].members,
              vbamdx!INSTR([Employee].[Employees].CURRENTMEMBER.Name,'David',1 >= 1 )
       )    on rows
 FROM [Adventure Works]

The above query search for David using the Instr function. if the instr function found the match then will return the position of David string which will be either 1 or greater than 1 . If there is no match found then it will return Zero. We then can use this as the logical expression in the filter function to get the subset of interested employees.

In order to use this query in SSRS we need to include member properties as shown below. Also replace static name ‘David’ with suitable parameter name

 SELECT  { } ON COLUMNS
       ,  FILTER(
              [Employee].[Employees].members,
              vbamdx!INSTR([Employee].[Employees].CURRENTMEMBER.Name,@EmplyeeName,1 >= 1 )
       )        DIMENSION PROPERTIES 
            MEMBER_CAPTION, 
            MEMBER_UNIQUE_NAME, 
            PARENT_UNIQUE_NAME, 
            LEVEL_NUMBER ON ROWS 
 FROM [Adventure Works] CELL PROPERTIES VALUE

Final solution will look like blow.

As you can see, using the cascaded parameters and MDX wildcard search, we can limit the list of employees. If you want to allow the user to select multiple employees then select the “Allow Multiple Values” in the parameter properties.

Happy Reading