Multiple Value Parameter not working

Posted on 2011-04-29
Medium Priority
Last Modified: 2012-05-11
In SSRS 2005 I have created a report having two two datasets.  

Nbr1 - get patient names and their diagnostic codes - but only for diag codes specified in @dxcodes.
select a.patientName, b.dxcode
from patientTbl a left outer join patdiagTbl b on a.patkey=b.patkey
where b.dxcode in (@dxcodes)

Nbr2: - get all dxcodes to populate param drop down
select distinct dxcode from patdiag order by dxcode

The dropdown seems to work fine, I am able to choose multiple dxcodes.  But the result set comes back only with the very first dxcode I selected.

dxcodes contains alpha values (some values are 600.1, 600.2, V25.1, V25.5)

It is interesting that SSRS doesn't place quotes around these values, only a comma between them.  Could the decimal be my problem??

Thanks in advance for your help!  

(this is the first time I've tried to use multi value params)

This is the first time I've tried to create a multi value param, so please bea
Question by:Scarlett20

Expert Comment

ID: 35493014
you need to set the parameter type of dxcode to string.

Author Comment

ID: 35493040
The data type of parameter dxcode is already set to string.
LVL 41

Expert Comment

ID: 35493459
try dynamic sql.
declare @sql varchar(500)
select @sql = 'select a.patientName, b.dxcode 
from patientTbl a left outer join patdiagTbl b on a.patkey=b.patkey 
where b.dxcode in (' + @dxcodes + ')'

Open in new window

LVL 22

Accepted Solution

Nico Bontenbal earned 2000 total points
ID: 35496299
Strange because what you did should work. No need to use dynamic sql. I've attached an example that demonstrates it can work. You need to rename the .xml file to .rdl because EE doesn't allow the upload of .rdl files.
The report creates it's own data, no need for any specific tables. But you need to change the data source of course.

If this sample doesn't work for you the cause is probably the configuration. Check to see if you have the latest service pack.

If it does work you can replace my queries with yours (one by one). If it then stops working the problem is probably in your data. I don't know if you gave the exact queries in your data but in one query you use patdiagTbl and in the other patdiag. Maybe the codes in both sources are not exactly the same (one might include spaces for example).


Author Closing Comment

ID: 35508082
Dear Nicobo - did exactly as you recommended.

I loaded your example, got it to work, then overlaid your queries with mine - and it works fine.  So I went back to my original report - and rechecked everything - still could not find what I had done incorrectly.

At any rate - I now having a working report that can use multiple param values.

Thank you very much.  Scarlett20

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 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