Hi guys,
I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.
Thanks in advance.
Hi guys,
I'm new in SSRS, Im having problems passing multivalue in stored procedures. Can someone help. please.
Thanks in advance.
Hi,
What is the stored procedure expecting More than one parameter Or a list of values concatenated with strings
-Jessica
I want to pass multiple value in the event type. If i pass a single value, it would work but for multiple value it fails.
Here's my storedd procedure:
GO
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
CREATE
PROCEDURE [dbo].[usp_retrieve_audit_data] -- Add the parameters for the stored procedure here@start_date
as DateTime = NULL,@end_date
as DateTime = NULL,@event
as varchar(20)= NULL
AS
BEGIN
SET NOCOUNT ON; -- Insert statements for procedure here SELECT audit_log_id, date_created, event_type, event_name, event_status,row_affected
, updated_by FROM audit_log WHERE (date_created >= @start_date) AND (date_created <= @end_date) AND (event_type IN (@event))END
someone gave me this answer last time... but i've modified the column size
create a table valued function..
CREATE
FUNCTION [dbo].[multival_prm] (@list ntext,@delimiter
nchar(1) = N',') RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL, str varchar(64)) ASBEGIN DECLARE @pos int,
@textpos
int,@chunklen
smallint,@tmpstr
nvarchar(64),@leftover
nvarchar(64),@tmpval
nvarchar(64)SET @textpos = 1 SET @leftover = '' WHILE @textpos <= datalength(@list) / 2 BEGIN SET @chunklen = 64 - datalength(@leftover) / 2 SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0 BEGIN SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1))) INSERT @tbl (str) VALUES(@tmpval) SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SET @pos = charindex(@delimiter, @tmpstr) END
SET @leftover = @tmpstr END
INSERT @tbl(str) VALUES (ltrim(rtrim(@leftover))) RETURN END
from your stored proc,
SELECT audit_log_id, date_created, event_type, event_name, event_status,
row_affected
, updated_by FROM audit_log WHERE (date_created >= @start_date) AND (date_created <= @end_date) AND (event_type inner join multiprm_val( @event , default))HTH
I get this done by useing dynamic SQL. You will need to add an a parm directly to your report.
Here's what your procedure code would look like:
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
CREATE
PROCEDURE [dbo].[usp_retrieve_audit_data]@start_date
as DateTime = NULL,@end_date
as DateTime = NULL,@event
as varchar(20)= NULLAS
BEGIN
SET
NOCOUNT ON;DECLARE
@sql varchar(8000) , @crlf char(2)SELECT
@crlf = char(13) + char(10)SELECT
@sql =N'SELECT audit_log_id, date_created, event_type, event_name, event_status, '
+ @crlf+
'row_affected, updated_by' + @crlf+
'FROM audit_log' + @crlf+
'WHERE (date_created between ''' + CONVERT(VARCHAR(30), @start_date, 121) + ''' AND ''' + CONVERT(VARCHAR(30), @end_date, 121) +''') AND' + @crlf+
'(event_type IN (' + @event + '))' + @crlfEXEC
(@sql)--PRINT @sql
END
In you report add a parameter and call it @Event_List of type string. Make it a multi select and add all of you default values. Wrap the values in single ticks ex. '1'
Hide the parm @event and under "Default values" set it's "Non-queried" value to the following expression:
=JOIN(Parameters!Event_List, ",")
What is passed back in @event will be a comma delimited list of values selected in @Event_List and will look like '1','2','3'
This value is substituted in the dynamic sql above and your dataset will be for only the events selected by your user.