SQL Server Performance Forum – Threads Archive

Multiple Parameters

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.

---

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

---

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

---

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

---

]]>