Filter dataset based on multivalue parameter

nolasaintsgal
nolasaintsgal used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial