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

MDX - Use formatting to change the Null values into other Characters

 

Have you ever used the If statement to convert the null into some special character mainly for Excel based reports? If then there is no need for it, you can easily achieve this via formatting

Let us first do this in a wrong way

In the below code I am checking for null values and if it is null then replace it with “–” . It works perfectly fine and it can be directly linked with excel reports and handed over to users

with member measures.[Reseller Sales Amount Formatted] as

iif( isempty([Measures].[Reseller Sales Amount])  ,"-",[Measures].[Reseller Sales Amount])

Select {[Measures].[Reseller Sales Amount], measures.[Reseller Sales Amount Formatted] }on 0

     ,[Date].[Calendar].[Date].members 

      * 

      [Geography].[Geography].[State-Province].members on 1

from [Adventure Works]

This has clearly produces the required result  as shown below,

What is the best way then?

Though this is correct in terms of results there is a simpler way of doing this with format_String as shown below

with member measures.[Reseller Sales Amount Formatted] as

[Measures].[Reseller Sales Amount], FORMAT_STRING = "$#,##.00;($#,##.00);$#,##.00;-"

Select {[Measures].[Reseller Sales Amount], measures.[Reseller Sales Amount Formatted] }on 0

     ,[Date].[Calendar].[Date].members 

      * 

      [Geography].[Geography].[State-Province].members on 1

from [Adventure Works]

So, Are we only talking about Aesthetic reasons here?

Actually no. Using the format option gives you lot of performance advantage in complex queries especially in Crosstab report.

E.g. Test the below query it takes around 30 seconds

with member measures.[Reseller Sales Amount Formatted] as

iif( isempty([Measures].[Reseller Sales Amount])  ,"-",[Measures].[Reseller Sales Amount])

Select [Geography].[Geography].[State-Province].members on 0

     ,[Date].[Calendar].[Date].members      on 1

from [Adventure Works]

where  measures.[Reseller Sales Amount Formatted

Just change it with format option, it will get executed under 5 seconds and that should convince you.

Happy Reading