How can we have 'optional' parameters?

Is there anyway in SQL2005RS to have optional parameters?
Eg supposing we have a report where we want the user to be able to
select a category, but if the user makes no selection we see ALL
catagories. Is this possible.
The sql dataset is something like
select col1
from table
where category=@.category
.....is it possible to make the where clause optional? Ie if category
is NOT selected then dont do the where at all? It also seems like the
SQL2k5RS client MAKES the user select a category?
Al.Use this query instead:
select col1
from table
where category in (@.category)
and make the parameter multi-value. Your drop down will have checkboxes next
to each value, plus a selection at the top that says "All".
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"scubaal" <al@.blakes.net> wrote in message
news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.|||The other option is to have a selection called 'All'. Then in your query do
this (I use this trick a lot).
select col1 from table where (category=@.category or @.category = 'All')
This gives you an example of a query to create your category list for your
parameter.
select category as label, category as value from categorytable union
select 'All' as lable, 'All' as value
order by label
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23LobPxnpIHA.5916@.TK2MSFTNGP04.phx.gbl...
> Use this query instead:
> select col1
> from table
> where category in (@.category)
> and make the parameter multi-value. Your drop down will have checkboxes
> next to each value, plus a selection at the top that says "All".
>
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>
> "scubaal" <al@.blakes.net> wrote in message
> news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
>> Is there anyway in SQL2005RS to have optional parameters?
>> Eg supposing we have a report where we want the user to be able to
>> select a category, but if the user makes no selection we see ALL
>> catagories. Is this possible.
>> The sql dataset is something like
>> select col1
>> from table
>> where category=@.category
>> .....is it possible to make the where clause optional? Ie if category
>> is NOT selected then dont do the where at all? It also seems like the
>> SQL2k5RS client MAKES the user select a category?
>> Al.
>|||My preferred variation (although the others are quite good):
select col1
from table
where category LIKE @.category
You can then define a drop down list within report parameters to
display 'All' to the user, while submitting '%' as the real parameter
value.
On Apr 24, 7:39 pm, scubaal <a...@.blakes.net> wrote:
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.|||Another option is to set the report parameter to 'Allows Nulls', and the in
you sql use:
select col1
from table
where
(category=@.category
OR
@.category is null)
-- Original Message --
From: "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com>
Newsgroups: microsoft.public.sqlserver.reportingsvcs
Sent: Friday, April 25, 2008 10:23 AM
Subject: Re: How can we have 'optional' parameters?
> The other option is to have a selection called 'All'. Then in your query
> do this (I use this trick a lot).
> select col1 from table where (category=@.category or @.category = 'All')
> This gives you an example of a query to create your category list for your
> parameter.
> select category as label, category as value from categorytable union
> select 'All' as lable, 'All' as value
> order by label
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
> news:%23LobPxnpIHA.5916@.TK2MSFTNGP04.phx.gbl...
>> Use this query instead:
>> select col1
>> from table
>> where category in (@.category)
>> and make the parameter multi-value. Your drop down will have checkboxes
>> next to each value, plus a selection at the top that says "All".
>>
>> Alain Quesnel
>> alainsansspam@.logiquel.com
>> www.logiquel.com
>>
>> "scubaal" <al@.blakes.net> wrote in message
>> news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
>> Is there anyway in SQL2005RS to have optional parameters?
>> Eg supposing we have a report where we want the user to be able to
>> select a category, but if the user makes no selection we see ALL
>> catagories. Is this possible.
>> The sql dataset is something like
>> select col1
>> from table
>> where category=@.category
>> .....is it possible to make the where clause optional? Ie if category
>> is NOT selected then dont do the where at all? It also seems like the
>> SQL2k5RS client MAKES the user select a category?
>> Al.
>
"scubaal" <al@.blakes.net> wrote in message
news:bb8fd4f4-23c7-4fc8-9195-cd984397947f@.u36g2000prf.googlegroups.com...
> Is there anyway in SQL2005RS to have optional parameters?
> Eg supposing we have a report where we want the user to be able to
> select a category, but if the user makes no selection we see ALL
> catagories. Is this possible.
> The sql dataset is something like
> select col1
> from table
> where category=@.category
> .....is it possible to make the where clause optional? Ie if category
> is NOT selected then dont do the where at all? It also seems like the
> SQL2k5RS client MAKES the user select a category?
> Al.

How can my c#.net app open a rs report?

Ok,
I have just developed the c# app and the rs report...how does my c# app
open the report (and how does it pass parameters)?
Thanks,
TrintTwo ways to integrate. Either with URL integration (embed the IE control and
give it the appropriate URL) or web services. URL integration is easier.
With Widbey and Yukon their will be available a winform and webform controls
but for now you need to use one of these other integration methods.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trint" <trinity.smith@.gmail.com> wrote in message
news:1104503150.525220.298270@.c13g2000cwb.googlegroups.com...
> Ok,
> I have just developed the c# app and the rs report...how does my c# app
> open the report (and how does it pass parameters)?
> Thanks,
> Trint
>|||Look at the ReportViewer control that is part of the Sample
Applications that are installed with RS. It is designed to view the
report in a web page, but might be helpfull.

How can I use arrays in parameters.

Following code is just select category 1 but for example What can I do if I
want to select 1 and 2?
declare @.CategoryID as int
set @.CategoryID = 1
SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)Hi
You would need to use dynamic SQL to have multiple values in a IN clause
when using a variable number of values.
Regards
Mike
"Murat BUDAK" wrote:

> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>
>|||http://www.sommarskog.se/arrays-in-sql.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Murat BUDAK" <mbudak@.islem.com> wrote in message
news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> Following code is just select category 1 but for example What can I do if
I
> want to select 1 and 2?
> declare @.CategoryID as int
> set @.CategoryID = 1
> SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@.CategoryID)
>|||"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:96082D73-8AD1-4597-A0E6-55D9F16E4386@.microsoft.com...
> You would need to use dynamic SQL to have multiple values in a IN clause
> when using a variable number of values.
Mike,
You should read Erland's article that I posted the link to. You do not need
dynamic SQL for this.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Impresive. Thanks for this article :o)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:Og0LJquAFHA.3492@.TK2MSFTNGP12.phx.gbl...
> http://www.sommarskog.se/arrays-in-sql.html
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Murat BUDAK" <mbudak@.islem.com> wrote in message
> news:u1J9jfuAFHA.1392@.tk2msftngp13.phx.gbl...
> I
>