Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dropdown parameters not required

Status
Not open for further replies.

henslecd

MIS
Apr 21, 2003
259
US
SQL 2005 RS

I have 5 dropdown parameters, of which only one is required.

All 5 are populated by a dataset.

I have checked allow nulls and blanks for the 4 that are not required.

The SP that I wrote is written in dynamic sql, so it tests for null values for all params, and only includes that part of the where clause if the param isn't null.

My goal is that a user can select any of the dropdowns, or none of them, and still get the data they want. Selecting none of them would return all the data.

Is there a more elegant way to do this? I don't think Filters will work because the fields I am narrowing down with aren't a part of the final report.

Thanks for your help.

 
yes, I use the following approach:
1) Use default values for my input parameters in my stored procedure that are optional and include a CASE statement to check for the default value. for example:
Code:
CREATE PROCEDURE usp_proc1
  @Param1 varchar(5) = 'All'

AS
  SELECT field1, field2, field3
    FROM table1
   WHERE field4 LIKE CASE WHERE @Param1 = 'All' THEN '%' ELSE @Param1
GO
2) In Reporting Services I define my datasets that are optional as follows:
Code:
SELECT 'All' AS field1

UNION

SELECT field1
  FROM table1
3) In Report Parameters, for the optional parameters I assign non-queried default values of 'All'

What will happen is when you render the report your optional drop-downs will have a default value of 'All'; when you submit the report and the default value of 'All' is passed the stored procedure will see this and select using the '%' wildcard; however, if you pass in a value other than the default value the stored proc will select on the value of the parameter and not the wildcard. This approach works great for me as I have a handfull of reports that use it. Does this make sense??
 
Yeh I already have that. The problem is that I don't want to select anything. It would be like the textbox with the checkbox beside it, enabling it or disabling. I guess I could Union Select null as field1, but I would have thought that 2005 RS would not make dropdown params required.

Thanks for the help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top