CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008reportingservices-2005
Dhana
Dhana

Reputation: 1658

how to set SSRS Report parameters to be optional i.e Non-Mandatory field in SSRS 2005

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

Upvotes: 14

Views: 65651

Answers (5)

Pratik
Pratik

Reputation: 1512

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.

Upvotes: 4

Sri Harsha
Sri Harsha

Reputation: 91

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)

Upvotes: 1

Daniel Ferreira
Daniel Ferreira

Reputation: 86

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.

Upvotes: 1

jjt
jjt

Reputation: 135

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)

Upvotes: 3

djangojazz
djangojazz

Reputation: 13242

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.

Upvotes: 22

Related Questions