?
Solved

Multi-Value Parameter

Posted on 2008-10-13
4
Medium Priority
?
1,433 Views
Last Modified: 2010-04-21
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

0
Comment
Question by:chaffee007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 1

Expert Comment

by:crumber
ID: 22703914
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
 

Author Comment

by:chaffee007
ID: 22704588
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
 
LVL 12

Accepted Solution

by:
jgv earned 2000 total points
ID: 22704995
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
 

Author Closing Comment

by:chaffee007
ID: 31505589
I figured it out just as I saw your post, but take the points for your time.  Thanks.
0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question