Generating reports in SSRS take a lot of time, especially if the CRM is hosting huge bulk of data. Sometimes, clients would only like to see and choose a few records for the report. In this case, they create a dropdown list. However, this is still inefficient as the dropdown will first load the bulk data (say 80,000 records as an example) before you could completely select the records you want to show on the report. In addition, you have to scroll down all the way to the bottom, one by one, to see the record you’re looking for. Worst part is the user is likely to experience lag due to massive request and loading of records.
As this provides UI and performance issues, there is a need to filter the dropdown list. When a user types a sample word, it should provide short number of filtered results in the dropdown to make a seamless user experience in searching and establishing reports. Here’s how to do it.
- Given that there is a parameter for dropdown list of all Contact records, add another parameter and name it FilteredContact. (Note: Name should contain no spaces. Only prompt can accept spaces as this is the one being displayed on the report).
- Check the Allow the null value option and choose Visible option under visibility parameter.
- Leave the other tabs untouched and click OK.
- Go and right click the Contact Dataset and click Dataset Properties.
Approach 1: OOTB SSRS Filter functionality
- Go to Filter Click Add button.
- Add fullname field in the Expression. Set the Like in the expression for the wildcard. Click the fx button for the value to set custom value.
- On the expression value, append and prepend the character “*” to the FilteredContact. This serves as a wildcard that will search for any character occurrences before and after the parameter. Click OK button.
- Please take note that the parameter should be in order to avoid errors. The filter parameter should come first before the parameter that populates all the contact record. SSRS executes the parameter in sequence based on priority. Since the list of contacts is dependent to the filter parameter, it should go after the filtered contact.
Approach 2: Filter by Query (Fetch XML)
- Click Parameters. Click Add button.
- Set the parameter name to @FilteredContact and select the Filtered Contact as parameter value. Click the fx button for the value to set custom value.
- On the expression value, append and prepend the character “%” to the FilteredContact This serves as a wildcard that will search for any character occurrences before and after the parameter. Click OK button.
- Add fullname condition to the Fetch XML query and set the assigned @FilteredContact to the value.
- Set value to the filtered contact parameter. The active contacts should now have short list of all contacts with the same occurrences with the filtered value.
- If the filtered contact is blank or null, the active contact should show all the contact records.