May 12, 2010 at 9:35 pm
I created a store proc for my report in SSRS 2205 which accept multi value parameters. It accepts 3 inputs. Location and year is a multi value parameters and auditee is a single value param.
However, when I select location= 'location A' and auditee='auditee A'. the query return all rows in 'auditee A' regardless the location. I expected it returned, rows in 'location A' with 'Auditee A'. Is there something wrong with my store proc?
The following is my store proc, and the UDF for using multi value parameters in SSRS 2005
============================================
ALTER PROCEDURE [dbo].[sp_PostponeReportByParam]
@location nvarchar(100) = NULL,
@year nvarchar(100) = NULL,
@auditee nvarchar(100) =NULL
AS
SELECT audit_id,
site,
auditee,
convert(varchar(10),planned_date, 101) as planned_date,
from_status,
current_status,
year(created_date) as year,
convert(varchar(10),postpone_date, 101) as postpone_date,
comments
FROM tbl_audit_postpone
WHERE
@location is null or site in (SELECT Item FROM dbo.Split (@location, ',')) and
@year is null or year(created_date) in (SELECT Item FROM dbo.Split (@year, ',')) and
@auditee is null or auditee = @auditee and
current_status in ('Approved','Pending Postpone')
===================================================================
ALTER FUNCTION [dbo].[Split]
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(4000),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item NVARCHAR(50) collate database_default )
AS
BEGIN
DECLARE @tempItemList NVARCHAR(4000)
SET @tempItemList = @ItemList
DECLARE @i INT
DECLARE @Item NVARCHAR(4000)
SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
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
===========================================================
Thanks for helping. 😉
May 13, 2010 at 10:01 am
Hi ,
Could you please try the below thin and let me know,
While running the report , using profile get the parameter values you are passing, then sun the SP separately in management studio for that combination and check what value you are getting..
Thanks & Regards,
MC
May 13, 2010 at 10:56 am
Try changing the where clause:
WHERE
(@location is null or site in (SELECT Item FROM dbo.Split (@location, ','))) and
(@year is null or year(created_date) in (SELECT Item FROM dbo.Split (@year, ','))) and
(@auditee is null or auditee = @auditee) and
current_status in ('Approved','Pending Postpone')
May 13, 2010 at 11:44 am
yeah , try changing the where clause as terraberry suggests.. there's a high chance of wrong output when you miss\exclude "()" when using "OR" & "AND"
May 14, 2010 at 10:56 am
ya. that really help!! thanks a lot.....
it took me 2 days for me to look at the problem. 😛
May 14, 2010 at 1:14 pm
You might want to have a look at the Tallytable article referenced in my signature. Part of it as a split string version that will outperform your loop in most (if not any) cases, but definitely on a larger data volume.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply