Custom Report Parameters
-
I have simple report parameters, and they are selected from a SQL statement like this:
select id, text from status
simple...
but I want to add 1 row to the results, I want a row with
"All" as the text
and an id of "99"
So when I select my parameters, I have all the normal database stuff, and 1 extra one. Can this be done? And if it can be done, what do i have to do with my SQL to ignor the @status parameter if the user selects it? (ie dont filter by the status)
-
Mark - I'm sure someone else will come along who has a better comprehension of your question....this is what I understand you want to do with the first part of your question..
select id, text from status
union
select 99, 'All'
could you explain the second part "what do i have to do with my SQL to ignor the @status parameter if the user selects it? (ie dont filter by the status)" in more detail ?!
**ASCII stupid question, get a stupid ANSI !!!** -
sorry i couldnt be more clear
ill try again,in my db, the status table contains 6 rows, with id's from 1 to 6.
if i add another option to the parameters called "All" like you have shown me how to do, then when the user selects the "All" parameter, nothing will be returned because no status' have 99 as their ID. So in my SQL i thought there may be a way to say:
select blah1, blah2.......from........
where (if @status = 99 then return all rows else job_status = @status)
get me?
I think i have a solution, heres my attempt, i think it works:
cast(job_status AS varchar(2)) LIKE CASE WHEN @status = 0 THEN '%' WHEN @status > 0 THEN @status END
-
Thanks for the reply, but like I said, none of the status' have an id of 99, so nothing gets returned
-
Have you tried running this up in RS? We use this approach quite often to give our users the 'All' or individual selection options. I noticed your earlier post looked to be dynamic SQL, this approach doesn't require that (ie you can just use standard SQL approach or even put it into a stored proc). i.e. where previously you were checking your parm value in VB.net and then concatenating to the sql string (ie this is where it's "dynamic"), you don't need to do this, you just use a standard SQL statement (ie, no quotes around the statement, no equals at the start).
The 'OR' statement in the where clause (when the user selects the 99 item) essentially makes your SQL statement SELECT <some_stuff> FROM <some_table> WHERE 1 = 1
(or in this case, WHERE 99 = 99), which is always true (we hope
) and is the functional equivalent of not have that part of the where clause and therefore not filtering out any records (ie, returning 'ALL' as required).Steve.
-
For parameters query:
SELECT 0, 'All'
UNION
SELECT id, text
FROM table1
For data retrieval:
CREATE TABLE #IDS (ID int)
IF @ID = 0
INSERT INTO #IDS
SELECT id FROM table1
ELSE
SELECT id FROM table1 WHERE id = @ID
Then join #IDS with your data on id instead using "WHERE id = ..."
-
I will try to be as clear as possible, excuse my english
What you will need to do is for the SP's that are used to generate the
parameter list add a value that will mean "All Possible value" like so:
create procedure <ParamList>
AS
declare @paramtable TABLE (ordersequence TINYINT,
id INT,
VALUE VARCHAR(100))
insert into @paramtable values (1,99,"All Possible values")
insert into @paramtable
select 1,id,value
from table1
select id,value
from @paramtable
order by ordersequence,value
Then you will need to setup your parameter in RS and define the default
value of this parameter to 99.
You will also need to modify your report SP to manage the case of the "All
Possible values" param value....
Something like this should work
Create Procedure <Report>
(
@ParamValue INT
)
AS
select col1,col2,col3,col4
from table1
where col1 = case when @ParamValue = 99 then col1
else @ParamValue
end
HTH,
Eric
-
Steve, I see what your saying now, I dont know what I was thinking. It works just fine in RS. The SQL i showed you was not dynamic, just pure SQL.
Aiwa, thanks for spending so much time on you reply, but steves method will do for now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply