本文介绍了在SSRS中,如何使用多值参数创建动态"where"条件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

首先,我将提及我的工作以更好地理解我的问题

First I will mention a bit of my work to better understand my question

因此,在SSRS中,我有多个参数,所有参数都使用= Join(Parameter!x.value,,"),然后在此过程中,我将所有参数连接起来,创建了一个where条件.

So, in SSRS, I have multiple parameters which all are sent to procedure using=Join(Parameter!x.value,",") then in the procedure I create a where condition by concatenating all the parameters.

类似这样的东西:

> Set @where =
> 'and Table.Column in(''' + replace(RTRIM(LTRIM(@Parameter1)),',', ''',''')
> + ''')' + '
> and Table.Column in(''' + replace(RTRIM(LTRIM(@Parameter2)),',', ''',''')
> + ''')' + '
> and Table.Column in(''' + replace(RTRIM(LTRIM(@Parameter3)),',', ''',''') +
> ''')' + '

我的问题是:如何在SSRS中将此@where创建为数据集/参数.我想这样做,以便我可以在详细报告中使用相同的@where,该报告连接到主要摘要报告并在我单击摘要报告中的数字时传递到我的详细报告.我不想在我的详细信息rdl中再次创建相同的where条件

My question is: How can I create this @where in SSRS as a dataset/parameter. I want to do this so that I can use the same @where in a detail report which is connected to main summary report and passed to my detail report when I click on a number in my summary report. I don't want to recreate the same where condition again in my detail rdl

推荐答案

正如@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.

更多完整答案

在此示例中,我使用了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列表
  • 列表或EmployeeID

我们最终的数据集查询将是动态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 reportAdded a conncetion to the Northwind database

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

Created a dataset called dsProdSet 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 dsEmployeeSet 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 pProdSet the parameter to be Mutil-valueSet the available values to the dsProd datasetSet the Value field to ProductIDSet the Label field to ProductName

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

Added a parameter called pEmpSet the parameter to be Mutil-valueSet the available values to the dsEmployee datasetSet the Value field to EmployeeIDSet the Label field to FirstName

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

Added a final parmater called pWHERESet 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 dsResultsSet 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.

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

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.折扣,d.ProductID,d.Quantity,d.UnitPrice 来自订单o 在[O.OrderID = d.OrderID]上加入[订单明细] d ProductID输入(@pProd)和EmployeeID输入(@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..

这篇关于在SSRS中,如何使用多值参数创建动态"where"条件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-20 12:22
查看更多