Multi-Value Parameter

I am trying to use a multi-valued list of sales reps as a parameter using report designer.  If I select the multivalue checkbox it seems as though I can only use the Non-Queried option.  If I have to use this option, I would have to list each sales rep manually instead of retrieving the sales reps names from the "FullName" column values.  Is there away to accomplish this?  I tried using "IN" for the parameter also with no luck
SELECT   FSU.fullname, FR.name
from filteredsystemuser FSU JOIN
FilteredSystemUserRoles FSUR ON FSU.systemuserid = FSUR.systemuserid JOIN
FilteredRole  FR ON FSUR.roleid = FR.roleid
 
Where fullname = @FullName

Open in new window

chaffee007Asked:
Who is Participating?
 
jgvConnect With a Mentor Commented:
I thought I understood what you were asking but your query threw me off. I'll give you a quick run down on how to setup a multi value parameter that will be used to filter the main query. You'll have to follow up if this is not what you are looking for.

If you have not already done this, create a new dataset that will pull the names of the sales reps. You don't include a parameter in this query. ie: SELECT FullName FROM MyRepTable

In your main query, include a where statement that will filter the results by the sales reps selected by person running the report. ie: SELECT * FROM MyTable WHERE SalesRepName IN (@RepName)

In the report parameters window you should see the parameter that was entered in the main query. Select the "Mult-value" option and under the "Available values" section select "From query". Now select the dataset that you created to retrieve the sales rep names. Set the Value field to the field that contains the values that you want to filter on in the main query and select a Label field which is what the user will see in the drop down box. You can use the same field for value and label.
0
 
crumberCommented:
My crystal report contains a parameter that is a list of values required to filter the result set.
The parameter created in the crystal report has the allow multiple values property set as well as discreet values.
I populate a delimited (^) string of the filtered values (my own parameter screen) and end up with a list of those values (i.e. value1^value2^value3...).
I pass them through the following function in order to create the multi-valued report parameter:

Private Sub setMultipleParams(ByRef oRpt As ReportDocument, ByVal sParamValue As String, ByVal sParamName As String)
Dim dv As ParameterDiscreteValue
Dim fds As ParameterFieldDefinitions
Dim fl As ParameterFieldDefinition
Dim pvs As ParameterValues
Dim aSplitItems() As String
fds = oRpt.DataDefinition.ParameterFields
aSplitItems = Split(sParamValue, "^")
For x = 0 To UBound(aSplitItems)
fl = fds.Item(sParamName)
pvs = fl.CurrentValues
dv = New ParameterDiscreteValue
dv.Value = aSplitItems(x)
pvs.Add(dv)
fl.ApplyCurrentValues(pvs)
Next
End Sub

This results in a parameter value that I can then attach to the report object and process the report.

setMultipleParams(oRpt, gsRP(1), "p_resnamelist")
Where oRpt is the report object.
Hope that is what you are looking for.
0
 
chaffee007Author Commented:
Whoa.  That is a bit over my head.  Thanks for the input, but I am looking for something a bit geared more toword SSRS.
0
 
chaffee007Author Commented:
I figured it out just as I saw your post, but take the points for your time.  Thanks.
0
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.

All Courses

From novice to tech pro — start learning today.