Making SSRS multiselect dropdowns searchable combobox


SSRS Multiselect dropdowns are pretty powerful. Just to add on to the power of it, i felt it would be good to add on a searchable combobox functionality. As and when the user types in a text, the dropdown filters the value and displays only the required results in the dropdown.

Also to ensure that the original functionality is not being affected i have tried using a jQuery code that you can simply port in. Just ensure the element IDs are fine.

Below is the code snippet for this functionality. Please copy them directly onto your webpage HTML section towards the end and verify the element IDs once again.

Ensure that you have jQuery loaded on your webpage.

<script type=”text/javascript” language=”javascript”>
/* Binding functions */
if ($(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).html() != null) { //Check if report dropdown is loaded on the page
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).removeAttr(“readOnly”); //Remove the readonly property or else user’s keypress wont work
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).bind(“keyup”, Searchable);//Search for each key press
$(“#ParameterTable_ctl00_cphMain_rpvMain_ctl00“).bind(“click”,ClearValue);//SSRS parameter area click to reset the dropdown filter
$(“#ParameterTable_ctl00_cphMain_rpvMain_ctl00“).bind(“focusin”,ClearValue);//SSRS parameter area focusin to reset the dropdown filter. This is to track user’s tab key press
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_ddDropDownButton“).bind(“click”,ClearValue);// Dropdown arrow click to reset the dropdown filter
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).click(SetFocus);//When the user click on the multiselect dropdown, the cursor should be in place for him to make text entry.
function SetFocus() { $(this).focus(); }
function ClearValue(){
RestoreValues();
}
function RestoreValues()
{
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_divDropDown table tr td”).each(function() {
$(this).parents(“tr”).show();
});
}
function Searchable(e) {
if(e.keyCode ==8 || e.keyCode==46) //Check for backspace or delete
RestoreValues();
var searchText = $(“#ctl00_cphMain_rpvMain_ctl00_ctl05_txtValue“).val(); //extract the textbox value (full string the user enters)
searchText = searchText.split(‘ ‘).join(” “);//Replace any special space charectors with normal space charector. It might appear weird, but i found the space in SSRS dropdown values to be different from normal space and my regular expression failed. Hence this code is added.
if(searchText != ”)
{
var reg_Ptrn = new RegExp(“^”+ searchText, “i”);
//Loop through all the elements in the dropdown and show or hide each of them based on the user key inputs.
$(“#ctl00_cphMain_rpvMain_ctl00_ctl05_divDropDown table tr td“).each(function() {
var labelText = $(this).text();
labelText = labelText.split(‘ ‘).join(” “);
if(reg_Ptrn.test(labelText))
$(this).parents(“tr”).show();
else
$(this).parents(“tr”).hide();
});
}
}
}
</script>

Known Issues:

1. Regular expression special charectors need to be handled separately. If you dropdown values have regex special charector like “[],^?$” etc.., then they will add their special meaning to the search. Although you can call it a regular expression searchable combobox 😉

But i will get back with the fix for the bug. The best option would be to keep track of their keycodes & prefix them with “\” (without double chords). ex. “[” become “\[“. Then pass it to the regex text function. It will escape these charectors and search them as normal charectors.

2. Need to remove the hardcodings of textboxes and dropdown.

3. Performance although not that bad, could be tweaked a bit more.

Hope this helps.

Feel free to leave a reply here...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s