| Author |
 Topic  |
|
|
tmcivery
Starting Member
USA
46 Posts |
 Posted - 07/30/2008 : 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
USA
35017 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 07/30/2008 : 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
USA
46 Posts |
Posted - 07/30/2008 : 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
India
48064 Posts |
Posted - 07/30/2008 : 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
USA
46 Posts |
Posted - 07/30/2008 : 13:44:32
|
Same error with that code  |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48064 Posts |
Posted - 07/30/2008 : 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
USA
46 Posts |
Posted - 07/30/2008 : 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? |
Edited by - tmcivery on 07/30/2008 14:42:02 |
|
|
|
shilpaa
Starting Member
6 Posts |
Posted - 07/31/2008 : 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
India
48064 Posts |
Posted - 07/31/2008 : 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
USA
46 Posts |
Posted - 08/01/2008 : 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! |
|
|
| |
Topic |
|
Error with multi-value parameter.
