Scarlett20
asked on
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
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
radcaesar
you need to set the parameter type of dxcode to string.
ASKER
The data type of parameter dxcode is already set to string.
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)ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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