Multiple Value Parameter not working

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
Who is Participating?
Nico BontenbalConnect With a Mentor Commented:
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).

you need to set the parameter type of dxcode to string.
Scarlett20Author Commented:
The data type of parameter dxcode is already set to string.
SharathData EngineerCommented:
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

Scarlett20Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.