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

Analysis Server and Reporting Services (2008)

Running SSRS with multi value parameter

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)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources