Filtering records from SharePoint list in SQL Server Reporting Services.

How to get all records from database, if selected value from drop down is selected to All

For example given below.

Here in our case we have three values for the Status.  .i.e. All, Open and Close.

We have to filter records according the parameters selected from the dropdown.

Step 1) Select your data set from the Report Data Toolbox.

2)   Right Click on your data set and select data set Properties.
3)   Select option filters in the opened dialog box.

 

4) Click Add to Create new filter.

5) Click on fx button circled below.

In the opened window you can write the following formula as show below.

=IIF(Parameters!CaseStatus.Value="All",true,Fields!CaseStatus.Value=Parameters!CaseStatus.Value)

Here the Parameters!CaseStatus.Value stands for the “Select test case” dropdown selected value.

Explanation: In this formula IIF(Parameters!CaseStatus.Value="All",true this part

explains the if we set the value as true in case where the CaseStatus=All, then it brings all the records else it will bring the record of the selected part, it can be Open or Close.

Then

 

Set type as Boolean and the Operator as = and Value  to  true.

  

Other Formulas

1) =IIF(Parameters!CrimeDescription.Value="",true,Fields!CrimeDescription.Value=Parameters!CrimeDescription.Value)

Explanation:  If you want to skip the field from the filter if the textbox is empty you can use the above formula.