SQL 在 Reporting Services 中为单个参数传递多个值

Question

提问by Scott and the Dev Team

I have several Multi-Select parameters in my report. I am trying to find a way to pass in multiple values for a single parameter in the web query string? If I pass in a single value, it works fine.

我的报告中有几个多选参数。我试图找到一种方法来为 web 查询字符串中的单个参数传递多个值？如果我传入一个值，它工作正常。

The report runs fine selecting multiple choices for a single param. My trouble lies in the web query string.

该报告运行良好，为单个参数选择多个选项。我的问题在于网络查询字符串。

Answer 1

回答by Ed Harper

Although John Sansom's solution works, there's another way to do this, without having to use a potentially inefficient scalar valued UDF. In the SSRS report, on the parameters tab of the query definition, set the parameter value to

尽管 John Sansom 的解决方案有效，但还有另一种方法可以做到这一点，而不必使用潜在的低效标量值 UDF。在 SSRS 报表中，在查询定义的参数选项卡上，将参数值设置为

=join(Parameters!<your param name>.Value,",")

In your query, you can then reference the value like so:

在您的查询中，您可以像这样引用该值：

where yourColumn in (@<your param name>)

Answer 2

回答by Scott and the Dev Team

This is what I use when passing a multi-select param to another multi-select param.

这是我在将多选参数传递给另一个多选参数时使用的。

=SPLIT(JOIN(Parameters!<your param name>.Value,","),",")

Answer 3

回答by John Sansom

This is one of the poor supported features in SQL Reporting Services.

这是 SQL Reporting Services 中支持较差的功能之一。

What you need to do is pass all of your selected items as a single string to your stored procedure. Each element within the string will be separated by a comma.

您需要做的是将您选择的所有项目作为单个字符串传递给您的存储过程。字符串中的每个元素都用逗号分隔。

What I then do is split the string using a function that returns the provided string as a table. See below.

然后我要做的是使用一个函数来拆分字符串，该函数将提供的字符串作为表返回。见下文。

