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

Question

I 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.

enter image description here

How can I fix this problem, or which expression must I use to get required results?

Have you tried to use OR instead of AND, so if any of the fields stated has value, then it should be visible. — jelliaes, Jul 07 '16 at 03:41

score 19 · Answer 1 · answered Apr 01 '14 at 13:58

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

JC Ford · Answer 2 · 2014-04-01T15:53:35.467

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.

If you aren't doing any sort of grouping, then I'm not sure where the Group Row comes into play. Is there a "Detail Row" where you can put the expression? — JC Ford, Apr 02 '14 at 12:31
I am doing Grouping before Style Code. And Yes there is a "Detail Row" where i can put the expression.. but which expression should i use? — almond eyes, Apr 03 '14 at 05:31

score 0 · Answer 3 · edited Jul 07 '16 at 03:33

0

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.

edited Jul 07 '16 at 03:33

Theresa

3,515
10
42
47

answered Jul 07 '16 at 02:59

roger bulawan

167
1
1
11

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

3 Answers3

Linked