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

  • 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

    Thursday, January 6, 2011 9:26 PM

Answers

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

    • Marked as answer by Gary_MSDN Monday, January 10, 2011 10:09 PM
    Monday, January 10, 2011 5:52 AM

All replies

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

    • Marked as answer by Gary_MSDN Monday, January 10, 2011 10:09 PM
    Monday, January 10, 2011 5:52 AM
  • Hi Challen, appreciate your help. Gary
    Monday, January 10, 2011 10:11 PM