ALTER FUNCTION [dbo].[fn_MVParam]
   (@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
  BEGIN
  DECLARE @chrind INT
  DECLARE @Piece nvarchar(100)
  SELECT @chrind = 1 
  WHILE @chrind > 0
    BEGIN
      SELECT @chrind = CHARINDEX(@Delim,@RepParam)
      IF @chrind  > 0
        SELECT @Piece = LEFT(@RepParam,@chrind - 1)
      ELSE
        SELECT @Piece = @RepParam
      INSERT  @Values(Param) VALUES(CAST(@Piece AS VARCHAR))
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
      IF LEN(@RepParam) = 0 BREAK
    END
  RETURN
  END

You can then reference the results in the where clause of your main query like so:

然后，您可以在主查询的 where 子句中引用结果，如下所示：

where someColumn IN(SELECT Param FROM dbo.fn_MVParam(@sParameterString,','))

I hope this you find this solution to be of use. Please feel free to pose any questions you may have.

我希望您发现此解决方案有用。请随时提出您可能有的任何问题。

Cheers,John

干杯，约翰

Answer 4

回答by CodeGrue

John Sansom and Ed Harper have great solutions. However, I was unable to get them to work when dealing with ID fields (i.e. Integers). I modified the split function below to CAST the values as integers so the table will join with primary key columns. I also commented the code and added a column for order, in case the delimited list order was significant.

John Sansom 和 Ed Harper 有很好的解决方案。但是，在处理 ID 字段（即整数）时，我无法让它们工作。我修改了下面的 split 函数以将值转换为整数，以便表将与主键列连接。我还注释了代码并添加了一个订单列，以防分隔的列表顺序很重要。

CREATE FUNCTION [dbo].[fn_SplitInt]
(
    @List       nvarchar(4000),
    @Delimiter  char(1)= ','
)
RETURNS @Values TABLE
(
    Position int IDENTITY PRIMARY KEY,
    Number int
)

AS

  BEGIN

  -- set up working variables
  DECLARE @Index INT
  DECLARE @ItemValue nvarchar(100)
  SELECT @Index = 1 

  -- iterate until we have no more characters to work with
  WHILE @Index > 0

    BEGIN

      -- find first delimiter
      SELECT @Index = CHARINDEX(@Delimiter,@List)

      -- extract the item value
      IF @Index  > 0     -- if found, take the value left of the delimiter
        SELECT @ItemValue = LEFT(@List,@Index - 1)
      ELSE               -- if none, take the remainder as the last value
        SELECT @ItemValue = @List

      -- insert the value into our new table
      INSERT INTO @Values (Number) VALUES (CAST(@ItemValue AS int))

      -- remove the found item from the working list
      SELECT @List = RIGHT(@List,LEN(@List) - @Index)

      -- if list is empty, we are done
      IF LEN(@List) = 0 BREAK

    END

  RETURN

  END

Use this function as previously noted with:

如前所述使用此函数：

WHERE id IN (SELECT Number FROM dbo.fn_SplitInt(@sParameterString,','))

Answer 5

回答by Jeff

ORACLE:

甲骨文：

The "IN" phrase (Ed's Solution) won't work against an Oracle connection (at least version 10). However, found this simple work-around which does. Using the dataset's parameter's tab turn the multi-value parameter into a CSV:

“IN”短语（Ed 的解决方案）不适用于 Oracle 连接（至少版本 10）。但是，找到了这个简单的解决方法。使用数据集的参数选项卡将多值参数转换为 CSV：

    :name =join(Parameters!name.Value,",")

Then in your SQL statement's WHERE clause use the instring function to check for a match.

然后在您的 SQL 语句的 WHERE 子句中使用 instring 函数来检查匹配。

    INSTR(:name, TABLE.FILENAME) > 0

Answer 6

回答by Bob Amy

I ran into a problem with the otherwise wonderful fn_MVParam. SSRS 2005 sent data with an apostrophe as 2 quotes.

我遇到了一个很棒的 fn_MVParam 的问题。SSRS 2005 发送带有单引号的数据作为 2 个引号。

I added one line to fix this.

我添加了一行来解决这个问题。

select @RepParam = replace(@RepParam,'''''','''')

My version of the fn also uses varchar instead of nvarchar.

我的 fn 版本也使用 varchar 而不是 nvarchar。

CREATE FUNCTION [dbo].[fn_MVParam]
   (
    @RepParam varchar(MAX),
    @Delim char(1)= ','
   )
RETURNS @Values TABLE (Param varchar(MAX)) AS
/*
  Usage:  Use this in your report SP 
     where ID in (SELECT Param FROM fn_MVParam(@PlanIDList,','))
*/

BEGIN

   select @RepParam = replace(@RepParam,'''''','''')
   DECLARE @chrind INT
   DECLARE @Piece varchar(MAX)
   SELECT @chrind = 1
   WHILE @chrind > 0
      BEGIN
         SELECT @chrind = CHARINDEX(@Delim,@RepParam)
         IF @chrind > 0
            SELECT @Piece = LEFT(@RepParam,@chrind - 1)
         ELSE
            SELECT @Piece = @RepParam
         INSERT @VALUES(Param) VALUES(@Piece)
         SELECT @RepParam = RIGHT(@RepParam,DATALENGTH(@RepParam) - @chrind)
         IF DATALENGTH(@RepParam) = 0 BREAK
      END
   RETURN
END

Answer 7

回答by grum

Modification of great John solution, solve:

大约翰解决方案的修改，解决：

"2 quotes" error

space after one of piece in parameter


ALTER FUNCTION [dbo].[fn_MVParam]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
//2 quotes error
set @RepParam = replace(@RepParam,char(39)+char(39),CHAR(39))
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1 
WHILE @chrind > 0
BEGIN
  SELECT @chrind = CHARINDEX(@Delim,@RepParam)
  IF @chrind  > 0
    SELECT @Piece = LEFT(@RepParam,@chrind - 1)
  ELSE
    SELECT @Piece = @RepParam
  INSERT  @Values(Param) VALUES(CAST(@Piece AS VARCHAR(300)))
  //space after one of piece in parameter: LEN(@RepParam + '1')-1
  SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam + '1')-1 - @chrind)
  IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

“2引号”错误

参数中的一块之后的空格


ALTER FUNCTION [dbo].[fn_MVParam]
(@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (Param nvarchar(4000))AS
BEGIN
//2 quotes error
set @RepParam = replace(@RepParam,char(39)+char(39),CHAR(39))
DECLARE @chrind INT
DECLARE @Piece nvarchar(100)
SELECT @chrind = 1 
WHILE @chrind > 0
BEGIN
  SELECT @chrind = CHARINDEX(@Delim,@RepParam)
  IF @chrind  > 0
    SELECT @Piece = LEFT(@RepParam,@chrind - 1)
  ELSE
    SELECT @Piece = @RepParam
  INSERT  @Values(Param) VALUES(CAST(@Piece AS VARCHAR(300)))
  //space after one of piece in parameter: LEN(@RepParam + '1')-1
  SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam + '1')-1 - @chrind)
  IF LEN(@RepParam) = 0 BREAK
