0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Alex Kwitny
  • 11,211
  • 2
  • 49
  • 71
  • Try getting the unique values from your dataset using distinct . Don't you have any hierarchy for the item .Example items are categorized into some categories or sub categories ?? – praveen Aug 08 '12 at 01:07

3 Answers3

0

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.

Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • Well we have categories, but they often one one item from one category, and another item from another category, etc. I'm trying to figure out how to parse a string that they enter into something I can use in SQL – Alex Kwitny Aug 08 '12 at 17:08
0

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.

Wesley Marshall
  • 434
  • 4
  • 16
0

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

LIKE '%'+@YourParameterName+'%'
bonCodigo
  • 14,268
  • 1
  • 48
  • 91
PKC
  • 1
  • 2