12

Please help me to set the SSRS Report parameters to be optional i.e Non-Mandatory field in SSRS 2005.

JackLock
  • 1,168
  • 1
  • 13
  • 26
Dhana
  • 1,618
  • 4
  • 23
  • 39
  • 1
    There is **no proprty call optional** but you can DEFAULT value in optional parameters. if your datasource is store proc that would make it easy to develop. – Hiten004 Mar 11 '13 at 16:32
  • As @Hiten004 suggested there is nothing like optional parameter. What you can do is modify your select statement to handle a case where parameter is NULL. Can you post your select statement ? – JackLock Mar 11 '13 at 16:46

5 Answers5

21

Set the parameter to 'Allow null value' under General section. Null according to SSRS means it does not have to be set, thus making it optional.

Then post something like this in the predicate of your SQL syntax:

Where column = isnull(@Variable, column)

This lets the dataset know that if the variable is set then use it, else have an operator to have the value equal itself. I am certain this works in SSRS 2008 and 2012, not sure of 2005 but I would assume it may be there.

djangojazz
  • 14,131
  • 10
  • 56
  • 94
4

As Hiten suggested there is nothing exactly we can call as optional parameters but if you want users to see this parameter as optional, use formula or case statements.

Parameterized stored procedures with some defaults or null value can be used to achieve the same goal.

approxiblue
  • 6,982
  • 16
  • 51
  • 59
Pratik
  • 1,472
  • 7
  • 20
  • 36
3

Further to djangojazz (dig the name, btw) - If the dataset is based on a SharePoint list (which doesn't support Query parameters, afaik), you can use the following formula in the Value box of the Filters section of the Properties dialog in whichever data region (e.g. Tablix) is invoking the parameter:

=IIf(IsNothing(Parameters!myParam.Value),Fields!myField.Value,Parameters!myParam.Value)

jjt
  • 125
  • 1
  • 10
1

Neither of these answers helped, nevertheless I found the solution here:

  1. Open the report for editing in Visual Studio.
  2. Expand the Parameters node and rename the affected parameter to ParameterName1.
  3. Set AllowBlank and Nullable to True if not already set.
  4. Deploy the report.
  5. Rename the parameter back to ParameterName.
  6. Deploy the report.
KyleMit
  • 30,350
  • 66
  • 462
  • 664
Daniel Ferreira
  • 86
  • 2
0

After adding a parameter to your report and checking 'Allow Null Value',you can then add a filter to the dataset where the below expression is added to the value field for the filter

=IIf(IsNothing(Parameters!Param.Value),Fields!Field.Value,Parameters!Param.Value)
Pedram
  • 6,256
  • 10
  • 65
  • 87
Sri Harsha
  • 81
  • 3