Solved

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

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 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

16 Experts available now in Live!

Get 1:1 Help Now