Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-10-22
3
Medium Priority
?
324 Views
Last Modified: 2013-10-23
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
0
Comment
Question by:LongFist
[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
3 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 39593198
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
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39593343
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
 
LVL 1

Author Closing Comment

by:LongFist
ID: 39594108
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

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

722 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