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 2008 Forums
 Analysis Server and Reporting Services (2008)
 Running SSRS with multi value parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/20/2014 :  08:23:47  Show Profile  Reply with Quote
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)










Edited by - Villanuev on 01/20/2014 19:56:04

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/20/2014 :  19:41:52  Show Profile  Reply with Quote
The Query type i've use is a text based not an stored procedure. How could i use the splitlist? any idea guys. thanks.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/21/2014 :  07:18:51  Show Profile  Reply with Quote
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

Edited by - visakh16 on 01/21/2014 07:19:12
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/21/2014 :  20:56:52  Show Profile  Reply with Quote
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

Edited by - Villanuev on 01/21/2014 20:59:57
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/22/2014 :  08:24:21  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 01/24/2014 :  04:53:42  Show Profile  Reply with Quote
In report parameter Properties
General
-----Datatype-->Text
-----check allow multiple values

available values
-----get value from a query


Edited by - Villanuev on 01/24/2014 04:54:04
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/24/2014 :  05:41:12  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 02/04/2014 :  02:30:38  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/04/2014 :  07:22:15  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 02/16/2014 :  20:03:57  Show Profile  Reply with Quote
This is my main select statement.

Select *
From
Where Areaid='XY'
AND EXISTS (SELECT wct.WrkCtrId FROM @wrkCtrTableTemp wct WHERE pr.WRKCTRID=wct.WrkCtrId)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/17/2014 :  05:25:34  Show Profile  Reply with Quote
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
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

443 Posts

Posted - 03/04/2014 :  04:33:22  Show Profile  Reply with Quote
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)



Edited by - Villanuev on 03/04/2014 04:33:50
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