Multivalue parameter with AllowNull = true is not working ...

11 posts, 0 answers
  1. Wayde
    Wayde avatar
    10 posts
    Member since:
    Dec 2011

    Posted 19 Dec 2011 Link to this post

    I have a multi-value parameter, @CountryID, that can also be set to null.  

    I have a sql database query that uses this parameter like such:

    "select * from Countries where CountryID is null or CountryID in (@CountryID)

    Running the report, everything works as expected when I set the parameter value to NULL or to a single value.  However, if I set it equal to two or more values I get the following error:

    An error has occurred while processing Table 'crosstab1': An expression of non-boolean type specified in a context where a condition is expected, near ','.

    What am I missing?  How do I configure the parameter and/or adjust the query so that it works with NULL, single values and multiple values?

    Thanks!
  2. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 19 Dec 2011 Link to this post

    Hello Wayde,

    Judging by the error, the problem is with expression used in filter/grouping/sorting of a CrossTab and not with the report parameters directly. Our suggestion is to revise these expressions and verify if they are correct in the context of a report parameter containing multiple values and allowance of null (i.e. respective IsNull checks have to be in place, see Functions).

    All the best,
    Steve
    the Telerik team

    Q3’11 of Telerik Reporting is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  3. DevCraft banner
  4. Wayde
    Wayde avatar
    10 posts
    Member since:
    Dec 2011

    Posted 20 Dec 2011 Link to this post

    First a correction of the sql statement I'm using (again, @CountryID is a multi-value parameter built from another sql datasource) :

    "select * from Countries where @CountryID is null or CountryID in (@CountryID)"

    The problem does not appear to be with null checking.  My results are:
    1. Click the NULL checkbox (thus setting the value to null) ...the above works

    2. Select ONE value ... the above sql query works

    3. Select TWO OR MORE values ... the sql query gives me the exception I specified in my original post.

    If the problem is with the query or some kind of null checking I need to do ... why is it that the first two cases succeed and only the last fails?

    Thanks - Wayde

  5. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 21 Dec 2011 Link to this post

    Hi Wayde,

    Checking for null was only one of the suggestions. Most likely you have an expression in the CrossTab that uses the CountryID report parameter and this expression is no longer valid when an array of values is used as condition.

    Greetings,
    Steve
    the Telerik team

    Q3’11 of Telerik Reporting is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  6. Wayde
    Wayde avatar
    10 posts
    Member since:
    Dec 2011

    Posted 26 May 2012 Link to this post

    Actually, the CrossTab does not use the CountryID parameter.

    I also think the query is fine.

    The problem I speculate has something to do with have a multi-value parameter with AllowNull = True.  I'm trying now to fix what is fed into the SQL statement by editing the value used depending on whether or not there are multiple values selected or not

    Right now I have this ...

    Name DbType Value
    -----------------------------------------------------------------------------------
    @CountryID Int32 =iif(Parameters.Indexes.Value.Length > 0,Parameters.Indexes.Value, null)


    This is not working.  Here is what happens:

    - If I check the Allow Null checkbox it works ... @CountryID is set to null as expected
    - If I select ONE value it works ... @Country is set to Parameters.Indexes.Value as expected
    - If I select TWO OR MORE VALUES ... it fails.  Why? I don't know

    I change my SQL to look like this:

    "select * from Countries where CountryID IN (isnull(@CountryID,CountryID))"

    The error I get when I pass in MULTIPLE VALUES is ... "The isnull function requires 2 argument(s)"

    I'm not sure what is happening here ... but it looks like when multiple values are specified the @CountryID isn't being set to something like "1,2,3" as I would expect.

    Any ideas?  I've been dealing with this forever.

    Thanks
  7. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 30 May 2012 Link to this post

    Hello Wayde,

    I've prepared a sample project that works correctly. Please find it attached and should you continue to have problems, provide us with a sample that shows the problem.

    Greetings,
    Steve
    the Telerik team

    BLOGGERS WANTED! Write a review about Telerik Reporting or the new Report Designer, post it on your blog and get a complimentary license for Telerik Reporting. We’ll even promote your blog and help bring you a few fresh readers. Yes, it’s that simple. And it’s free. Get started today >

  8. Bryan Hughes
    Bryan Hughes avatar
    130 posts
    Member since:
    Oct 2005

    Posted 04 Jun 2012 Link to this post

    I think the main problem is using length function on a Int32.
  9. Cory Seaman
    Cory Seaman avatar
    10 posts
    Member since:
    Jul 2006

    Posted 18 Mar 2013 Link to this post

    I figured out the solution to this after encountering it myself and seeing no good Telerik response.  With a multi-select, Telerik basically just dynamically replaces your single parameter with a comma-delimited list of params in the SQL, assuming that you'll always put it within a T-SQL "IN ()" function.  Your version, which is:
    • ... WHERE @CountryID IS NULL OR CountryID IN (@CountryID)
    ...ends up throwing an error because Telerik turns it into this:
    • ... WHERE @CountryID1, @CountryID2 IS NULL OR CountryID IN (@CountryID1, @CountryID2)

    To enable your multi-select parameter to be "ignored" by the query if it's NULL, the only way I've found is to use the T-SQL COALESCE() function in your query, like so:
    • ... WHERE COALESCE( @CountryID, NULL) IS NULL OR CountryID IN (@CountryID)

    Now the @CountryID can be dynamically replaced with multiple values and it evaluates as you intend...and without fear of a syntax error.

    Telerik should include this as a best practice when users want to enable NULL as an option to have a parameter "ignored" in a query's WHERE clause.

  10. Stef
    Admin
    Stef avatar
    3047 posts

    Posted 22 Mar 2013 Link to this post

    Hello Cory,

    Thank you for your input.

    First let me point that the query is not well structured, if  you expect a collection from the parameter (CountryID in (@CountryID)) the checking for null should handle collection as well - Concat, Coalesce.
    So indeed this is one possible solution on query level.
    We can suggest you to move this filtering on report level, where the expression will look as follows:
    this.Filters.Add(new Telerik.Reporting.Filter("=IIf( Parameters.ContactID.Value is Null,1, Fields.ContactID)", Telerik.Reporting.FilterOperator.In, "=IIf( Parameters.ContactID.Value is Null,1, Parameters.ContactID.Value)"));
    The expression is created with the help of the the built-in reporting functions.

    Let us know if you have any further concerns.

    Greetings,
    Stef
    the Telerik team

    Telerik Reporting Q1 2013 available for download with impressive new visualizations. Download today from your account.

  11. Patrick Saunders
    Patrick Saunders avatar
    15 posts
    Member since:
    Jul 2011

    Posted 16 May 2014 in reply to Cory Seaman Link to this post

    Cory thanks for your post, this was exactly the issue I was running into and COALESCE fixed it.
    1st time in 10 years of SQL Server Ive found a use for COALESE! :)

    Regards, Patrick.
  12. Joe Kowalski
    Joe Kowalski avatar
    10 posts
    Member since:
    Jun 2005

    Posted 02 Feb Link to this post

    Cory, thank you SO MUCH for posting this, it was driving me nuts that I couldn't figure out how to make it work properly.
Back to Top
DevCraft banner