October 4, 2010 at 2:29 pm
I have a report which I would like to have a nullable parameter to filter the data set I am pulling in. I'm not exactly sure how to do this. Could someone give me some suggestions? Thanks!
1. My parameter is @server_name and has a default value of null.
2. I would like to pull something like this in:
if @server_name is null
begin
SELECT server_name
FROM JobReports
WHERE (enter_date BETWEEN @STARTDATE AND @ENDDATE)
ORDER BY server_name
end
else
SELECT server_name
FROM JobReports
WHERE (enter_date BETWEEN @STARTDATE AND @ENDDATE) and server_name = @server_name
ORDER BY server_name
October 5, 2010 at 2:37 pm
I was able to solve the problem by creating a different where clause and adding a second dataset with a union to pull in the servername.
1. First data set to get server_name and other columns
select server_name from jobreports
where (enterdate between @startdate and @enddate) and (@server_name = 'All' or server_name = @server_name)
2. Second data set to pull in server_names plus adding an 'All' server option.
select distinct(servername)
from backupjobs
union
select 'All' as servername
3. I then made the parameter @server_name to a default parameter of 'All' to pull all server names unless otherwise directed.
Hope this helps others.
October 5, 2010 at 11:12 pm
Nice...job well done:-)
Raunak J
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply