炎龍牙

今天遇到了主管有個需求，希望在看報表時，可以篩選要看的條件!!

例如，想比較2013年跟2015年的交易資料

其實這在SSRS中很簡單，就是將參數條件改為【允許多個值】。

在將報表中的DataSet的TSQL語法變更為 IN

EX：WHERE YEARS = @YEAR 改為 YEARS IN (@YEAR)

呼呼!!!打完收工~~~~~!!

打開報表準備修改時，發現報表的DataSet的來源是..預存程序 @@!!!!

很好!!，直接改預存程序中的語法，也是變更為IN就好了!!

結果出現以下錯誤!!

將nvarchar值轉換成資料型態int時，轉換失敗。

使用SQL Profiler，發現SSRS是執行以下語法 EXEC USP_TEST @YEAR=N'2015,2014,2013'

看來又是字串轉換造成的查詢問題。

先來測試一下字串轉換的方式

--建立查詢資料表與資料內容
DECLARE @TBL TABLE
(
	YEARS CHAR(4)
	,VALUE CHAR(5) 
)
INSERT INTO @TBL VALUES
('2013','ABS10'),
('2012','ABS09'),
('2014','ABS11'),
('2012','ABS08'),
('2015','ABS12'),
('2013','ABS11'),
('2015','ABS13')

--SELECT  * FROM @TBL
--建立假查詢條件(查和年)
DECLARE @YEARS NVARCHAR(20)
SET  @YEARS = N'2015,2013'
--建立查詢條件來源資料暫存表
DECLARE @COMTBL TABLE
(
	YEARS CHAR(4)
)

DECLARE @STR NVARCHAR(50)
DECLARE @Delimiter CHAR(1) = ','
SET @STR = @YEARS + @Delimiter
WHILE LEN(@STR) > 0
BEGIN 
	INSERT INTO @COMTBL 
	SELECT SUBSTRING(@STR , 1 , CHARINDEX(',',@STR)-1)
	
	SET @STR = RIGHT(@STR,LEN(@STR)-CHARINDEX(',',@STR))
END 

SELECT * FROM @TBL WHERE 
YEARS IN (SELECT YEARS FROM @COMTBL)

利用上面的方式來改成預存程序

CREATE PROCEDURE [USP_YEAR]
(
	@YEARS NVARCHAR(20)
)
AS
DECLARE @COMTBL TABLE
(
	YEARS CHAR(4)
)

DECLARE @STR NVARCHAR(50)
DECLARE @Delimiter CHAR(1) = ','
SET @STR = @YEARS + @Delimiter
WHILE LEN(@STR) > 0
BEGIN 
	INSERT INTO @COMTBL 
	SELECT SUBSTRING(@STR , 1 , CHARINDEX(',',@STR)-1)
	
	SET @STR = RIGHT(@STR,LEN(@STR)-CHARINDEX(',',@STR))
END 

SELECT * FROM @TBL WHERE --@TBL為變數資料表
YEARS IN (SELECT YEARS FROM @COMTBL)

就可以完成預存程序查詢了。

後來發現，如果參考 Terry 大的方式。使用FUNCTION來將字串轉換，可應用範圍與方式就更好。

資料集的查詢類型設為預存程序時，使用查詢參數的小技巧

 

結論(只是要記住這件事)：

1.在SSRS報表參數中，若是語法使用文字型態(T-SQL)，如果選擇允許複選，報表的參數帶入字串就會是 N‘2015’, N’2013’。

這時若要用該參數做判斷，都會因為有”逗號”導致判斷失敗。

EX： CHARINDEX(',',@YEAR) 

SSRS 在執行時，就會變成 CHARINDEX(',',N'2015',N'2013')，就會回傳CHARINDEX有無效的參數。

 

2.如果使用預存程序，報表執行時參數就會變更成為N'2015, 2013' 的字串。

水滴可成涓流，涓流可成湖泊大海。
汲取累積知識，將知識堆積成常識；將常識探究成學識；將學識簡化為知識；授人自省。