optional parameter in ssrs 2008
Hello :)
I have a parameter that provides a dropdown list in the report, and I would like to make it optional, that is, I want a report to run even if I don't select anything from the list, and to have a Null checkbox next to it, that regulates if the parameter is
optional or not. The parameter is declared in stored procedure and is = null, and in the query is WHERE (p.somename = @parameter or @parameter is Null), also in the parameter properties in the ssrs I checked "allow blank value" and "allow null value",
for the available values it's set to "get values from the query" (second dataset) and default values are set to "no default values" (in ssrs 2008 there is no option for null for the default values like in ssrs 2005).
When I run the report I have to select the values from the dropdown list which I want it optional, to run the report whether or not anything is selected. Can anyone help?
July 1st, 2010 5:34pm
I think you need to set parameters' defualt values.Have fun with Reporting Service
Free Windows Admin Tool Kit Click here and download it now
July 1st, 2010 5:51pm
hi, thanks for the fast reply
i'm using ssrs 2008, and when i try to "specify values" with the isNothing function like
IsNothing(Parameters!User.Value) or Iif(Parameters!User.Value is Nothing,Nothing,Parameters!User.Value)
it raises an error: "the expression that references the parameter does not exist in the parameters collection, letters in the names of parameters must use the correct case"
although it exists in the param coll it's listed in the Category field in the Expression tab, and report and dataset parameters are correctly mapped. I even tried in the dataset properties for the parameters tab to put expression in the "parameter value"
and it's the same...
July 2nd, 2010 1:50pm
Hi powerwind,
As you mentioned, if the parameter available values is set to "get values from the query", the "allow null value" option will not have a Null checkbox next to it. To workaround this issue, if possible, to contruct the parameter dataset to include a special
value for some special cases like this:
select col from dataset1
Union all
select 'Optional' -- or other word symbol to indicate this is an optional value.
then, change the sql statement somename=@parameter or @paramter = 'Optional' . So, the parameter will have a value called "Optional" in its dropdownlist, to tell users that they can choose this value to
achieve that the parameter is null.
thanks,
Jerry
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2010 6:58am
Hi Jerry,
Thank you very much for your reply it was very helpfull, and it worked.
I have another similar question, if it's not a bother, i have another parameter that is set from different columns in the table i.e A,B,C,D, and the parameters available values are not filled from the dataset but they are "specified", and i have added the
(Null) value and label in the "specify values" tab, amongst the A,B,C,D values, for the same optional functionality of the parameter. Also, i've added the (Null) value in the default values tab.
In the stored procedure this parameter is set to be =null.
How can i achieve this parameter to be optional (i've tried similarly like for the regular dropdown parameter that feeds on dataset query but although it shows that optional functionality in the drop down it doesn't show data when selected).
Thanks,
powerwind :)
July 6th, 2010 5:44pm
Thanks so much for this, Jerry! Based on what I found at
http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/85d0bd42-8243-480a-aedb-1a1f8004202d, I was worried that multivalued parameters were going to be my only option. However, your solution gives me the workaround I need.
Now if the report parameter options only worked the way the dialog boxes make it seem like they should.
Free Windows Admin Tool Kit Click here and download it now
February 23rd, 2011 8:07pm