Parameters Select one or all

I have 2 datasets. One contains the information for the report and the other
contains a list of available companies.
In the report parameter area I have the company as a parameter and 'from
query' selected.
When the report runs, the user is prompted to 'Select a Company' from a drop
down list.
I want the user to be able to select one company or all companies. How do I
make this happen?On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> I have 2 datasets. One contains the information for the report and the other
> contains a list of available companies.
> In the report parameter area I have the company as a parameter and 'from
> query' selected.
> When the report runs, the user is prompted to 'Select a Company' from a drop
> down list.
> I want the user to be able to select one company or all companies. How do I
> make this happen?
Hi donna...what version of SSRS are you using? In SSRS 2005 you can
make the drop down a multi-value select by checking the 'multi-value'
box inside the Report Parameters window. This also would require that
your stored procedure be able to process multiple values inside that
parameter. If this is an option I would suggest following the steps
layed out by Bruce Loehle-Cogner here:
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
Otherwise if you are using SSRS 2000, or don't want to allow a multi-
select, you could make the query returning the values for the drop
down include a NULL. So something like...
/***/
select NULL as 'value', ' - All - ' as 'label'
union
/* Normal select for list of companies here */
select ... from ...
/***/
then in your stored proc that returns the main set of data have
something like the following in your where clause
/***/
select
...
from
...
where
...
(@.company = company_column or @.company is null)
/***/
This should then return results for all companies if @.company is
null.
Hope this helps!
--
Ben Sullins|||Thanks Ben,
I'm on RS 2000. Your comments were helpfull. I successfully used the UNION
statement on varchar fields. I now have this issue. On a int datatype field
I am receiving the following message: Syntax error converting the varchar
value 'ALL PROJECTS' to a column of data type int. Following is my code.
SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
FROM ProjectActualCost
UNION
SELECT - 1, 'ALL PROJECTS'
Any ideas?
Thanks,
Donna
"sullins602" wrote:
> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> > I have 2 datasets. One contains the information for the report and the other
> > contains a list of available companies.
> >
> > In the report parameter area I have the company as a parameter and 'from
> > query' selected.
> >
> > When the report runs, the user is prompted to 'Select a Company' from a drop
> > down list.
> >
> > I want the user to be able to select one company or all companies. How do I
> > make this happen?
> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> make the drop down a multi-value select by checking the 'multi-value'
> box inside the Report Parameters window. This also would require that
> your stored procedure be able to process multiple values inside that
> parameter. If this is an option I would suggest following the steps
> layed out by Bruce Loehle-Cogner here:
> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>
> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> select, you could make the query returning the values for the drop
> down include a NULL. So something like...
> /***/
> select NULL as 'value', ' - All - ' as 'label'
> union
> /* Normal select for list of companies here */
> select ... from ...
> /***/
> then in your stored proc that returns the main set of data have
> something like the following in your where clause
> /***/
> select
> ...
> from
> ...
> where
> ...
> (@.company = company_column or @.company is null)
> /***/
> This should then return results for all companies if @.company is
> null.
> Hope this helps!
> --
> Ben Sullins
>|||SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
FROM ProjectActualCost
UNION
SELECT - 1 as value, 'ALL PROJECTS' as label
Also, I like to use value and label when creating this, it makes it real
obvious which one you are using when you are filling in the properties for
the parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DONNA" <DONNA@.discussions.microsoft.com> wrote in message
news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> Thanks Ben,
> I'm on RS 2000. Your comments were helpfull. I successfully used the
> UNION
> statement on varchar fields. I now have this issue. On a int datatype
> field
> I am receiving the following message: Syntax error converting the varchar
> value 'ALL PROJECTS' to a column of data type int. Following is my code.
> SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> FROM ProjectActualCost
> UNION
> SELECT - 1, 'ALL PROJECTS'
> Any ideas?
> Thanks,
> Donna
>
> "sullins602" wrote:
>> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> > I have 2 datasets. One contains the information for the report and the
>> > other
>> > contains a list of available companies.
>> >
>> > In the report parameter area I have the company as a parameter and
>> > 'from
>> > query' selected.
>> >
>> > When the report runs, the user is prompted to 'Select a Company' from a
>> > drop
>> > down list.
>> >
>> > I want the user to be able to select one company or all companies. How
>> > do I
>> > make this happen?
>> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> make the drop down a multi-value select by checking the 'multi-value'
>> box inside the Report Parameters window. This also would require that
>> your stored procedure be able to process multiple values inside that
>> parameter. If this is an option I would suggest following the steps
>> layed out by Bruce Loehle-Cogner here:
>> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>>
>> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> select, you could make the query returning the values for the drop
>> down include a NULL. So something like...
>> /***/
>> select NULL as 'value', ' - All - ' as 'label'
>> union
>> /* Normal select for list of companies here */
>> select ... from ...
>> /***/
>> then in your stored proc that returns the main set of data have
>> something like the following in your where clause
>> /***/
>> select
>> ...
>> from
>> ...
>> where
>> ...
>> (@.company = company_column or @.company is null)
>> /***/
>> This should then return results for all companies if @.company is
>> null.
>> Hope this helps!
>> --
>> Ben Sullins
>>|||How do I manage this same concept with a datetime value? I'm having some
problems with an error related to converting to datetime from string.
Query parameter:
SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
sent_date
FROM o_dpl_deployment
WHERE sent_date IS NOT NULL
UNION
SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
Main Query:
...
((D.sent_date = @.sent_date) OR (@.sent_date ='1753-01-01')) AND
...
"Bruce L-C [MVP]" wrote:
> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
> FROM ProjectActualCost
> UNION
> SELECT - 1 as value, 'ALL PROJECTS' as label
> Also, I like to use value and label when creating this, it makes it real
> obvious which one you are using when you are filling in the properties for
> the parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
> > Thanks Ben,
> >
> > I'm on RS 2000. Your comments were helpfull. I successfully used the
> > UNION
> > statement on varchar fields. I now have this issue. On a int datatype
> > field
> > I am receiving the following message: Syntax error converting the varchar
> > value 'ALL PROJECTS' to a column of data type int. Following is my code.
> >
> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
> > FROM ProjectActualCost
> > UNION
> > SELECT - 1, 'ALL PROJECTS'
> >
> > Any ideas?
> >
> > Thanks,
> >
> > Donna
> >
> >
> >
> > "sullins602" wrote:
> >
> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
> >> > I have 2 datasets. One contains the information for the report and the
> >> > other
> >> > contains a list of available companies.
> >> >
> >> > In the report parameter area I have the company as a parameter and
> >> > 'from
> >> > query' selected.
> >> >
> >> > When the report runs, the user is prompted to 'Select a Company' from a
> >> > drop
> >> > down list.
> >> >
> >> > I want the user to be able to select one company or all companies. How
> >> > do I
> >> > make this happen?
> >>
> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
> >> make the drop down a multi-value select by checking the 'multi-value'
> >> box inside the Report Parameters window. This also would require that
> >> your stored procedure be able to process multiple values inside that
> >> parameter. If this is an option I would suggest following the steps
> >> layed out by Bruce Loehle-Cogner here:
> >>
> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
> >>
> >>
> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
> >> select, you could make the query returning the values for the drop
> >> down include a NULL. So something like...
> >>
> >> /***/
> >> select NULL as 'value', ' - All - ' as 'label'
> >> union
> >> /* Normal select for list of companies here */
> >> select ... from ...
> >> /***/
> >>
> >> then in your stored proc that returns the main set of data have
> >> something like the following in your where clause
> >>
> >> /***/
> >> select
> >> ...
> >> from
> >> ...
> >> where
> >> ...
> >> (@.company = company_column or @.company is null)
> >> /***/
> >>
> >> This should then return results for all companies if @.company is
> >> null.
> >>
> >> Hope this helps!
> >> --
> >> Ben Sullins
> >>
> >>
>
>|||SELECT convert(datetime,'1753-01-01') as value, 'All Deployment Dates' as
sent_date
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephanie" <Stephanie@.discussions.microsoft.com> wrote in message
news:49D8B8E1-7657-4D1D-92D5-38D3ED874751@.microsoft.com...
> How do I manage this same concept with a datetime value? I'm having some
> problems with an error related to converting to datetime from string.
> Query parameter:
> SELECT DISTINCT sent_date as value, convert(varchar(30),sent_date) as
> sent_date
> FROM o_dpl_deployment
> WHERE sent_date IS NOT NULL
> UNION
> SELECT '1753-01-01' as value, 'All Deployment Dates' as sent_date
> Main Query:
> ...
> ((D.sent_date = @.sent_date) OR (@.sent_date => '1753-01-01')) AND
> ...
>
> "Bruce L-C [MVP]" wrote:
>> SELECT PROJECT_ID AS value, convert(varchar(20),PROJECT_ID) as label
>> FROM ProjectActualCost
>> UNION
>> SELECT - 1 as value, 'ALL PROJECTS' as label
>> Also, I like to use value and label when creating this, it makes it real
>> obvious which one you are using when you are filling in the properties
>> for
>> the parameter.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "DONNA" <DONNA@.discussions.microsoft.com> wrote in message
>> news:417636EC-544E-40AF-A495-3BD6A582E498@.microsoft.com...
>> > Thanks Ben,
>> >
>> > I'm on RS 2000. Your comments were helpfull. I successfully used the
>> > UNION
>> > statement on varchar fields. I now have this issue. On a int datatype
>> > field
>> > I am receiving the following message: Syntax error converting the
>> > varchar
>> > value 'ALL PROJECTS' to a column of data type int. Following is my
>> > code.
>> >
>> > SELECT PROJECT_ID AS PROJECT_ID_KEY, PROJECT_ID
>> > FROM ProjectActualCost
>> > UNION
>> > SELECT - 1, 'ALL PROJECTS'
>> >
>> > Any ideas?
>> >
>> > Thanks,
>> >
>> > Donna
>> >
>> >
>> >
>> > "sullins602" wrote:
>> >
>> >> On Mar 2, 2:49 pm, DONNA <D...@.discussions.microsoft.com> wrote:
>> >> > I have 2 datasets. One contains the information for the report and
>> >> > the
>> >> > other
>> >> > contains a list of available companies.
>> >> >
>> >> > In the report parameter area I have the company as a parameter and
>> >> > 'from
>> >> > query' selected.
>> >> >
>> >> > When the report runs, the user is prompted to 'Select a Company'
>> >> > from a
>> >> > drop
>> >> > down list.
>> >> >
>> >> > I want the user to be able to select one company or all companies.
>> >> > How
>> >> > do I
>> >> > make this happen?
>> >>
>> >> Hi donna...what version of SSRS are you using? In SSRS 2005 you can
>> >> make the drop down a multi-value select by checking the 'multi-value'
>> >> box inside the Report Parameters window. This also would require that
>> >> your stored procedure be able to process multiple values inside that
>> >> parameter. If this is an option I would suggest following the steps
>> >> layed out by Bruce Loehle-Cogner here:
>> >>
>> >> http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/e832f21f9400af08/5b6ea54b15e7fb3f?lnk=gst&q=2005+multi-value+select+list&rnum=5&hl=en#5b6ea54b15e7fb3f
>> >>
>> >>
>> >> Otherwise if you are using SSRS 2000, or don't want to allow a multi-
>> >> select, you could make the query returning the values for the drop
>> >> down include a NULL. So something like...
>> >>
>> >> /***/
>> >> select NULL as 'value', ' - All - ' as 'label'
>> >> union
>> >> /* Normal select for list of companies here */
>> >> select ... from ...
>> >> /***/
>> >>
>> >> then in your stored proc that returns the main set of data have
>> >> something like the following in your where clause
>> >>
>> >> /***/
>> >> select
>> >> ...
>> >> from
>> >> ...
>> >> where
>> >> ...
>> >> (@.company = company_column or @.company is null)
>> >> /***/
>> >>
>> >> This should then return results for all companies if @.company is
>> >> null.
>> >>
>> >> Hope this helps!
>> >> --
>> >> Ben Sullins
>> >>
>> >>
>>