Options for filtering parameter values in a SSRS drop down

I was working with a client who had a problem with their SSRS reports. Some reports had a parameter with a drop down that had thousands of values. This parameter was for Item SKU and this client had LOTS of parts for their products. Scrolling through the large drop down list was slow and cumbersome. The client wanted type ahead / autocomplete functionality to allow the user to type in part or all the Item SKU to filter down the values in the drop down list to a smaller list of values.

The three options I came up with to address this issue were:

Option 1: In SSRS 2008 users can now type the first few letters of their parameter which will position the highlighted value to that value. The problem is that this must be done EXTREMELY fast. If you not very quick with the keyboard this will not work. The delay between the first key stroke and the second key stroke will cause SSRS to think the second key stroke is the first keystroke and therefor position the highlighted value on a new parameter.

Advantages: Already available in the SSRS report viewers.
Disadvantage: Not usable for users who type slower than 240 words per minute.

Option 2: Add an optional parameter value called “ItemSKU filter” to pre filter the Item SKU drop down. The default behavior when a user enters the report would be blank for Item SKU filter and show the massive list of values for Item SKU. If the user enters a value in the ItemSKU filter prompt, the string entered will be used to filter what is presented in the Item SKU drop down box. If the user enters the entire Item SKU then only one value will be presented in the drop down box. The user will still have to make a selection in the Item SKU drop down, which adds one more mouse click. This technique was described by Ella Maschiach in her blog.
Below shows the default behavior when the user enters the report. Initially, Item SKU filter is blank and all Item SKUs are shown.

If the user enters in a value in the ItemSKU filter prompt, the available values in the Item SKU are filtered. A user can type in a complete Item SKU to limit the list to one product.

The one difference from Ella Maschiach’s example was her example used SQL against a database. My client was using MDX against a cube. The query that populated the available values for Item SKU had to be modified as below.

SELECT NON EMPTY { } ON COLUMNS,
IIF(LEN(@ItemSkuFilter) =0,
([Product].[Item SKU].[Item SKU].ALLMEMBERS),
(FILTER([Product].[Item SKU].ALLMEMBERS,
INSTR ([Product].[Item SKU].CURRENTMEMBER.MEMBER_CAPTION , @ItemSkuFilter) > 0))) ON ROWS
FROM [ProductCube]

The trick was to use replicate a SQL LIKE operator in MDX. Jason Thomas had an example of this posed here. I could have also used the LIKE function found in ASSP – Analysis Services Stored Procedure Project. This project found on Codeplex.

Advantages: Easy to implement with in SSRS. Each report that would need this can be modified under an hour.
Disadvantage: Adds an additional mouse click if a user wants to enter a specific Item SKU which is not really much of a time saver.

Option 3: Consider creating autocomplete functionality using Ajax/jscript. This would have to be implemented in a .Net application or a custom AJAX report viewer before the report is executed in SSRS. The application would have to handle populating all parameter values and then calling the report in SSRS. The upside is that our user experience for our massive drop down list can be greatly improved. This type of autocomplete functionality is similar to what you see in Google or Bing search. Perhaps Microsoft can add this kind of functionality in a future SSRS release. Ben Scheirman had a blog posting on solution.

Advantages: Best of breed approach
Disadvantages: Higher cost to implement.