正如@StevenWhite 所指出的，您可能需要重新考虑您的方法，但如果您真的想这样做.

As @StevenWhite pointed out, you probably need to rethink your approach but if you really want to do this.

你需要在你的报告中添加一个额外的参数(你可以在一切正常后隐藏它)

You need to add an additional parameter to your report (you can hide it once it's all working OK)

此参数的数据集将是您现有的动态 sql 代码，但只是 WHERE 子句部分，因此在数据集查询的末尾执行 SELECT @where 之类的操作.

The dataset for this parameter would be your existing dynamic sql code, but just the WHERE clause part, so hte end of the dataset query just do something like SELECT @where.

因此，一旦填充了其他参数，就会填充这个新参数，它的值将是您的 where 子句.然后，您可以将其作为参数传递给适用的其他数据集.

So, this new parameter will be populated once the other parameters have been populated and it's value will be your where clause. You can then pass that as a parameter to your other datasets where applicable.

如果这没有意义，请告诉我，我会尽快给出更完整的答案.

If that doesn't make sense, let me know and I'll do a more complete answer soon.

更完整的答案

在这个例子中，我使用了 hte Northwind 示例数据库

In this example I've used hte Northwind sample database

我展示了如何生成一个 WHERE 子句，该子句可用于另一个数据集(或任意数量).在这个例子中，我只会用一个来做.

I show how to generate a WHERE clause that can be used in another dataset (or as many as you like). In this exmaple I'll just do it with one.

我将有两个参数用于 where 子句选择

I will have two parmeters for the where clause selections

• ProductID 列表
• 列表或员工 ID

我们最终的数据集查询将是动态 sql，它形成这样的语句..

Our final dataset query will be dynamic sql that forms the statement something like this..

SELECT 
       o.*
       , d.Discount, d.ProductID, d.Quantity, d.UnitPrice
    FROM Orders o
       JOIN [Order details] d on o.OrderID = d.OrderID
    WHERE ProductID in (11,42,72) and EmployeeID IN (3,5,6)

这是我采取的步骤:

创建了一个新的空白报告向 Northwind 数据库添加了一个连接

Created a new blank report Added a conncetion to the Northwind database

创建了一个名为 dsProd 的数据集将此数据集的查询设置为 SELECT ProductID, ProductName FROM Products ORDER BY ProductName

Created a dataset called dsProd Set the query for this dataset to be SELECT ProductID, ProductName FROM Products ORDER BY ProductName

创建了一个名为 dsEmployee 的数据集将此数据集的查询设置为 SELECT EmployeeID, FirstName FROM Employees ORDER BY FirstName

Created a dataset called dsEmployee Set the query for this dataset to be SELECT EmployeeID, FirstName FROM Employees ORDER BY FirstName

添加了一个名为 pProd 的参数设置参数为Mutil-value将可用值设置为 dsProd 数据集将值字段设置为 ProductID将标签字段设置为 ProductName

Added a parameter called pProd Set the parameter to be Mutil-value Set the available values to the dsProd dataset Set the Value field to ProductID Set the Label field to ProductName

添加了一个名为 pEmp 的参数设置参数为Mutil-value将可用值设置为 dsEmployee 数据集将值字段设置为 EmployeeID将标签字段设置为 FirstName

Added a parameter called pEmp Set the parameter to be Mutil-value Set the available values to the dsEmployee dataset Set the Value field to EmployeeID Set the Label field to FirstName

添加了一个名为 pWHERE 的最终参数将此的默认值(指定值)设置为以下表达式

Added a final parmater called pWHERE Set the default value (Specify values) for this to the following Expression

="WHERE ProductID IN (" & Join(Parameters!pProd.Value, ",") & ") " &
" AND EmployeeID IN (" & JOIN(Parameters!pEmp.Value, ",") & ")"

接下来添加了一个名为 dsResults 的数据集将数据集查询设置为

Next added a datset called dsResults Set the dataset Query to

DECLARE @SQL varchar (1000)

SET @SQL = 'SELECT 
       o.*
       , d.Discount, d.ProductID, d.Quantity, d.UnitPrice
    FROM Orders o
       JOIN [Order details] d on o.OrderID = d.OrderID '
       + @pWHERE

EXEC (@SQL)

最后，我在报告中添加了一个表，指向 dsResults 以显示输出.

Finally I added a table to the report pointing to dsResults to display the output.

现在，当您选择员工和产品时，where 子句在 pWHERE 参数中构造并传递到最终查询的数据集.

Now, when you choose the employees and products, the where clause is constructed in the pWHERE parameter and passed to the final query's dataset.

注意: 回到我原来的观点，重申@StevenWhite 所说的话，所有这些可能都是不必要的.在这种简单的情况下，您可以简单地将最终数据集查询设置为

NOTE: Going back to my original point, reiterating what @StevenWhite was saying, all this is probably unneccessary. In this simple case you could have simply set the final dataset query to

选择o.*, d.Discount, d.ProductID, d.Quantity, d.UnitPriceFROM 订单 oJOIN [订单详情] d on o.OrderID = d.OrderIDWHERE ProductID in (@pProd) 和 EmployeeID IN (@pEmp)

SELECT o.* , d.Discount, d.ProductID, d.Quantity, d.UnitPrice FROM Orders o JOIN [Order details] d on o.OrderID = d.OrderID WHERE ProductID in (@pProd) and EmployeeID IN (@pEmp)

这会做完全相同的工作，它会更快，你根本不需要 pWHERE 参数，它会更可靠，上面的例子在第一次运行后可能会出现问题 pWHERE 参数可能无法正确刷新.

This would do exactly the same job, it would be quicker, you would not need the pWHERE parameter at all and it would be more reliable, the example above will probably have issues after the first run as the pWHERE parameter may not refresh correctly.

无论如何，这取决于你，但从长远来看，以正确的方式做总是更快..

Anyway, that's up to you but doing it the right way is always quicker in the long run..