SQL Server Performance

Multiple Parameters

Discussion in 'SQL Server Reporting Services' started by drhabi, May 5, 2004.

  1. drhabi New Member

    I am trying to create a report that can accept none, one or multiple parameters setn to it. I am somewhat new to this and maybe I set up my parameters incorrect but it seems I if I set up a parameter then I must send it one. It would not accept multiple or none. Is this the way it is or is their a way to do this different.
  2. harsh_sr New Member

    You can set default values for parameters. Than it'll not ask for those values in case you don't provide any.

    Harsh
  3. drhabi New Member

    What I am trying to do is have a report that run for all types and locations when nothing is selected or if I choose a location or specific types it filters it out. I have been trying to see if this is possible but so far I do not believe it is. If I create a parameter I must send a value and have not been able to have it work if I send none. Is there some other way to send a filter to the report or must I send it like this
    http://myserver/Reportserver?/forms/PhysForm&rs:Command=Render&loc=1&type=1
  4. harsh_sr New Member

    You can write the query in Report itself as

    IF @location IS NULL
    Select * ...
    ELSE
    Select * ....
    where location = @location

    In report parameters, check ALLOW Null Value for @location.

    OR

    You can write an Stored Proc to fetch records based on the @location & @types.
    Your SP can take care to make query based on parameters passed (if null than dont filter)...like

    If @location IS NULL
    ...
    else
    select * ..

    HTH
    Harsh

Share This Page