SQL Server Report Paramters issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |