How to config sql report filter and make it return all data when parameter is null

Question

One sql report was created by visual studio 2008 and it has one filter. It is working fine. but I want to config this filter in visual studio to return all data if input parameter value is null or user select null for this parameter. Most of time, if parameter is null, search result is empty. Instead, I want to return all data. How to do it  and any suggestion for it.

thanks

Gary

Answer 1

 Hi Gary,

You could utilize IIF function in the reporting services to achieve this requirement, in the filter's dialogbox, click Add button to add a filter, type in following information:

Expression :=Fields!Datafields.Value

Operator: =

Value: =IIF(Isnothing(Parameters!ParaName.Value),Fields!Datafields.Value,Parameters!ParaName.Value)

If you have any question, please feel free to ask.

Thanks,

---

Challen Fu [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Challen, appreciate your help. Gary