Link to home
Start Free TrialLog in
Avatar of nolasaintsgal
nolasaintsgal

asked on

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
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

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.
Avatar of PortletPaul
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.
So, what you want is to search a given text in any column rather that a single one, correct?
Avatar of Mike McCracken
Mike McCracken

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.
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial