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
Solved

Multi-Value Parameter

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 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