使用存储过程的 SSRS 多值参数

问题描述：

我正在处理使用包含几个参数的存储过程的 SSRS 报告.我在使用两个参数时遇到问题，因为我希望可以选择多个项目.

I am working on a SSRS report that uses a stored procedure containing a few parameters. I am having problems with two of the parameters because I want to have the option of selecting more than one item.

这是我所拥有的精简版:

Here's a condensed version of what I have:

CREATE PROCEDURE [dbo].[uspMyStoredProcedure] 
  (@ReportProductSalesGroupID AS VARCHAR(MAX) 
  ,@ReportProductFamilyID AS VARCHAR(MAX)
  ,@ReportStartDate AS DATETIME
  ,@ReportEndDate AS DATETIME)

--THE REST OF MY QUERY HERE WHICH PULLS ALL OF THE NEEDED COLUMNS

WHERE DateInvoicedID BETWEEN @ReportStartDate AND @ReportEndDate
AND ProductSalesGroupID IN (@ReportProductSalesGroupID)
AND ProductFamilyID IN (@ReportProductFamilyID)

当我尝试只运行存储过程时，如果我只为 @ReportProductSalesGroupID 输入 1 个值和 1 个值 @ReportProductFamilyID，我只会返回值.如果我尝试输入两个 SalesGroupID 和/或 2 个 ProductFamilyID，它不会出错，但我什么都不返回.

When I try to just run the stored procedure I only return values if I enter only 1 value for @ReportProductSalesGroupID and 1 value @ReportProductFamilyID. If I try to enter two SalesGroupID and/or 2 ProductFamilyID it doesn't error, but I return nothing.

-- Returns data
EXEC uspMyStoredProcedure 'G23',     'NOF',     '7/1/2009', '7/31/2009'

-- Doesn't return data
EXEC uspMyStoredProcedure 'G23,G22', 'NOF,ALT', '7/1/2009', '7/31/2009'

在 SSRS 中，我收到一条错误消息:

In SSRS I get an error that says:

',' 附近的语法不正确

Incorrect syntax near ','

看起来 , 分隔符被包含在字符串中，而不是分隔符

It appears that the , separator is being included in the string instead of a delimiter

答

你需要三件事:

在 SSRS 数据集属性中，将多值参数作为逗号分隔的字符串传递给存储过程

In the SSRS dataset properties, pass the multi-value param to the stored procedure as a comma-delimited string

=Join(Parameters!TerritoryMulti.Value, ",")

在 Sql Server 中，您需要一个表值函数，可以将逗号分隔的字符串拆分回迷你表(例如见这里).自 SQL Server 2016 起，您可以为此使用内置函数 STRING_SPLIT

In Sql Server, you need a table-value function that can split a comma-delimited string back out into a mini table (eg see here). edit: Since SQL Server 2016 you can use the built-in function STRING_SPLIT for this

在存储过程中，有一个像这样的 where 子句:

In the stored procedure, have a where clause something like this:

WHERE sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,','))

... 其中 ufnSplit 是第 2 步中的拆分函数.

... where ufnSplit is your splitting function from step 2.

(我的博客文章中的完整步骤和代码较少失败的 SSRS 多值参数'):

(Full steps and code in my blog post 'SSRS multi-value parameters with less fail'):

使用存储过程的 SSRS 多值参数

相关推荐