SQL Server Performance

SQL Server Report Paramters issue

Discussion in 'SQL Server Reporting Services' started by ravikris, Sep 5, 2006.

  1. ravikris New Member

    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
  2. jastone New Member

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

    if its null, it will pull them all

    Joe Janka
  3. ravikris New Member

    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
  4. jastone New Member

    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

Share This Page