CODEWITHSUNDEEP
reporting-servicessql-server-2012ssrs-2012
gemini6609
gemini6609

Reputation: 332

Display Column if Count of Boolean Parameters is Greater than an Amount in SQL Server Report Viewer

I want to display a column in a MSSQL Report, if more than 2 out of 4 boolean report parameters are true. Below is the attempt I made to create a visibility expression for this condition but it causes an error. Any suggestions would be greatly appreciated.

=IIF(COUNT(IIF(Parameters!HideQuantityOnHand.Value = "False" OR Parameters!HideQuantityOnOrder.Value = "False" OR Parameters!HideCostPerItem.Value = "False" OR Parameters!HideRetailPerItem.Value = "False")) < 3, true)

Upvotes: 0

Views: 358

Answers (3)

gemini6609
gemini6609

Reputation: 332

As shown in the example below I found that I had to join three parameters with AND and then specify true or false for the visibility in the expression followed by IIF for the expression comparison. See below.

=IIf(Parameters!HideQuantityOnHand.Value="False" AND
Parameters!HideQuantityOnOrder.Value="False" AND
Parameters!HideRetailPerItem.Value="False" AND
Parameters!HideCostPerItem.Value="False",true,
IIf(Parameters!HideQuantityOnOrder.Value="False" AND
Parameters!HideCostPerItem.Value="False",true,
IIf(Parameters!HideCostPerItem.Value="False" AND
Parameters!HideQuantityOnHand.Value="False",true,
IIf(Parameters!HideRetailPerItem.Value="False" 
AND Parameters!HideQuantityOnHand.Value="False",true,false))))

Upvotes: 0

Alan Schofield
Alan Schofield

Reputation: 21738

I think this is what you are after. 

=(
    IIF(Parameters!HideQuantityOnHand.Value ,1,0) + 
    IIF(Parameters!HideQuantityOnOrder.Value ,1,0) + 
    IIF(Parameters!HideCostPerItem.Value ,1,0) + 
    IIF(Parameters!HideRetailPerItem.Value ,1,0) 
    ) < 3

Basically we evaluate each parameter and convert true to 1, we then sum all the values and check they are < 3 in which case we return True to the hidden property (so it's not visible).

Upvotes: 0

SS_DBA
SS_DBA

Reputation: 2423

You can't Count because of the OR in the statement. It just doesn't look right. So all you need is 1 to be false, assuming that the others are true. See if this works for you.

UPDATED See if this is what you wanted. I think I got all of the combinations.

            =IIF((Parameters!HideQuantityOnHand.Value = "False" AND Parameters!HideQuantityOnOrder.Value = "False") OR 
            (Parameters!HideQuantityOnHand.Value = "False" AND Parameters!HideCostPerItem.Value = "False") OR 
         (Parameters!HideQuantityOnHand.Value = "False" AND Parameters!HideRetailPerItem.Value = "False")
        OR (Parameters!HideQuantityOnOrder.Value = "False" AND Parameters!HideCostPerItem.Value = "False") OR
     (Parameters!HideQuantityOnOrder.Value AND Parameters!HideRetailPerItem.Value = "False") OR
(Parameters!HideCostPerItem.Value = "False" AND Parameters!HideRetailPerItem.Value = "False") , true, false)

Upvotes: 1

Related Questions