Solved

Multi-Value Parameter

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now