sql2012 ssrs
Experienced business intelligence (BI) developers would tell you that as you move from one project to another, some requirements start becoming repetitive like you have dealt with them before. One such repetitive requirement occurs during SQL Server Reporting Services (SSRS) development wherein a client would request that a report parameter be configured to allow multiple values from a dataset that is populated by stored procedure, as illustrated in Figure 1.
经验丰富的商业智能（BI）开发人员会告诉您，当您从一个项目转到另一个项目时，某些需求开始变得重复，就像您以前处理过的那样。 这样的重复性需求发生在SQL Server Reporting Services（SSRS）开发期间，其中客户端将请求将报告参数配置为允许来自存储过程填充的数据集中的多个值， 如图1所示 。
In the backend, the selected multiple parameter values are passed onto the report stored procedure as one long delimited string as shown in Script 1.
在后端，如脚本1所示，将所选的多个参数值作为一个长定界字符串传递到报表存储过程中。
EXEC [dbo].[rpt_spDemo] 'Chelsea,Arsenal
It then becomes the responsibility of the report stored procedure to be able to break down the long delimited-string into multiple rows so they can be evaluated in a WHERE clause of the stored procedure query. One way of breaking down the delimited parameter values is by using a user-defined table-valued function. Script 2 shows my well-trusted table-valued function code that I usually utilize for splitting values into multiple rows – the function assumes that the string to be split uses a comma delimiter but this can always be modified according to your own delimiter.
然后，报表存储过程的责任是能够将长的分隔字符串分成多行，以便可以在存储过程查询的WHERE子句中对其进行求值。 分解定界参数值的一种方法是使用用户定义的表值函数。 脚本2显示了我经常使用的表值函数代码，该函数代码通常用于将值拆分为多行–该函数假定要拆分的字符串使用逗号定界符，但始终可以根据自己的定界符对其进行修改。
CREATE FUNCTION [dbo].[func_CommaDelimitedString] (@val nvarchar(100))
RETURNS @clublist TABLE (clubname nvarchar(55))
BEGIN
DECLARE @x int = 1
DECLARE @y int = CHARINDEX(',', @val)
WHILE @x < LEN(@val) + 1
BEGIN
IF @y = 0
BEGIN
SET @y = LEN(@val) + 1
END
INSERT INTO @clublist (clubname) VALUES (SUBSTRING(@val, @x, @y - @x))
SET @x = @y + 1
SET @y = CHARINDEX(',', @val, @x)
END
RETURN
END
Having copy-pasted the code in Script 2 and successfully created the function, all that is left to do is simply alter the report stored procedure and modify the WHERE clause to reference the newly created table-valued function as indicated in Script 3.
复制粘贴了脚本2中的代码并成功创建函数后，剩下要做的就是简单地更改报表存储过程，并修改WHERE子句以引用脚本3中指示的新创建的表值函数。
CREATE PROC [dbo].[rpt_spDemo] @ClubName [varchar](985)
AS
SELECT
[Position],
[Club],
[Goals]
FROM [dbo].[view_RPT_Demo]
WHERE [Club] IN (
SELECT
clubname
FROM [dbo].[func_CommaDelimitedString](@ClubName))
One major benefit of reusing existing scripts when dealing with a repetitive requirement is that you cut down on development time thus enabling you to focus on other aspects of the project. Yet, not all SQL Server environments will support the successful execution of your well-trusted scripts. I was recently involved in a project whose requirement felt like something I have done before in that the client – you guessed it – wanted one of the SSRS reports to be able to pass multiple parameter values into a stored procedure-based dataset. However, the stored procedure was stored in a SQL Server Parallel Data Warehouse (PDW) environment. Undoubtedly, SQL Server PDW as a data store has several benefits for business intelligence and related analytical projects. Yet, SQL Server PDW lacks support for basic features that are largely supported in a traditional SQL Server symmetric multiprocessing instance. This then makes it difficult to “plug and play” your well trusted script into a SQL Server PDW environment. In this article, I will cover some of the limitations of SQL Server PDW when it comes to splitting multi-parameter values passed from SSRS into multiple rows and later, I will provide you with a piece of string-splitter code that can successfully be executed in a SQL Server PDW to get your SSRS report working.
在处理重复性需求时重用现有脚本的一个主要好处是，您可以减少开发时间，从而使您可以专注于项目的其他方面。 但是，并非所有SQL Server环境都将支持成功执行值得信赖的脚本。 我最近参与了一个项目，这个项目的需求就像我以前做过的一样，因为客户（您猜到了）希望一个SSRS报告能够将多个参数值传递到基于存储过程的数据集中。 但是，该存储过程存储在SQL Server并行数据仓库（PDW）环境中。 毫无疑问，SQL Server PDW作为数据存储可为商业智能和相关分析项目带来诸多好处。 但是，SQL Server PDW缺少对传统SQL Server对称多处理实例中很大程度上受支持的基本功能的支持。 这样就很难将您值得信赖的脚本“插入并播放”到SQL Server PDW环境中。 在本文中，当涉及将从SSRS传递的多参数值拆分为多行时，我将介绍SQL Server PDW的一些局限性，稍后，我将为您提供一段可以成功执行的字符串拆分器代码在SQL Server PDW中运行SSRS报告。
SQL Server PDW限制＃1：表值函数 (SQL Server PDW Limitation #1: Table-Valued Functions)
You will soon realize that your so-called well-trusted table-valued function scripts are not very reliable because as soon as you execute them in a SQL Server PDW environment, you immediately run into the error message shown in Figure 2.
您很快就会意识到，所谓的值得信赖的表值函数脚本并不十分可靠，因为一旦在SQL Server PDW环境中执行它们，就会立即遇到图2所示的错误消息。
To be fair, the error returned in Figure 2 has more to do with the fact that return type TABLE in user defined functions is not recognized in SQL Server PDW. SQL Server PDW only supports the implementation of scalar-valued functions as demonstrated in Figure 3.
公平地说， 图2中返回的错误与SQL Server PDW无法识别用户定义函数中的返回类型TABLE有关。 SQL Server PDW仅支持标量值函数的实现， 如图3所示 。
Since the table-valued function is not a viable option when it comes to SQL Server PDW, we need to find another mechanism for splitting delimited string values passed from an SSRS dataset.
由于对于SQL Server PDW，表值函数不是一个可行的选择，因此我们需要找到另一种机制来拆分从SSRS数据集中传递的定界字符串值。
SQL Server PDW限制＃2：递归公用表表达式（CTE） (SQL Server PDW Limitation #2: Recursive Common Table Expressions (CTE))
Recursive common table expressions (CTE) provides us with another mechanism for splitting delimited string into multiple rows. Figure 4 shows a sample recursive CTE script that references itself as part of breaking down a given comma delimited string into multiple rows. Although this recursive CTE script successfully executes in a traditional SQL Server instance, it fails to commit when run within a SQL Server PDW environment.
递归公用表表达式（CTE）为我们提供了另一种将定界字符串拆分为多行的机制。 图4显示了一个示例递归CTE脚本，该脚本在将给定的逗号分隔字符串分成多行的过程中对其进行引用。 尽管此递归CTE脚本在传统SQL Server实例中成功执行，但是在SQL Server PDW环境中运行时，它无法提交。
Similarly, to the behavior of user-defined functions, SQL Server PDW still supports the implementation of normal CTEs just not the recursive kind. Yet again, we still find ourselves with no viable option for splitting multi-valued parameter strings in SQL Server PDW. Thus, we continue to look for another method so we can get our SSRS report working.
同样，对于用户定义函数的行为，SQL Server PDW仍然支持常规CTE的实现，而不仅仅是递归类型。 再一次，我们仍然发现没有可行的选择在SQL Server PDW中拆分多值参数字符串。 因此，我们继续寻找另一种方法，以便我们的SSRS报告能够正常工作。
SQL Server PDW限制＃3：XML Nodes（）方法 (SQL Server PDW Limitation #3: XML Nodes() Method)
Another way we can split a delimited string into multiple rows would be to convert the delimited string passed by an SSRS report dataset into an XML document and query it using the nodes() method as shown in Figure 5.
我们可以将定界字符串拆分为多行的另一种方法是将SSRS报告数据集传递的定界字符串转换为XML文档，并使用如图5所示的nodes（）方法对其进行查询。
Yet again, a well-trusted script that successfully executes in a traditional SQL Server instance breaks when being run on a SQL Server PDW platform. Furthermore, unlike with user defined functions and CTEs which are partially supported, SQL Server PDW doesn’t support XML data type at all. This is also true for CLR data types. Luckily, there is one more option that works in both traditional SQL Server and SQL Server PDW.
再一次，当在SQL Server PDW平台上运行时，在传统SQL Server实例中成功执行的可信脚本会中断。 此外，与部分支持的用户定义函数和CTE不同，SQL Server PDW完全不支持XML数据类型。 对于CLR数据类型也是如此。 幸运的是，在传统SQL Server和SQL Server PDW中都可以使用另一个选项。
解决方案：WHILE循环中的临时表 (Solution: Temporary Table in a WHILE Loop)
It turns out SQL Server PDW does support both the creation of temporary tables as well as the execution of WHILE loop statements. A combination of these two features enables us to setup a stored procedure script that can split a delimited string received from an SSRS report into multiple rows used for filtering the base query. The complete script is shown in Script 4.
事实证明，SQL Server PDW确实支持临时表的创建以及WHILE循环语句的执行。 这两个功能的组合使我们能够设置存储过程脚本，该脚本可以将从SSRS报告接收的定界字符串拆分为用于过滤基本查询的多个行。 完整的脚本显示在脚本4中 。
DECLARE @String varchar(550) = 'Chelsea,Arsenal'
IF OBJECT_ID('tempdb..#tmp_CommaDelimitedString') IS NOT NULL
DROP TABLE #tmp_CommaDelimitedString
CREATE TABLE #tmp_CommaDelimitedString (val varchar(100))
DECLARE @IndexOfDelimeter int = CHARINDEX(',', @String),@y varchar(max),@x int = 1
IF @IndexOfDelimeter = 0
BEGIN
INSERT INTO #tmp_CommaDelimitedString VALUES (@String)
END
SET @String = @String + ','
WHILE @IndexOfDelimeter > 0
BEGIN
SET @y = SUBSTRING(@String, @x, @IndexOfDelimeter - @x)
IF (@y <> '')
INSERT INTO #tmp_CommaDelimitedString VALUES (@y)
SET @x = @IndexOfDelimeter + 1
SET @IndexOfDelimeter = CHARINDEX(',', @String, @x)
END
SELECT
[Position],
[Club],
[Goals]
FROM [dbo].[view_RPT_Demo]
WHERE [Club] IN (SELECT val FROM #tmp_CommaDelimitedString)
结论 (Conclusion)
As SSRS report requirements go, providing a functionality to be able to pass multiple parameter values into a stored procedure-based dataset has to be one of the popular requirements from business. Yet, depending on your data store, implementing a string-splitter mechanism necessary for providing the required functionality can range from convenient to complicated. In this article, we have demonstrated that whilst it is possible to split delimited string using table-valued function, XML nodes method, recursive CTEs, in a traditional SQL Server instance only a combination of temporary tables and WHILE loop statements will successfully execute against a SQL PDW data store.
随着SSRS报告要求的发展，提供一种能够将多个参数值传递到基于存储过程的数据集中的功能，已成为企业中流行的要求之一。 但是，根据数据存储的不同，实现提供所需功能所必需的字符串分隔符机制的范围可能从方便到复杂。 在本文中，我们证明了虽然可以使用表值函数，XML节点方法，递归CTE分割定界字符串，但在传统SQL Server实例中，只有临时表和WHILE循环语句的组合才能成功地对SQL PDW数据存储。
sql2012 ssrs
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
