We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Multiple Value Parameter not working

Medium Priority
184 Views
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
Comment
Watch Question

you need to set the parameter type of dxcode to string.

Author

Commented:
The data type of parameter dxcode is already set to string.
SharathData Engineer
CERTIFIED EXPERT

Commented:
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 + ')'
exec(@sql)

Open in new window

CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.