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

sql sql-server reporting-services iif
almond eyes picture almond eyes · Apr 1, 2014 · Viewed 142.9k times · Source

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.

enter image description here

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

Thanks in Advance for help.

Answer

PeterRing picture PeterRing · Apr 1, 2014

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

Related questions

SSRS report not displaying data

I have just created an SQL Server 2005 SSRS report and the data is not being displayed in the Preview pane. The dataset is correctly populated from a stored procedure against a String parameter. I can execute it in the Data …

Read More
"An item with the same key has already been added" Error on SSRS When Trying to Set Dataset

When i try to set the Dataset in SSRS IDE, i get the error you see in the snapshot. The query works totally fine in SQL Server Management Studio, i wonder where did i go wrong?! The connection to DB …

Read More
Where does Reporting Services store its log files

The most relevant google result seems to indicate that in order to access the logs we have to depoly our own log tables to the database and make Reporting Services write to it. Simply put: surely there must be plain …

Read More