Query execution failure for dataset

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 2008 Forums

Analysis Server and Reporting Services (2008)

Query execution failure for dataset

Author

Topic

Anteneh
Starting Member

4 Posts

Posted - 2012-10-22 : 22:43:14

I have been practicing on building report by just following the steps in the SQL Server 2008 Tutorial in the software documentation. The tutorial uses 'AdventureWorks' database. After setting the 'Allow multiple values' option for one of the parameters, I tried to preview the report, and the following msg comes:

An error occurred during local report processing.
An error has occurred during report processing.s
Query execution failed for dataset 'Dataset_Name'.
Incorrect syntax near ','.

It worked fine just before activating the 'Allow multiple values' option in the 'Report Parameter Properties' dialogue box. Why is that so?
Thank you.

prett
Posting Yak Master

212 Posts

Posted - 2012-10-23 : 06:34:30

When the parameter is a multi-select you get a string like this: 'ParamValue1,ParamValue2,ParamValue3', while if it is a single select the string is: 'ParamValue1'. You want to say: WHERE paramName IN ('ParamValue1','ParamValue2','ParamValue3'), so you have to split the string prior to using it like this.

I would normally build a function in SQL, which will do this. Something like udf_t_SplitString(@String), which is a table valued function breaking up the comma-delimited string. Then, you can use it like this: WHERE ParamName IN (SELECT ParamValue FROM udf_t_SplitString(@Param)). This should give you the correct result.

Note: The issue does not exist for single selects because the param value is already in the correct format: WHERE ParamName IN ('ParamValue1').

Also, make sure that the nvarchar/varchar for the sproc parameter is long enought to take all selections.

Anteneh
Starting Member

4 Posts

Posted - 2012-10-25 : 10:37:52

Thank you prett, for your quick reply.
The problem I am facing here is that I am not using the WHERE clause in my query- of course I just used the same query statements in the tutorial I mentioned. I did not try your solution, but still it seems in the tutorial as if there is nothing wrong with the query. The query build report says that there in not one error, not even a warning. The msg I just mentioned is shown in the 'Preview' window of the report designer. Below is the name of the two datasets I already have and the respective queries, as reference:

AdventureWorksDataSet:

SELECT soh.OrderDate AS [Date], DATENAME(weekday, soh.OrderDate) as Weekday,
soh.SalesOrderNumber AS [Order],
pps.Name AS Subcat, pp.Name as Product,
SUM(sd.OrderQty) AS Qty,
SUM(sd.LineTotal) AS LineTotal
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader AS soh
ON sp.BusinessEntityID = soh.SalesPersonID
INNER JOIN Sales.SalesOrderDetail AS sd
ON sd.SalesOrderID = soh.SalesOrderID
INNER JOIN Production.Product AS pp
ON sd.ProductID = pp.ProductID
INNER JOIN Production.ProductSubcategory AS pps
ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS ppc
ON ppc.ProductCategoryID = pps.ProductCategoryID
GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber, pps.Name, pp.Name,
soh.SalesPersonID
HAVING ppc.Name = 'Clothing'
AND (soh.OrderDate BETWEEN (@StartDate) AND (@EndDate))
AND soh.SalesPersonID = (@BusinessPersonID)

BusinessPersons:

SELECT SP.BusinessEntityID, C.FirstName, C.LastName
FROM Sales.SalesPerson AS SP INNER JOIN
HumanResources.Employee AS E ON E.BusinessEntityID = SP.BusinessEntityID INNER JOIN
Person.Person AS C ON C.BusinessEntityID = E.BusinessEntityID
ORDER BY SP.BusinessEntityID

As you can see there is no any 'WHERE' statement in both datasets queries. (The 'Dataset_Name' in the error report, in my first post, is 'AdventureWorksDataSet', to be specific. And please let me know if I am not describing the situation well.
Thank you.

Anteneh
Starting Member

4 Posts

Posted - 2012-10-25 : 11:41:00

Finally! I figured it was just my problem. I missed changing the equality sign in

AND soh.SalesPersonID = (@BusinessPersonID)

by the 'IN' keyword and have:

AND soh.SalesPersonID IN (@BusinessPersonID)

when I do copying, after changing the parameter property to accept multiple values. What a mistake, huh? No problem with the code in the tutorial, it surely means. Thanks for the clue, prett!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources