Selection List or All Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Selection List or All Records

Author

Topic

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-31 : 09:18:44

I have a table employees (greatly simplified):
Emp_Number
Emp_Department
Emp_Name

Select * From Employees
Where Emp_Dept IN (@Dept)

In MSRS I have a second dataset to select the departments to report:

Select Distinct Emp_Department From Employees

In my report I was putting the selected departments at the top of the first page so the user knew exactly what departments should be on the report. My problem is that the number of departments no longer fit nicely across a page heading. What I would like to do is allow the user to NOT select anything, defaulting to NULL in the department selection but then select all departments.

Is there a way to create the SQL statement that effectively omits the WHERE clause if a department not selected?

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 09:28:20

Select * From Employees
Where Emp_Dept =@Dept or @Dept is null

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-31 : 09:47:25

Madhivanan,

The department will never be null on the employee table. I want the user to ignore the parameter selection which defaults to NULL and then I want all records selected but if they select one or more departments, to select only those. Basically I want to make a report parameter optional.

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 09:51:49

See the code I posted. If you want to omit the parameter, just make it null

Madhivanan

Failing to plan is Planning to fail

JCirocco
Constraint Violating Yak Guru

392 Posts

Posted - 2009-08-31 : 10:05:19

Thanks. When I first read it, it looked to my brain like:

"Where Emp_Dept =@Dept or Emp_Dept is null"

Sorry for the confusion and thanks for the help.

John

"The smoke monster is just the Others doing barbecue"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 10:09:33

quote:
Originally posted by JCirocco

Thanks. When I first read it, it looked to my brain like:

"Where Emp_Dept =@Dept or Emp_Dept is null"

Sorry for the confusion and thanks for the help.

John

"The smoke monster is just the Others doing barbecue"

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources