Multivalue parameter that allows input of a value that is stored in multiple fields

I have a report that was converted from Crystal.  It allows the user to input multiples of any of these values (Client, Matter ID, MasterName).
Records in the database would look like this:

Client        Matter ID           MasterName
123            123-001             Smith
123            123-002             Smith
234            234-001             Nelson
345            345-001             Landers

When the client is prompted, they could enter '123' and return the two records for smith.  Or they could enter 'Smith' and return those two same records.  They could enter 'Smith' and '234-001' and return 3 records.

The functions that came over from Crystal looks like this but neither has any affect when I input values for the parameter and I can't tell where or how they are being used anywhere. I assume they should be used as a filter on the dataset somehow:

Public Function CRFdisplaymatterids(Fields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields) As String
      Seti(1)
      Settx("")
      If Not IsNothing(Report.Parameters!ClientIdMatterId.Value) Then
            displaymatterids_0_Loop(Fields)
      End If
      If Not IsNothing(Report.Parameters!ClientIdMatterId.Value) Then
            Settx(Left(Gettx, Len(Gettx) - 2))
      End If
      Return "Master/Client/Matter IDs: " & Iif(Gettx = "", "All", Gettx)
End Function

Public Function displaymatterids_0_Loop(ByRef Fields As Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Fields) As String
      Seti(1)
      While Geti <= Report.Parameters!ClientIdMatterId.Count
            Addtx(Mid(Report.Parameters!ClientIdMatterId.Value, Geti, 1) & ", ")
            Addi(1)
      End While
      Return "" ' *** Check this return value
End Function
nolasaintsgalAsked:
Who is Participating?
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.

Russell FoxDatabase DeveloperCommented:
It can be done, but you need to tweak the underlying query:

Create your three parameters
Set all to allow NULL values
Set the default value for all to NULL
Include the parameters in your query like below, with "([field] = @param OR @param IS NULL)
The parentheses around each param ARE important here: AND (this or that) AND (this or that)...
SELECT Client, Matter ID, MasterName
FROM MYTABLE
WHERE (Client = @Client OR @Cient IS NULL)
AND (Matter_ID = @MatterID OR @MatterID IS NULL)
AND (MasterName = @MasterName OR @MasterName IS NULL)

Open in new window

As a design note, rather than letting them type in values for the parameters you can use three additional queries to pull in all distinct active values, and then set each parameter's data source to its respective query. That will give them a dropdown from which to select instead of typing. Of course, that only works well if there's a reasonable number of each param, otherwise it may crash trying to load a million client names.
0
PortletPaulfreelancerCommented:
When the client is prompted, they could enter '123' and return the two records for smith.  Or they could enter 'Smith' and return those two same records.  They could enter 'Smith' and '234-001' and return 3 records.
That last item (in bold) complicates things quite a bit.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, what you want is to search a given text in any column rather that a single one, correct?
0
mlmccCommented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for Russell_Fox's comment #a41248484

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
PortletPaulfreelancerCommented:
Not sure the selected answer does not solve the question as posed. It is a good solid conventional approach, but it does not solve this question.

The challenge is that the where clause cannot assume AND between predicates, as I mentioned some time ago, the last part of this quote (in bold) complicates the requirements
When the client is prompted, they could enter '123' and return the two records for smith.  Or they could enter 'Smith' and return those two same records.  They could enter 'Smith' and '234-001' and return 3 records.

If all the predicates are joined by AND
then if a user entered 'Smith' plus entered  '234-001'  they would get NO RESULTS
because of the ANDs

so the answer requires ORs I believe

WHERE (Client = @Client OR @Cient IS NULL)
OR (Matter_ID = @MatterID OR @MatterID IS NULL)
OR (MasterName = @MasterName OR @MasterName IS NULL)
0

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.