iT邦幫忙

第 12 屆 iT 邦幫忙鐵人賽

0
自我挑戰組

回顧再出發~系列 第 5

SSRS Report 條件複選

1.add report
2.add dataset
3.edit param be mulit
https://ithelp.ithome.com.tw/upload/images/20201124/20106764u6BRvhJL1r.png

2.在CR #Add DataSet

Query type : Text
Query :

select * from
(
select '1' as ID
union select '2'
union select '3'
union select '4'
union select '5'
) a
where ID in (@pal)

檢查1.Parameters 會出現@pal
檢查2.DataSet出現欄位
https://ithelp.ithome.com.tw/upload/images/20201124/20106764qlvX5OKnbs.pnghttps://ithelp.ithome.com.tw/upload/images/20201124/20106764mWDVxqJ2JG.png
https://ithelp.ithome.com.tw/upload/images/20201124/201067648VCEfiEJVU.png

3.編輯@pal-Report Parameter Properties

#編輯@pal

Data type : Allow multiple values
https://ithelp.ithome.com.tw/upload/images/20201124/20106764AOwJ0BQKkF.png

使用變數

JOIN(Parameters!pal.Value,",")
https://ithelp.ithome.com.tw/upload/images/20201124/20106764xKXujBERZ8.png

顯示 : ●金 或○金
=IIF
(
JOIN(Parameters!pal.Value,",").Contains("1,2,3,4,5")=true 
,"○"
,IIF
(
JOIN(Parameters!pal.Value,",").Contains("1")=true 
,"●","○"
)
) & "金" 

顯示 : ●ALL 或○ALL
 
=IIF
(
JOIN(Parameters!pal.Value,",").Contains("1,2,3,4,5")=true 
,"●"
,"○"
) & "ALL"

如果使用SP也可以

1.

在SQL SERVER add proc : sp_xxx @pal

create proc sp_xxx @pal varchar(max)
as
create table #tmp
(
ID varchar(8)
)
insert into #tmp select 1
insert into #tmp select 2
insert into #tmp select 3

select * from #tmp where @pal like '%'+ID+'%' --這裡要注意,要寫成這樣.

2.

Query type : SP *這裡要選SP
Query : sp_xxx

ref
https://dotblogs.com.tw/terrychuang/2011/06/14/28315
https://www.sqlshack.com/using-multi-value-parameters-in-ssrs/


上一篇
SSRS Pass a Report Parameter Within a URL
下一篇
SQL MD5 加密
系列文
回顧再出發~18

尚未有邦友留言

立即登入留言