How do I separate elements of an SSRS 2008 Multiple User Selection?

Good morning Experts!

Once again I fear I may have outsmarted myself in the interest of providing more user-friendly reports.  (SSRS 2008 R2)

I populate a user-selection list from SQL like this:

SELECT sysCode || ' - ' || codeDescription AS CodeSelection
FROM sysCodes
ORDER BY sysCode

Open in new window


...quite naturally, the selection list (for the user) is a neatly laid out set of selection that look something like this:

BR549 - Portable Light Retriever
CC212 - West Virginia Nothing Grinder
FB900 - Left-Handed Metric Adjustable Wrench
VS829 - Radial Spectrum Weeble Balancer

Open in new window


...and the user has the option to select multiple list entries, if they desire.  That all works just spiffily, and the users like the added descriptions to go with the codes.

But that's where everything breaks.  Normally, all I need to do is add a clause:
WHERE whasit IN (:UserSelectedStuff)

Open in new window

in my normal SQL code and it matches the [whatsits] to the user-selected items; but not any more.  I realize that the selections are an array, but need to somehow "strip off" everything past the the "-" (which I added in the SQL statement).  That's the rub - if I can just get rid of the "-" and all of the information to the right of it, the list will re-match what appears in the [whatsit] column of the table.

How might I achieve this feat?  Or am I being "too nice" to my users by providing additionally clarifying information with the code list they normally would have to guess at?  I'd really like to make this work, because other than the fact that it doesn't, my test lab people are really a fan.

Thanks in advance,

The Lurking LongFist
LVL 1
LongFistAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
This is why the 'Available Values' section of the parameter property box has both 'Value field' and 'Label field'. You can separate what is shown to the user, and what is used as the value for the parameter. You can, for example, display the name of a customer in the list, but use the ID of the customer as the parameter value. In your case you need to change the query for the parameter to:
SELECT sysCode, sysCode || ' - ' || codeDescription AS CodeSelection
FROM sysCodes
ORDER BY sysCode

Open in new window

Then in the parameter properties you change the 'Value field' from CodeSelection to sysCode.

Looks like you are using Oracle. The solution might not work when you select multiple values. I think there is a solution for that on this page:
{edit ValentinoV: link to competing site removed as per the Terms}
http://arcanecode.com/2008/10/20/sql-server-reporting-services-oracle-and-multivalued-parameters/
I don't have an Oracle database running so I can't test this.
0
 
Nico BontenbalCommented:
You could try and search Google for:
"in many of our reports we are converting we pass multiple values in parameters, however i cannot get this working in SSRS pointing to the Oracle datasource"
(including the quotes). You might find an interesting link in that search result :-)
0
 
LongFistAuthor Commented:
Works fine.  Does exactly what I need it to - what the users need it to - so this is perfect.  Thank you for sharing this!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.