Solved

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

Posted on 2013-10-22
3
315 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

896 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

15 Experts available now in Live!

Get 1:1 Help Now