SQL Server Report Paramters issue

select all facilty when multi value parameter property is chosen in sql server2005 reporting services is removed after service pack 1 onwards.
Would some one help me how to resolved this issue.

thanks a lot in advance.
regards
krishna

allow a Null option, then in the SQL, put FieldName like isNull(@Parameter, '%')

if its null, it will pull them all

Joe Janka

Thanks a lot for ur quick reply !! Joe
What i waned it to select Multiple Value and have Select ALL option at the same time.
I implemented

="select Agentid,AgentName,phonelogincode,position from vwethil_agent" &
Iif(Parameters!AgentName.Value=0," "," WHERE Agentid in(" & Parameters!AgentName.Value & ")")

Which allows me to have SELECT ALL but only single value selection not multiple selection.
any ideas..

thanks

my recommendation takes a couple steps, but its the best way to work it, at least in my opiniong

A) keeping the SQL for SQL Reporting Services in a stored procedure on the actual SQL Server instance housing the table is safer and cleaner, so I would recommend calling a stored procedure from the report and having all of logic run away from the reporting instance

B) create a second dataset in your report named something like ds_AgentPopulation, populate it with the following

SELECT
Agentid,
AgentName
FROM
dbo.vwethil_agent
UNION

SELECT
AgentId = 'All',
AgentName = 'All',

C) now go to the parameters property box for AgentName, check the "From Query" option under "Available values:"; input "ds_AgentPopulation" for "Dataset:", Set "Value field" = "Agentid" and "Label Field" = "AgentName" (you may also want to make the default for the parameter ="All"

D) Finally, open the stored procedure on the production instance mentioned earlier.

before you execute the select statement in this code, run an if statement as follows:

if @Agentid = 'All'
begin
set @Agentid = Null
end

select
Agentid,
AgentName,
phonelogincode,
position
from
vwethil_agent
where
Agentid like isnull(@Agentid, '%')

E) this should work, if you have any more questions, let me know

Joe Janka