微软BI SSRS渐悟之动态SQL

浏览: 3325

引言

最近微软BI技术群(15864834)有位小伙伴,被如何在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'''

删除之后,保存可能会报错,直接忽略即可,看效果:


推荐 3
本文由 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

0 个评论

要回复文章请先登录注册