Filter a Parameter with Long List of Values Using Type Ahead

I have a customer who has huge numbers of SKUs. Their reporting is using an SSAS cube as the data source. When filtering an SSRS report the users have to scroll through many many records to get to the SKU they want.   There is a type-ahead feature built into SSRS but you have to type superfast and hit it exactly in order to make it work.  I came across this great solution, and would like to blog the step-by-step with a cube as datasource. 

The solution is basically to add an additional text parameter which will prefilter the available values in the large parameter list.  This way the user doesn’t have to scroll through the long list to find the item they want, but can produce a shorter picklist, or even reduce the list down to one value if they type the exact SKU in.  They still have to check the box on the item they want, even if they’ve typed it all in, but it is still a good option when dealing with huge lists.

1. Alter your existing Available Values query to include the new pre-filter parameter.

When you created the original parameter in the report BIDS automatically created the dataset for the Available Values in the parameter.

You need to alter this query to use an additional pre-filter text based parameter.

1. Show hidden datasets.  Right click on your data source and select Show Hidden Datasets

2. Find out which dataset is being used by your parameter in the Available Values.  Right click on the parameter and select Parameter Properties.  Select the Available Values tab and find the name of the query being used.

3. Open up the dataset query.  Right click on the dataset you just identified, and select Query.

4. Add a parameter to the query.Click on the parameter button in Query Designer.

Type in the name of the new Parameter you would like to add for the type-ahead prefilter, and put in a Default value.  Don’t worry, you can change the default value in your Parameter Properties later if you wish.

Select OK.

5. Add the highlighted additional line of MDX to your query. Place the line right before the very last FROM [nameofcube], and add a close bracket at the end of the query.  This MDX will further filter your available values based on what is typed into the prefilter you are creating.

WITH MEMBER [Measures].[ParameterCaption] AS [Product].[Style Code].CURRENTMEMBER.MEMBER_CAPTION MEMBER [Measures].[ParameterValue]
AS [Product].[Style Code].CURRENTMEMBER.UNIQUENAME MEMBER [Measures].[ParameterLevel] AS [Product].[Style Code].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Product].[Style Code].ALLMEMBERS ON ROWS
FROM ( SELECT ( STRTOSET(@ProductDivisionCode, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT (IIF(LEN(@ProductStyleCode_filter) =0, ([[Product].[Style Code].ALLMEMBERS), (FILTER([Product].[Style Code].ALLMEMBERS, INSTR ([Product].[Style Code].CURRENTMEMBER.MEMBER_CAPTION , @ProductStyleCode_filter) > 0)))) ON COLUMNS
FROM [Sales and Inventory])     )  

Click OK to save your Query.

BIDS has created your new paramater for you.  Now you can edit the default value of your parameter if you like, and then test your report. All should be working as expected.

Thanks to Thomas Puch and Ella Maschiach for their blogs on the subject.