SQL 如何在 ssrs 的 IIF 表达式中使用多个条件（带 AND）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22786212/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use multiple conditions (With AND) in IIF expressions in ssrs
提问by almond eyes
I just want to hide rows in SSRS report having Zero Quantity.. There are following multiple Quantity Columns like Opening Stock , Gross Dispatched,Transfer Out, Qty Sold, Stock Adjustment and Closing Stock etc.. I am doing this task by using following expression.
我只想隐藏 SSRS 报告中数量为零的行.. 有以下多个数量列，如开仓库存、总发货、转出、已售数量、库存调整和收盘库存等。我正在使用以下表达式执行此任务.
=IIF(Fields!OpeningStock.Value=0 AND Fields!GrossDispatched.Value=0 AND
Fields!TransferOutToMW.Value=0 AND Fields!TransferOutToDW.Value=0 AND
Fields!TransferOutToOW.Value=0 AND Fields!NetDispatched.Value=0 AND Fields!QtySold.Value=0
AND Fields!StockAdjustment.Value=0 AND Fields!ClosingStock.Value=0,True,False)
But by using this expression in row visibility, report hides all the rows except Totals Row. Even though report should show rows having Quantities of above mentioned columns. Total values are shown correct.
但是通过在行可见性中使用此表达式，报表将隐藏除总计行之外的所有行。即使报告应显示具有上述列数量的行。总值显示正确。
Note:I set this row visibility expression on Detail Row.
注意：我在 Detail Row 上设置了此行可见性表达式。
Without using expression result is as following.
不使用表达式结果如下。
For the first 2 rows all the quantities are 0 (ZERO), i want to hide these 2 rows.
对于前 2 行，所有数量都是 0（零），我想隐藏这 2 行。
How can i fix this problem or which expression must i use to get required results?
我该如何解决这个问题或者我必须使用哪个表达式来获得所需的结果？
Thanks in Advance for help.
提前感谢您的帮助。
回答by PeterRing
Could you try this out?
你能试试这个吗？
=IIF((Fields!OpeningStock.Value=0) AND (Fields!GrossDispatched.Value=0) AND
(Fields!TransferOutToMW.Value=0) AND (Fields!TransferOutToDW.Value=0) AND
(Fields!TransferOutToOW.Value=0) AND (Fields!NetDispatched.Value=0) AND (Fields!QtySold.Value=0)
AND (Fields!StockAdjustment.Value=0) AND (Fields!ClosingStock.Value=0),True,False)
Note: Setting Hidden to False will make the row visible
注意：将 Hidden 设置为 False 将使该行可见
回答by JC Ford
You don't need an IIF() at all here. The comparisons return true or false anyway.
你在这里根本不需要 IIF() 。无论如何，比较都会返回 true 或 false。
Also, since this row visibility is on a group row, make sure you use the same aggregate function on the fields as you use in the fields in the row. So if your group row shows sums, then you'd put this in the Hidden property.
此外，由于此行可见性位于组行上，因此请确保对字段使用与在行中的字段中使用的聚合函数相同的聚合函数。因此，如果您的组行显示总和，那么您可以将其放在 Hidden 属性中。
=Sum(Fields!OpeningStock.Value) = 0 And
Sum(Fields!GrossDispatched.Value) = 0 And
Sum(Fields!TransferOutToMW.Value) = 0 And
Sum(Fields!TransferOutToDW.Value) = 0 And
Sum(Fields!TransferOutToOW.Value) = 0 And
Sum(Fields!NetDispatched.Value) = 0 And
Sum(Fields!QtySold.Value) = 0 And
Sum(Fields!StockAdjustment.Value) = 0 And
Sum(Fields!ClosingStock.Value) = 0
But with the above version, if one record has value 1 and one has value -1 and all others are zero then sum is also zero and the row could be hidden. If that's not what you want you could write a more complex expression:
但是在上面的版本中，如果一条记录的值为 1，一条记录的值为 -1，而所有其他记录都为零，则 sum 也为零，并且可以隐藏该行。如果这不是你想要的，你可以写一个更复杂的表达式：
=Sum(
IIF(
Fields!OpeningStock.Value=0 AND
Fields!GrossDispatched.Value=0 AND
Fields!TransferOutToMW.Value=0 AND
Fields!TransferOutToDW.Value=0 AND
Fields!TransferOutToOW.Value=0 AND
Fields!NetDispatched.Value=0 AND
Fields!QtySold.Value=0 AND
Fields!StockAdjustment.Value=0 AND
Fields!ClosingStock.Value=0,
0,
1
)
) = 0
This is essentially a fancy way of counting the number of rows in which any field is not zero. If every field is zero for every row in the group then the expression returns true and the row is hidden.
这本质上是一种计算任何字段不为零的行数的奇特方法。如果组中每一行的每个字段都为零，则表达式返回 true 并且该行被隐藏。
回答by roger bulawan
Here is an example that should give you some idea..
这是一个应该给你一些想法的例子..
=IIF(First(Fields!Gender.Value,"vw_BrgyClearanceNew")="Female" and
(First(Fields!CivilStatus.Value,"vw_BrgyClearanceNew")="Married"),false,true)
I think you have to identify the datasource name or the table name where your data is coming from.
我认为您必须确定数据来源的数据源名称或表名称。