END
RETURN
END

Answer 8

回答by Nilanka Sooriyabandara

Create the dataset for the list in the report
Right click the parameter and select available values
Select newly created dataset as dataset
Add the value passing to the stored procedure as value field
Add the description of the parameter to label field (If the parameter is customerID then label could be CustomerName ex.)
Finally, add following code to your stored procedure

为报表中的列表创建数据集
右键单击参数并选择可用值
选择新创建的数据集作为数据集
添加传递给存储过程的值作为值字段
将参数的描述添加到标签字段（如果参数是 customerID，则标签可以是 CustomerName ex。）
最后，将以下代码添加到您的存储过程中

declare @paramName AS NVARCHAR(500),

声明@paramName AS NVARCHAR(500),

IF RIGHT(@paramName, 1) = ',' BEGIN SET @paramName = LEFT((@paramName, LEN((@paramName)-1) END

Answer 9

回答by jon

It would probably be easier to add the multi values to a table first and then you can join or whatever you'd like (even with wildcards) or save the data to another table for later use (or even add the values to another table).

首先将多值添加到表中可能会更容易，然后您可以加入或任何您想要的（即使使用通配符）或将数据保存到另一个表以供以后使用（甚至将值添加到另一个表） .

Set the Parameter value via expression in the dataset:

通过数据集中的表达式设置参数值：

="SELECT DISTINCT * FROM (VALUES('" & JOIN(Parameters!SearchValue.Value, "'),('") & "')) 
AS tbl(Value)"

The query itself:

查询本身：

DECLARE @Table AS TABLE (Value nvarchar(max))

INSERT INTO @Table EXEC sp_executeSQL @SearchValue

Wildcard example:

通配符示例：

SELECT * FROM YOUR_TABLE yt 

INNER JOIN @Table rt ON yt.[Join_Value] LIKE '%' + rt.[Value] + '%'

I'd love to figure out a way to do it without dynamic SQL but I don't think it'll work due to the way SSRS passes the parameters to the actual query. If someone knows better, please let me know.

我很想找到一种没有动态 SQL 的方法，但由于 SSRS 将参数传递给实际查询的方式，我认为它不会起作用。如果有人知道更好，请告诉我。

Answer 10

回答by ScottLenart

Just a comment - I ran into a world of hurt trying to get an IN clause to work in a connection to Oracle 10g. I don't think the rewritten query can be correctly passed to a 10g db. I had to drop the multi-value completely. The query would return data only when a single value (from the multi-value parameter selector) was chosen. I tried the MS and Oracle drivers with the same results. I'd love to hear if anyone has had success with this.

只是一个评论 - 我遇到了一个痛苦的世界，试图让 IN 子句在与 Oracle 10g 的连接中工作。我认为重写的查询不能正确传递给 10g db。我不得不完全放弃多值。查询仅在选择单个值（来自多值参数选择器）时才会返回数据。我尝试了 MS 和 Oracle 驱动程序，结果相同。我很想听听是否有人在这方面取得了成功。

SQL 在 Reporting Services 中为单个参数传递多个值

提问by Scott and the Dev Team

回答by Ed Harper

回答by Scott and the Dev Team

回答by John Sansom

回答by CodeGrue

回答by Jeff

回答by Bob Amy

回答by grum

回答by Nilanka Sooriyabandara

回答by jon

回答by ScottLenart

相关推荐

最近更新

标签

SQL 在 Reporting Services 中为单个参数传递多个值

提问by Scott and the Dev Team

回答by Ed Harper

回答by Scott and the Dev Team

回答by John Sansom

回答by CodeGrue

回答by Jeff

回答by Bob Amy

回答by grum

回答by Nilanka Sooriyabandara

回答by jon

回答by ScottLenart

相关推荐

如何查看 SQL 数据库的所有授权？

SQL 故意在 Oracle 中引起 ORA-00600 异常

SQL 有没有办法同时 SELECT 和 UPDATE 行？

SQL 在 postgresql 中将行转换为字符串

相关推荐

最近更新

标签