How to use multiple conditions With AND in IIF expressions in ssrs

Question

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        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   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      How can i fix this problem or which expression must i use to get required results   Thanks in Advance for help

User · Answer

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

User · Answer

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

User · Answer

You don t need an IIF   at all here  The comparisons return true or false anyway   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    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    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

[sql] How to use multiple conditions (With AND) in IIF expressions in ssrs

Examples related to sql

Examples related to sql-server

Examples related to reporting-services

Examples related to iif