| Author |
Topic |
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
 Posted - 2014-01-20 : 08:23:47
|
Hi,
I have an SSRS report with a
Parameter value that get the values from a query. I have to select more or less 20 items (workid) from a drop down. upon running the report i got this error message. I'm thinking this is came from the splitlist which is having a long list of workctr. Thanks.
Error message running an SSRS report
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'Prod'. (rsErrorExecutingCommand)
Procedure or function Dynamics.dbo.splitlist has too many arguments specified.
--Here are the setup in my SSRS report.
Parameter Name: workCtr
Prompt :WorkCenter
Datatype :Text
allow multiple values
--inside my query type text
DECLARE @wrkCtrTableTemp TABLE
(
[WrkCtrId] NVARCHAR(10) PRIMARY KEY CLUSTERED
);
-- Estimated record to be inserted : 150
INSERT INTO @wrkCtrTableTemp(WrkCtrId)
SELECT [ListValue] from Dynamics.dbo.splitlist(@workCenter,',')
--In my select statement I’m using this line of codes
SELECT *
FROM WHERE Dataaewa='TTP'
AND EXISTS (SELECT wct.WrkCtrId FROM @wrkCtrTableTemp wct WHERE pr.WRKCTRID=wct.WrkCtrId)
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2014-01-20 : 19:41:52
|
| The Query type i've use is a text based not an stored procedure. How could i use the splitlist? any idea guys. thanks. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-21 : 07:18:51
|
no need of intermediate table variable
SELECT *
FROM TableNameHere wct
INNER JOIN Dynamics.dbo.splitlist(@workCenter,',') f
ON f.[ListValue]=wct.WrkCtrId
WHERE Dataaewa='TTP'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2014-01-21 : 20:56:52
|
Hi Visakh, Thanks for the reply.
An error occurred during processing of this report.
norrowing down to select 1 workcenter is working but 2 or more workcenter i have select still got this error. same error message.
Here is the value of my parameter workCenter selecting using dropdown.
workCenter: Assembly1
Assembly2
EOL
AB-1
AB-2
C-1
CB
Assy |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-22 : 08:24:21
|
how have you set parameter in report? Have you set Allow Multivalue values property? also how is the parameter value passed down to the query behind?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2014-01-24 : 04:53:42
|
In report parameter Properties
General
-----Datatype-->Text
-----check allow multiple values
available values
-----get value from a query
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-24 : 05:41:12
|
first run this and see whether you get individual values out correctly when you pass multiple values for workcenter parameter
SELECT [ListValue] from Dynamics.dbo.splitlist(@workCenter,',')
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2014-02-04 : 02:30:38
|
Hi Visakh,
Sorry for my late reply. I choose 1 work center and this select statement is working, but choosing 2 or more and gets an error. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-04 : 07:22:15
|
quote:
Originally posted by Villanuev
Hi Visakh,
Sorry for my late reply. I choose 1 work center and this select statement is working, but choosing 2 or more and gets an error.
can you show the filter condition used in query?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2014-02-16 : 20:03:57
|
This is my main select statement.
Select *
From
Where Areaid='XY'
AND EXISTS (SELECT wct.WrkCtrId FROM @wrkCtrTableTemp wct WHERE pr.WRKCTRID=wct.WrkCtrId) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-02-17 : 05:25:34
|
quote:
Originally posted by Villanuev
This is my main select statement.
Select *
From
Where Areaid='XY'
AND EXISTS (SELECT wct.WrkCtrId FROM @wrkCtrTableTemp wct WHERE pr.WRKCTRID=wct.WrkCtrId)
where's filter for multi valued parameter? i cant see anything related to it.
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
Villanuev
Constraint Violating Yak Guru
478 Posts |
Posted - 2014-03-04 : 04:33:22
|
Here is my code. btw, i'm using Text Query type not SP.
--inside my query type text
DECLARE @wrkCtrTableTemp TABLE
(
[WrkCtrId] NVARCHAR(10) PRIMARY KEY CLUSTERED
);
-- Estimated record to be inserted : 150
INSERT INTO @wrkCtrTableTemp(WrkCtrId)
SELECT [ListValue] from Dynamics.dbo.splitlist(@workCenter,',')
--In my select statement I’m using this line of codes
SELECT *
FROM WHERE Dataaewa='TTP'
AND EXISTS (SELECT wct.WrkCtrId FROM @wrkCtrTableTemp wct WHERE pr.WRKCTRID=wct.WrkCtrId)
|
|
|
|
|
Running SSRS with multi value parameter