This is a migrated thread and some comments may be shown as answers.

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

15 Answers 317 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Wayde
Top achievements
Rank 1
Wayde asked on 19 Dec 2011, 09:36 AM
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!

15 Answers, 1 is accepted

Sort by
0
Steve
Telerik team
answered on 19 Dec 2011, 11:08 AM
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!

0
Wayde
Top achievements
Rank 1
answered on 20 Dec 2011, 08:30 PM
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

0
Steve
Telerik team
answered on 21 Dec 2011, 06:00 PM
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!

0
Wayde
Top achievements
Rank 1
answered on 26 May 2012, 09:59 AM
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
0
Steve
Telerik team
answered on 30 May 2012, 05:40 PM
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 >

0
Bryan Hughes
Top achievements
Rank 1
answered on 04 Jun 2012, 08:10 PM
I think the main problem is using length function on a Int32.
0
Cory Seaman
Top achievements
Rank 1
answered on 18 Mar 2013, 04:53 PM
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.

0
Stef
Telerik team
answered on 22 Mar 2013, 07:26 AM
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.

0
Patrick Saunders
Top achievements
Rank 1
answered on 17 May 2014, 02:55 AM
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.
0
Joe Kowalski
Top achievements
Rank 1
answered on 02 Feb 2016, 10:13 PM
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.
0
Jayaram Krishnan
Top achievements
Rank 1
answered on 14 Dec 2017, 09:44 AM
Thanks Cory.I was going to post a thread and then found this solution.

This can be tested in t-sql:

select 'A' where 1,2 is null -- this will generate the error mentioned, so telerik is replacing the param value with comma separated values

select 'A' where COALESCE( 1,2, NULL) IS NULL -- this works and will evaluate to false so it will pick the values in the 'in' clause

Madani
0
Inayat Walli
Top achievements
Rank 1
answered on 28 Nov 2018, 10:23 AM

Hi Cory from 2018:)

Your solution still actual, thank you very much, really appriciate your help!

0
Cory Seaman
Top achievements
Rank 1
answered on 28 Nov 2018, 12:32 PM
Glad to see this has been helpful to a few people over the years! :)
0
Cory Seaman
Top achievements
Rank 1
answered on 28 Nov 2018, 12:33 PM
Nice to see that it's been helpful to a few people over the years! :)
0
Cory Seaman
Top achievements
Rank 1
answered on 28 Nov 2018, 12:34 PM
Nice to see that it's been helpful to a few people over the years!
Tags
General Discussions
Asked by
Wayde
Top achievements
Rank 1
Answers by
Steve
Telerik team
Wayde
Top achievements
Rank 1
Bryan Hughes
Top achievements
Rank 1
Cory Seaman
Top achievements
Rank 1
Stef
Telerik team
Patrick Saunders
Top achievements
Rank 1
Joe Kowalski
Top achievements
Rank 1
Jayaram Krishnan
Top achievements
Rank 1
Inayat Walli
Top achievements
Rank 1
Share this question
or