Error with multi-value parameter.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Error with multi-value parameter.

Author

Topic

tmcivery
Starting Member

46 Posts

Posted - 2008-07-30 : 13:25:19

Hey all,

I'm having an issue with any report that I choose to have multi-value selected for its parameters. If you only select one value for a parameter the report runs fine but the minute you select more than one I get the following error:

Query Execution Failed for Dataset 'Dataset Name'.
Incorrect Syntax Near ','.

Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-30 : 13:26:37

Show us the query in the dataset. Can it handle comma separated lists?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:28:45

quote:
Originally posted by tmcivery

Hey all,

I'm having an issue with any report that I choose to have multi-value selected for its parameters. If you only select one value for a parameter the report runs fine but the minute you select more than one I get the following error:

Query Execution Failed for Dataset 'Dataset Name'.
Incorrect Syntax Near ','.

Any ideas?

i think this is because of the way your back end query is written. are you using query as source or stored procedure? In either case can you post the query atleast the filteration part for this parameter?

tmcivery
Starting Member

46 Posts

Posted - 2008-07-30 : 13:33:01

Thanks for the help,

Here is the entire query(not a stored procedure) for one of the reports having the issue:

SELECT Incident.IncidentID, Incident.CreatedDateTime, Incident.CreatedBy, Incident.CategoryEnglish, Incident.SubCategoryEnglish, Incident.Priority, Customer.FullName, Incident.StatusEnglish, Customer.Location, Incident.DetailEnglish, Incident.IncidentTypeEnglish

FROM Incident INNER JOIN Customer ON Incident.CustomerRecID = Customer.RecID

WHERE (Incident.CreatedDateTime >= @StartDate) AND (Incident.StatusEnglish <> 'Closed') AND (Incident.StatusEnglish <> 'Resolved') AND (Customer.Location = @Location) AND (CONVERT(varchar(12), Incident.CreatedDateTime, 101) <= CONVERT(varchar(12), @EndDate, 101)) AND (Incident.IncidentTypeEnglish = @incidenttype)
------------------------------

The parameters @location and @incidenttype are both multi-value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:40:41

try like this

SELECT Incident.IncidentID, Incident.CreatedDateTime, Incident.CreatedBy, Incident.CategoryEnglish, Incident.SubCategoryEnglish, Incident.Priority, Customer.FullName, Incident.StatusEnglish, Customer.Location, Incident.DetailEnglish, Incident.IncidentTypeEnglish

FROM Incident INNER JOIN Customer ON Incident.CustomerRecID = Customer.RecID

WHERE (Incident.CreatedDateTime >= @StartDate) 
AND (Incident.StatusEnglish <> 'Closed') 
AND (Incident.StatusEnglish <> 'Resolved') 
AND (',' + @Location + ',' LIKE '%,'+ Customer.Location + ',%') 
AND (CONVERT(varchar(12), Incident.CreatedDateTime, 101) <= CONVERT(varchar(12), @EndDate, 101)) 
AND ( ',' + @incidenttype + ',' LIKE '%,'+ Incident.IncidentTypeEnglish + ',%')

tmcivery
Starting Member

46 Posts

Posted - 2008-07-30 : 13:44:32

Same error with that code

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-30 : 13:49:17

quote:
Originally posted by tmcivery

Same error with that code

how are you passing value of parameters from the report? also have you got this error while filling the dataset in datatab?

tmcivery
Starting Member

46 Posts

Posted - 2008-07-30 : 14:39:41

I'm not sure if this is what you're asking but they are passing as string(for location and incidenttype). I receive no errors when populating the dataset.

Should I be creating seperate data sets for these two parameters?

shilpaa
Starting Member

6 Posts

Posted - 2008-07-31 : 06:53:52

Hello,

Just change Your Where Condition For the Multi Value Parameter like If the Multi Value parameter in your report is Location then condition is

(Customer.Location = @Location) to (Customer.Location IN (@Location))

Hope this Helps

Shilpa Reddy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 09:11:22

quote:
Originally posted by tmcivery

I'm not sure if this is what you're asking but they are passing as string(for location and incidenttype). I receive no errors when populating the dataset.

Should I be creating seperate data sets for these two parameters?

nope i was asking how values will be passed to query when you enter it in report. for checking it place a textbox anywhere in report with expression

=Parameters!Yourparam.value

tmcivery
Starting Member

46 Posts

Posted - 2008-08-01 : 11:24:21

Thanks for the suggestions everyone(Especially Shilpaa)! It turns out the issue was as easy as Shilpaa thought it was. Simply using IN instead of = was the key.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources