January 5, 2012 at 12:30 pm
I've been asked to create a report that works on multiple select concatenated parameters, and I'm not sure how to do this. I've easily been able to create the multiple select concatenated parameter with this select statement:
SELECT DISTINCT RTRIM(CompanyName) + ' ' + Convert(varchar(25),mastercompanyid) AS Company
FROM vCompanyView
then created the parameter to use available values from this query and be multiple select however, my next problem is using only the mastercompanyid selected for the parameter to another dataset?? Do I need some kind of token split on the @parameter???
January 6, 2012 at 5:09 am
When you populate your available values with a dataset, you can use two different columns as the label and the value. So you can select the label or the value when referencing the parameter.If you pass your parameter directly to your query, by default the value is used, but within an expression you can take the label and work with that as a second (hidden) parameter.
Lars
January 6, 2012 at 6:59 am
neither parameter is hidden to the user, I've already figured out how to populate the multi-select drop down for the user to see. I am working on maybe sending the array to a stored procedure since it seems that ssrs is weak on processing strings/arrays.
January 6, 2012 at 9:26 am
I think Lars is leading you in the right direction.
When you have a dataset driven parameter you can have two components to the parameter. The value the user sees in the drop down box (Label) and the value that is passed to the dataset that feeds the report (Value)
If you change your query to
SELECT DISTINCT RTRIM(CompanyName) + ' ' + Convert(varchar(25),mastercompanyid) AS Company
,mastercompanyid
FROM vCompanyView
You can use your "Company" column for the Label in the parameter, and your "mastercompanyid" column as your Value in the parameter.
January 6, 2012 at 11:57 am
The parameters are set the way they should be, the requirement is that the user will see both fields as the label when doing the multi-select.
What other reason would I have for concatenating them in the first place?
January 6, 2012 at 12:51 pm
Either we don't understand exactly what your issue is, or you are not understanding our response.
my next problem is using only the mastercompanyid selected for the parameter to another dataset??
The solution I proposed allows you to isolate the mastercompanyid from the concatenated companyname and mastercompanyid.
Perhaps you could provide a few screen shots, and some actual data so we are not guessing at exactly what the issue is.
How many user input parameters do you have? What are their names? What would you like as their input values?
January 9, 2012 at 8:19 am
my parameter was the one from the original question, which I concatenated as 1 parameter to be used as the multi-select.
Can't anybody figure this out? I'm almost finished processing this anyway, but gees guys how hard do you think this question is?
January 12, 2012 at 7:04 am
Create a DataSet (eg DataSet1) and set the query to
SELECT DISTINCT mastercompanyid,RTRIM(CompanyName) + ' ' + Convert(varchar(25),mastercompanyid) AS Company
FROM vCompanyView
Create Parameter eg ReportParameter1 and tick Allow multiple values
For Available Values
Dataset: use the dataset above
Value Field: select mastercompanyid
Label Field: select Company
For the main dataset (eg the report data)
Expression: mastercompanyid
Operator: In
Value: =Parameters!ReportParameter1.Value
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply