SQL Server Report Paramters issue | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
SQL Server Report Paramters issueselect 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.
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 = ‘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’
set @Agentid = Null
Agentid like isnull(@Agentid, ‘%’) E) this should work, if you have any more questions, let me know Joe Janka