Trouble shooting of The ''select all'' parameter
-
Hi
When we use a report where in the parameter several possibilities shoud be available we want to use teh Select all check box.
But it seems to be a problem because the several selections or the select all doesn't not work properly in reporting services. Can't understand why because it's based on SQL, but I guess it's microsoft
Anyone got any clue how to deal with it ? By the way, we are using stored procedures.
We heard already of a split function so the selection within our stored procedure is :
where CustMain.[Responsibility Center] in (SELECT Item FROM dbo.Split('''
+ @rc + ''', ' + ''',''' + ')) and ...
and the funtion itself is :
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
ALTER
FUNCTION [dbo].[Split]
(
@ItemList
NVARCHAR(4000),
@delimiter
CHAR(1)
)
RETURNS
@IDTable TABLE (Item VARCHAR(50))
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, ' ', '')
SET @i = CHARINDEX(@delimiter, @tempItemList)
WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)
INSERT INTO @IDTable(Item) VALUES(@Item)
IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)
SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END
Anyone got a clue ?
Would help us alot !
Thx in advance
JV -
We use the multi-select all the time and it seems to work fine. I am guessing you are trying to use it with a store procedure as the source of your report? If so, that is pretty hard to get to work because stored procedures are not really designed for multi-value parameters.
The alternative is to make the source of the report your actual query. If that is done, you can simply make the query something like SELECT * FROM MyTable WHERE MyField IN (?) or SELECT * FROM MyTable WHERE MyField IN (@MyParameter). Then, just make it a multi-value parameter and reporting services handles it. The named parameter works with some data providers, but not with others. You should not have to use a split function at all.
-
I often use the "all" / multivalue dropdown option, and here's how I accomplish it:
I use a stored procedure that takes a varchar(8000) parameter (or varchar(max) in SS2005). The main query is generated via dynamic sql, and the parameter is usually used in an IN() clause.
My parameters are almost always integers, so what you get is something like this:
321,242,55,6,44,222
and your @SqlWhere (or similar) variable might look like this:
'where bookID IN(' + @MultivalueParam + ')'
-
I also use sp's with the split function and have had no issues with it. Are you using varchar(max) as the datatype for your parameter?
-
This works in a stored procedure and avoids using a function:
(charindex(',' + dbfield + ',',',' + @MulitParamList + ',')>0
-
Jon
Can u put on a more detailed example ?
I've had this issue for a long time and it would be great when i can delete it from my list.
I'm using sp's also.
Thx in advance
El Jefe
JV -
Here it is in detail:
ALTER procedure [dbo].[sp_proc] (@MultiParamList varchar(1000))
AS
SELECT *
FROM TableName
WHERE (charindex(',' + TableName.Field + ',',',' + @MultiParamList + ',')>0
-
Hi this is my stored procedure :
ALTER PROCEDURE [dbo].[sp_sepia_Financieel_Ouderdomsanalyse]
-- Add the parameters for the stored procedure here
--@rcvarchar(30),
--@Methodvarchar(10)
@MultiParamList varchar(1000)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
--set dateformat dmy
DECLARE @sql VARCHAR(MAX)
DECLARE @dtNowDATETIME
DECLARE @Opendvarchar(5)
set dateformat dmy
SET @dtNow = getdate()
SET @Opend = 1
-- Insert statements for procedure here
set @sql=' set dateformat dmy
SELECT CustMain.No_,Name, [Payment Method Code] as Betalingsconditie,
CustMain.[Responsibility Center],Blocked,
(SELECT sum(DCLE.Amount)
FROM [FIN SEPIA$Cust_ Ledger Entry] as CLE
join dbo.[FIN SEPIA$Sales Invoice Header] as IH
on CLE.[Document No_] = IH.No_
join dbo.[FIN SEPIA$Detailed Cust_ Ledg_ Entry] as DCLE
on DCLE.[Document No_] = CLE.[Document No_]
WHERE (CLE.[Customer No_] = CustMain.No_) and ([Open] = ''' + @Opend + ''')) as [Total],
FROM dbo.[FIN SEPIA$Customer] as CustMain
where (charindex('','' + [Payment Method Code] + '','','','' ' + @MultiParamList + ''','')>0
ORDER by No_
'
In my report I have the parameter : MultiParamList. It's a string.
And settings are : Multivalue, non queried.
Label 30 Days - Value 0
Label 14 Days - Value 1
Label Domi - Value 2
I still receive an error Procedure expects parameter @MultiParamList which was not supplied.
What can still be wrong ?
Many thx
El Jefe
JV -
have you re-queried the data source in report designer and checked the data source parameters tab to make sure it is listed?
-
On the Data Tab of your report go to the properties of your dataset, parameters tab and have the value set to @MultiParamList= Parameters!MultiParamList.Value and since you have already defined MultiParamList as an input parameter to your report it should automatically be passed to the procedure.
Sorry if I am missing anything here.
Prasad Bhogadi
www.inforaise.com -
Hi Prasad
That's already there.
Still get the error and he returns only one value in the error, the first.
Any idea ? Are my quotes in order (since i'm working with stored procedure) :
where (charindex(''',''' + [Payment Method Code] + '','','','' ' + @MultiParamList + ''','')>0
thx in advance to solve this one
JV
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply