SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Error with multi-value parameter.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tmcivery
Starting Member

USA
46 Posts

Posted - 07/30/2008 :  13:25:19  Show Profile  Send tmcivery a Yahoo! Message  Reply with Quote
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

USA
36782 Posts

Posted - 07/30/2008 :  13:26:37  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/30/2008 :  13:28:45  Show Profile  Reply with Quote
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?
Go to Top of Page

tmcivery
Starting Member

USA
46 Posts

Posted - 07/30/2008 :  13:33:01  Show Profile  Send tmcivery a Yahoo! Message  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/30/2008 :  13:40:41  Show Profile  Reply with Quote
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 + ',%')
Go to Top of Page

tmcivery
Starting Member

USA
46 Posts

Posted - 07/30/2008 :  13:44:32  Show Profile  Send tmcivery a Yahoo! Message  Reply with Quote
Same error with that code
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/30/2008 :  13:49:17  Show Profile  Reply with Quote
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?
Go to Top of Page

tmcivery
Starting Member

USA
46 Posts

Posted - 07/30/2008 :  14:39:41  Show Profile  Send tmcivery a Yahoo! Message  Reply with Quote
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?

Edited by - tmcivery on 07/30/2008 14:42:02
Go to Top of Page

shilpaa
Starting Member

6 Posts

Posted - 07/31/2008 :  06:53:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2008 :  09:11:22  Show Profile  Reply with Quote
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
Go to Top of Page

tmcivery
Starting Member

USA
46 Posts

Posted - 08/01/2008 :  11:24:21  Show Profile  Send tmcivery a Yahoo! Message  Reply with Quote
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!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000