SQL Server 2008 SSRS Reports to allow multi select value from a dataset, but dataset is too large to scroll. Possible to filter like excel?

Question

New to SSRS here. I have a report I'm writing to display all of the sales lines where a line is found containing a list of specific items.

The parameter @Items gets its data from a dataset which is pretty simple. The issue is, we have 100,000 items. Users are usually only searching for a few items at a time.

This currently gives them a drop down with all 100k items. They have to scroll up/down and check the box for each item.

select ITEMID,
    ItemId + ' - ' + ITEMNAME as 'ItemId_ItemName'
    from INVENTTABLE
    order by ITEMID asc

In Excel, if you filter by a field, you can start typing the item, then check the box, clear the filter, type a different item, etc. and then you have a 100k list with the three or so items checked.

Any idea how I can accomplish this?

Answer 1

The short answer is no, you can't do what you describe in Reporting Services.

If you have some other values (e.g. Category) that could be used to filter the items as per praveen's comment, then you could using "cascading parameters" so that the user selects a Category which then filters the list of available Items.

Alternatively, if your users know the Item name or code then you could just let them type in an item name or code as the parameter instead of providing a drop down list.

Answer 2

I agree with the previous answer suggesting that you use cascading parameters. If users need to select items from multiple categories, then have the parent parameter be a multi-select showing the categories. You can then filter the available values in the child parameter to include only items in any of the selected categories.

Answer 3

In your parameter properties, in the Available Values, you might try the following:

LIKE '%'+@YourParameterName+'%'