How to add Optional Parameters for SQL Reporting Service 2005
Hi friends, I am developing reports using SQL Server 2005 Reporting service I want to pass optional parameters to Report using dropdown I filled dataset using EmpId and EmpName. and assigned this dataset to query the values. I checked properties for Report Parameters of Allow Null, Allow Blank values Even i checked this properties, it enforces me to Enter some value for dropdown while running or previewing the report I don't want toenforce theuser that value must be selected. In short, How we can able to pass multiple parameters which are not mandatory. Pls reply me ASAP Any suggestion is appreciated Thanks in Advance. Regards Suds
June 14th, 2007 9:54am

hmm.... if i set the parameter to be multivalue, there'll be error if i want to set it to Allow Null... my solution is if user does not want to select any specific value, then they can use select all.... i don't know how to put select all as the default value... maybe someone can suggest this... if you found out, pls share ya....
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2007 10:40am

Add a default value of "All" and use this value with in your expressions/queries to allow for a catch all situationeg: with in the sql where clause add the following:and case @empid = 'All' then table.emp_id else @empid = table.emp_idIt is a little bit messy to write but I believe it gives you the most control over the report.Sam Vella.
June 14th, 2007 10:40am

minority80 wrote: i don't know how to put select all as the default value... maybe someone can suggest this...Report -> ParametersSelect the parameter you want to add a default value toSelect the Non-Queried radio ButtonEnter "All" without the quotes into the text boxSam Vella
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2007 10:42am

Hi All. I have same problem like Suds. Is it possible to do or not? Thanks in advance Vera
November 6th, 2009 6:57am

setting the parameter to 'multi-value' and then defaulting the parameter to 'All' as Sam suggested will have the same result as what Suds was after.
Free Windows Admin Tool Kit Click here and download it now
November 6th, 2009 8:50am

woww.. great. I miss overlook on it. Thanks Numbatau for it :) regards, Vera
November 6th, 2009 9:24am

I have same issue, can someone provide suggestion to me , for the Optional Parameter implementation in SSRS 2005 ? Thanks.
Free Windows Admin Tool Kit Click here and download it now
February 18th, 2011 7:43am

I don't get it. The solution cited above works fine in the query but when the preview is run the results are completely different. I am at a loss to explain this. In the query view the results are as expected, all the rows are returned. In the preview and in the deployed report however NOTHING is returned. This is not logical. Edward R. Joell MCSD MCDBA
April 11th, 2011 5:10pm

For anyone else reading this. When the above occurred to prevent this solution from working for me, I changed my approach. I created a parameter dodic which I defaulted to "All DoDICs". Then I set a filter on the Dataset for the field DoDIC where by the field Fields!DoDIC.Value = Iif(Parameters!dodic.Value = "All DoDICs", Fields!DoDIC.Value, Parameters!dodic.Value) This accomplished the same thing on the report side rather than the database side.Edward R. Joell MCSD MCDBA
Free Windows Admin Tool Kit Click here and download it now
April 14th, 2011 10:42am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics