May 29, 2014 at 11:58 am
Hi
Not sure why I'm stuck on this.. but I am
I have a parameter called activestatus values are "A" for Active or "B" for "All"
I want to filter the dataset by the field "Active" if the parameter value is "A" then get only "Active" field = "A"
But... if the parameter is "B" then I want everything with no filtering...
Thanks
Joe
May 29, 2014 at 12:08 pm
jbalbo (5/29/2014)
HiNot sure why I'm stuck on this.. but I am
I have a parameter called activestatus values are "A" for Active or "B" for "All"
I want to filter the dataset by the field "Active" if the parameter value is "A" then get only "Active" field = "A"
But... if the parameter is "B" then I want everything with no filtering...
Thanks
Joe
What you have here is a catch all query. Look at this article which explains this type of query and how to make them not only work but also work fast!!!
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2014 at 12:12 pm
If you have those values assigned to a parameter in a report, you can take that parameter and pass it to a function in the filter of the dataset for your tablix.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 29, 2014 at 12:15 pm
I tried this on the dataset filter expression but is not working
=IIf(Parameters!active.Value = "A",isnothing(Fields!DischargeDate.Value),Fields!activestatus.Value = "A" or Fields!activestatus.Value = "B")
May 29, 2014 at 12:25 pm
Why are you pulling in discharge date if the parameter value is "A"?
Don't you want to pull in where the active field is equal to "A"?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 25, 2014 at 6:32 pm
One thing you can do is ...
create procedure myProc
(@status char(1))
AS
BEGIN
set nocount on;
IF (@status ='B')
BEGIN
/* run this code with no filtering by the field 'active'*/
END
ELSE
BEGIN
SELECT /* your columns
..., etc... */
WHERE active = @status
END
END/* proc */
I have a hunch the engine can produce multiple query plans in this instance. Is this correct?
----------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply