微軟BI SSRS漸悟之動態SQL

每日幹貨好文分享丨請點擊+關注

歡迎關注天善智能微信公眾號，我們是專注於商業智能BI，大數據，數據分析領域的垂直社區。

對商業智能BI、大數據分析挖掘、機器學習，python，R等數據領域感興趣的同學加微信：tstoutiao，邀請你進入頭條數據愛好者交流群，數據愛好者們都在這兒。

引言

最近微軟BI技術群有位小夥伴，被如何在RS數據集中執行動態SQL搞死了。能把解決思路放在動態SQL上的，不用問一定是幹過軟件開發的。動態SQL在某些業務場景下，不論是執行效率還是簡潔程度，都給足了我們去用它的理由。那在RS的數據集中能不能使用動態SQL呢？至少存儲過程是可以的……

存儲過程動態SQL

示例表

select * from Tmp

存儲過程

create proc up_ssrs_dynamic_sql
(
@where nvarchar(1000)
)
as
declare @sql nvarchar(1000)
begin
set @sql = 'select name,paymon,type,cost from tmp'
if(@where is not null and @where != '')
set @sql = @sql + ' where ' + @where
exec(@sql)
end
go

測試

exec up_ssrs_dynamic_sql 'name = ''羅達'' and type = ''Budget'''

RS數據集

step1、添加數據集

step2、測試存儲過程

接上步，點擊“查詢設計器”

在查詢設計器窗口中，點擊“！”執行按鈕，在彈出的“定義查詢參數”－“參數值”中輸入模擬的參數“name = '羅達' and type = 'Budget'”，注意是紅色部分。不知道大家注意到沒有，這兒的參數值和我們在SQL分析器中的輸入是有區別的，區別在引號上，數據庫字符串中，兩個單引號表示一個轉譯的單引號。結果如下：

添加字段

點擊“確認”，關閉查詢設計器。將設計器屬性切換到“字段”，有木有發現，有木有發現，字段是空白的……

不要怕，不給爺顯示，爺自已加……

接著點擊“確定”，數據集已成功創建

數據集參數

接上，點擊“參數”文件夾，RS已經幫我們創建好了參數，想設置雙擊設置就OK了

預覽

隨便插入個列表組件，添加一下列綁定，點擊“預覽”按鈕。在參數框中輸入我們的測試參數，點擊“查看報表”，大功告成……

數據集中動態SQL

很多時候我們是沒有權限在業務數據庫中創建存儲過程之類的資源的，而且也不利於維護。那麼數據集中真的不能拚接動態SQL嗎？抱著永不放棄的精神試試吧……

直接SQL字符串

接上，我們雙擊已創建好的數據集，將查詢類型由“存儲過程”改為“文本”。在文本框中直接輸入SQL字符串

點擊“確認”，RS仍然彈出一個“定義參數查詢”窗口，如上麵，我們依然輸入查詢參數“name = '羅達' and type = 'Budget'”，點擊“確定”，結果卻是一個大大的異常……

語法不對，確實也是，在SQL查詢分析器，我們拚接變量也得用set或者是exec直接執行，我們依次試下

SET方式

EXEC方式

正確方式

雖然上述兩種方式得到的答案都有異常，但是也能從中收獲一些提示。

DECLARE @where nvarchar(1000) = 'name = ''羅達'' and type = ''Budget'''
exec('select name,paymon,type,cost from tmp where ' + @where)

點擊“確定”，窗口奇跡般的關閉了，怎麼情況……實際上已經設置成功了。

如果是新創建的數據集，應該會彈出一個“定義參數查詢”窗口，直接輸入參數。

如果是新創建的數據集，在參數那兒的參數綁定應該是空的，不過這也正是需要我們調整的地方，手動添加參數。

切記

所有的設置都完成之後，刪除數據集“查詢語句”中的

DECLARE @where nvarchar(1000) = 'name = ''羅達'' and type = ''Budget'''

刪除之後，保存可能會報錯，直接忽略即可，看效果：

天善學院微軟BI課程精華推薦：

微軟BI SSIS 2012 ETL 控件與案例精講、

SSRS 2012 Metro UI 高端報表視頻教程【全國首家】

鏈接：https://edu.hellobi.com/course/15

對商業智能BI、大數據分析挖掘、機器學習，python，R等數據領域感興趣同學加微信：tstoutiao，邀請您加入頭條數據愛好者交流群，數據愛好者們都在這兒。

本文來源自天善社區悟的博客。

原文鏈接：https://ask.hellobi.com/blog/lovezsr/5223 。