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

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

Posted on 2013-10-22
3
317 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
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 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

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

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