Filter dataset based on multivalue parameter

I have a multivalue parameter for 'area' that consists of five values:

Collections
Collections/Real Estate
Collections/Estates
Corporate
Corporate/By-laws

If the user chooses 'Collections' and 'Corporate/By-laws' then the dataset should return records where 'area' like 'Collections%' and records where area= 'Corporate/By-laws'. The idea is for a user not to have to pick every value that begins with Collections because in reality they have a bunch of them, but be able to get them all if they choose 'Collections.' They still however will occasionally want to see only a specific value so might check just 'Corporate/By-laws'.

The dataset is created from a stored procedure that I'd rather not have to edit and if I did have to edit, then I'd have to figure out how to pass it a multivalue parameter and then the sql that would do what the filter should do.

Any insight would be appreciated. thanks.

The only way I can think of doing this without changing the stored procedure is by writing a custom function to do the comparison. If you never used custom code before you can start here:
https://msdn.microsoft.com/en-us/library/ms156028.aspx
and here:
https://technet.microsoft.com/en-us/library/ms155798(SQL.100).aspx

Your custom code could be like this:

Public Function ShowParameterValues(ByVal parameter as Parameter, ByVal area as String) as Boolean
   For i as integer = 0 to parameter.Count-1
      if area like CStr(parameter.Value(i)) & "*" then
         Return true
       end if
   Next
   Return false
End Function

Open in new window

This procedure accepts a multi value parameter and a string value (the Area field from the dataset). It then checks if any of the values from the parameter match with the Area. If it does it returns true, else it returns false. Then you can set up a filter on the Dataset like this:
Expression: =code.ShowParameterValues(Parameters!Area,Fields!Area.Value)
(I've used Area as name of the parameter and the field, you need to change these to the names used in your report).
Type: Boolean
Operator: =
Value: True

See the attached report to see this in action. You need to change the Data Source on the report and point it to your own SQL Server in order to run the example. You might need to change the code a bit, it's not perfect, just to demonstrate the technique. I haven't tested it for empty (null) values for the Area field for example.

If you want to do it by changing the stored procedure you can start by reading this article:
http://www.experts-exchange.com/articles/2002/Reporting-On-Data-From-Stored-Procedures-part-2.html
But you have to make a small change to the technique using 'like' (in a join) and not 'in'.
Multivalue.rdl

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 22

Nico BontenbalCommented: 2015-08-04

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Filter dataset based on multivalue parameter

Who is Participating?