Multiple Value Parameter not working

Posted on 2011-04-29
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
    LVL 9

    Expert Comment

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

    Author Comment

    The data type of parameter dxcode is already set to string.
    LVL 40

    Expert Comment

    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

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now