February 8, 2008 at 1:45 am
Hi all.
i would like to know if there is any way that i can make a Dynamic filter in my Reporting services project? any form of assistance will be very much appreciated
Regards,
Noel
February 8, 2008 at 3:03 am
The simple answers are to either use a parameter variable in your where clause in the SQL, or filter the rows in the table properties based on a parameter.
If you're thinking of using a completely different where clause, that's another question entirely. Then you could union two select statements (one with each where clause), and use a boolean parameter to turn on or off each select.
Regards
Jonathan
February 8, 2008 at 8:57 am
I'm using this code sequence but i still get all the results i need instead of the ones i have selected in the report manager
/*****************************************************/
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER Proc [dbo].[MyNewestReportProc]
(@id varchar(40)= null,
@lastname varchar(40)= null,
@jobs varchar(40)= null)
as
declare @where varchar(800)
declare@sqlstatement nvarchar(4000)
set @where = ' where '
if @id <> ''
begin
set @where = @where + 'id' + '=' + @id
end
else
begin
set @where = ' '
end
if @lastname <> ''
begin
set @where = @where + ' AND ' + 'lastname' + ' = ' + @lastname
end
else
begin
set @where = ' '
end
if @jobs <> ''
begin
set @where = @where + ' and ' + 'jobs' + ' = ' + @jobs
end
else
begin
set @where = ' '
end
set @sqlstatement = ' select * from tbl_profile ' + @where
exec sp_executesql @sqlstatement
/*****************************************************/
can you please comment on my scripts?
any help would be soo great
thanks
N.O.E.L.
February 8, 2008 at 11:30 am
I see why you're getting all your records back without limits.
Let's look at some pseudocode for your script:
Set @where variable = "WHERE "
if param1 not empty then
filter by param1'
else
don't filter (set @where variable to no value)
if param2 not empty then
filter by param1 + param2 or syntax error (@where = "AND param2")
else
don't filter
if param3 not empty then
filter by param1 + param2 + param3 or syntax error (@where still missing "WHERE")
else
don't filter
Do you see what's happening? Unless you populate all the params the proc is going to return all rows or fail on a syntax error.
You're final select is going to be either
"SELECT *
FROM table
WHERE id = @id AND lastname = @lastname AND jobs = @jobs"
or
"SELECT *
FROM table"
or
"SELECT *
FROM table
AND lastname = @lastname [AND jobs = @jobs]"
-- WHERE gets zapped whenever a param is empty
You're going to need to somehow iterate for all 7 combinations (@id only, @lastname only, @jobs only, @id+@lastname, @id+@jobs, @id+@lastname+@jobs, @lastname+@jobs) plus no params.
If it were me, I'd try to do it with a CASE statement
SET @WHERE =
CASE
WHEN @ID <> '' AND @Last <> '' AND @jobs <> '' THEN 'WHERE id = @id AND last = @Last AND jobs = @jobs'
WHEN @ID <> '' AND @Last <> '' AND @jobs = '' THEN 'WHERE id = @id AND last = @Last'
--other combos
ELSE @WHERE = ' '
END
Remember, since CASE returns the first true scenario it finds, order your choices from most constraints to least.
HTH
Good luck
[font="Comic Sans MS"]toolman[/font]
[font="Arial Narrow"]Numbers 6:24-26[/font]
February 11, 2008 at 8:30 am
Rather than using dynamic code unnecessarily, why not do it like this?
[font="Courier New"]
SELECT*
FROMtbl_profile
WHERE(id = @id OR @id IS NULL)
AND(lastname = @lastname OR @lastname IS NULL)
AND(jobs = @jobs OR @jobs IS NULL)
[/font]
Regards
Jonathan
April 21, 2009 at 3:34 am
I want to add to the conversation, if I may.
The problem with the pattern "@XXX IS NULL OR XXX = @XXX" is that it ignores the indexes then.
Best regards
Andrew
April 21, 2009 at 3:41 am
Why is that?
I recently had an issue where 'AND @ProfileIDI IS NOT NULL' was extremely slow (> 1 min), whereas using 'OR @ProfileIDI IS NULL' was much faster (< 10 seconds) which wouldn't seem to agree with what you say...so I'd like to properly understand what is going on and the background to what you're saying.
Thanks
April 21, 2009 at 5:45 am
external (4/21/2009)
I want to add to the conversation, if I may.The problem with the pattern "@XXX IS NULL OR XXX = @XXX" is that it ignores the indexes then.
Best regards
Andrew
Why would this ignore the index?
M.Sc.IT, M.B.A, MCTS BI 2008, MCITP BI 2008, MCTS SQL Dev, CSM, CDVDM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply