Search code examples
reporting-services

SSRS Single parameter dropdown list to choose between multiple columns

I want 2 cascading parameters, the 1st needs to be a dropdown (@SearchBy) which would allow the user to choose WHICH column they want to search by: Item, Username, Location. Then a 2nd parameter would be a typed search based on the 1st parameter. I know how to get the 2nd parameter (@SearchFor), but I am struggling to figure out how to set up the dropdown list parameter for separate columns.

It would be easier to set up multiple parameters with null options but this is not what the user wants. The columns do not have distinct values so I cannot set the query to : WHERE user.name IN (@Search) OR location.num IN (@Search) OR item.type IN (@Search)

Solution

  • There may be better ways of doing this but try something like this simplified version..

    SELECT * 
FROM myTable
WHERE 
    (user.name IN (@Search) AND @SearchBy = 'user.name')
    OR (location.num IN (@Search) AND @SearchBy = 'location.num')
    OR (item.type IN (@Search) AND @SearchBy = 'item.type')