none
Multi-value parameter problem

    Question

  • I set up two parameters that allow multple values.  The first parameter is populated by sql.  The second parameter is populated by sql based on the first parameters answer.  If I select one item from the first parameter and one from the second the report works.  If I select two items from the first parameter I get an error in the sql for the second parameter.  The logic on the second paramter sql is: where @b = ' ' or a in (@b), and I get the following error:  An expression of non-boolean type specified in a context where a condition is expected, near ','.

    The parameters are set up for text, allow space and multiple.

    Wednesday, June 19, 2013 2:36 AM

Answers

  • Hi Scott,

    your query is failing because where condition @b = ''. This is not a valid condition in case of multiselect parameter.

    For example if you select value two value "ABC" and "DEF".

    you query will be formed as 

    SELECT * FROM <<TABLENAME>> WHERE ('ABC','DEF') = '' or a in ('ABC','DEF')

    so the condition ('ABC','DEF') = '' is totally wrong, you can't have it. if you are filter based on another parameter this condition is not required.

    It should simply be:

    SELECT * FROM <<TABLENAME>> WHERE a in (@Parameter1)

    which will turn out as (If you are really curious you can capture the query in SQL Profiler).

    SELECT * FROM <<TABLENAME>> WHERE  a in ('ABC','DEF')

    Hope this clarifies your doubt.


    Regards Harsh


    • Edited by Harsh Kumar Wednesday, June 19, 2013 6:24 AM fixed the code
    • Proposed as answer by Valentino VrankenMVP Wednesday, June 19, 2013 6:33 AM
    • Marked as answer by ScottHD Wednesday, June 19, 2013 6:55 AM
    Wednesday, June 19, 2013 6:24 AM
  • Hi Scott,

    yup if you want to only have time(Without date), then is good to have a string datatype(number of the datatype is limited in ssrs for parameter list). Only advantage you get with datetime is you get a calendar control while rendering the report.

    Regards

    Harsh


    Regards Harsh

    • Marked as answer by ScottHD Wednesday, June 19, 2013 1:48 PM
    Wednesday, June 19, 2013 7:12 AM

All replies

  • is @b your first parameter?

    can you post the whole sql statement because its hard to follow, and i mean the sql that populates your second parameter

    Wednesday, June 19, 2013 3:20 AM
  • Hi,

    Check whether anything can be done with LookupSet function

    - http://msdn.microsoft.com/en-us/library/ee240819.aspx


    sathya --------- Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Wednesday, June 19, 2013 3:29 AM
  • Hi Scott,

    your query is failing because where condition @b = ''. This is not a valid condition in case of multiselect parameter.

    For example if you select value two value "ABC" and "DEF".

    you query will be formed as 

    SELECT * FROM <<TABLENAME>> WHERE ('ABC','DEF') = '' or a in ('ABC','DEF')

    so the condition ('ABC','DEF') = '' is totally wrong, you can't have it. if you are filter based on another parameter this condition is not required.

    It should simply be:

    SELECT * FROM <<TABLENAME>> WHERE a in (@Parameter1)

    which will turn out as (If you are really curious you can capture the query in SQL Profiler).

    SELECT * FROM <<TABLENAME>> WHERE  a in ('ABC','DEF')

    Hope this clarifies your doubt.


    Regards Harsh


    • Edited by Harsh Kumar Wednesday, June 19, 2013 6:24 AM fixed the code
    • Proposed as answer by Valentino VrankenMVP Wednesday, June 19, 2013 6:33 AM
    • Marked as answer by ScottHD Wednesday, June 19, 2013 6:55 AM
    Wednesday, June 19, 2013 6:24 AM
  • Thanks Harsh.  I removed the test for ' ' and all is wroking well.

    Seeing as you got this one right.  I want to enter a time parameter only but the paramter option is date/time.  Do I have to set up as a string and convert it to a time?

    Wednesday, June 19, 2013 6:57 AM
  • Hi Scott,

    yup if you want to only have time(Without date), then is good to have a string datatype(number of the datatype is limited in ssrs for parameter list). Only advantage you get with datetime is you get a calendar control while rendering the report.

    Regards

    Harsh


    Regards Harsh

    • Marked as answer by ScottHD Wednesday, June 19, 2013 1:48 PM
    Wednesday, June 19, 2013 7:12 AM