Solved

Multi-Value Parameter

Posted on 2008-10-13
4
1,429 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 500 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
section a string 5 52
SQL Server 2014 Express vs Full - Service Pack question 5 44
Remove () 9 32
Rewriting a simple query 2 21
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

738 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