Multiple item selection from a queried report parameter list

I am trying to mirror a report originally published through another non MS
reporting application which allows multiple selection from a query parameter
list by use of the normal Windows 'Ctrl' or 'Shift' keys in cojunction with
the mouse click.
This method does not appear to function with SQL Reporting Services. The
expanded list automatically closes on selection of one item.
Is there another method of performing multiple selection from a list.This is something which needs to be added reporting services. You can NOT do
a multi selection... About the best thing you can do is allow your user to
enter a delimited list... It's really a good option, but about the only
option right now...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"RW" <RW@.discussions.microsoft.com> wrote in message
news:63B4D8FB-A36B-485B-B251-9D07D810A513@.microsoft.com...
> I am trying to mirror a report originally published through another non MS
> reporting application which allows multiple selection from a query
parameter
> list by use of the normal Windows 'Ctrl' or 'Shift' keys in cojunction
with
> the mouse click.
> This method does not appear to function with SQL Reporting Services. The
> expanded list automatically closes on selection of one item.
> Is there another method of performing multiple selection from a list.

Multiple Default Values of Parameter

I have a report parameter that will either have 1 value to pick from, or multiple values to pick from depending on who is logged in. I want to be able to set the default value to the first result that is returned in the dataset. Is there a simple way to do this without making a new dataset?Hi,

you either need a new dataset or a static value for the default property. You can′t use the dataset as a expression to get the first value of the dataset.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Multiple Datasource as Parameter

This question has been asked numerous times, but I'm still stumped. I have
done several research and some people say it's impossible, while others say
it can be done. One I heard is the dataset extension. I'm still a little
confuse how this work since I'm quite new to working with Reports, especially
in Reporting Services. Hopefully someone can help shed some lights here.
Here's the situation. We have a couple of databases with same fields,
tables, and schema. Each database belongs to a department within the
company. I don't want to have to create each report for each different
database. What I wanted to do is be able to create a report that will be
able to use a parameter for the different databases. The parameter will be a
dynamic one, where the user can just select the database that they want and
then click view.
Can someone shed some lights here?
Thanks.Various approaches for dynamic database connections in RS 2000 have been
discussed on this newsgroup:
* If the databases are on the same server, use a dynamic query text (i.e.
="select * from " & Parameters!DatabaseName.Value & "..table")
* Use the linked server functionality of SQL Server; please check this
thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=848bac6b-98a2-4de7-abfd-bf199a99b660&sloc=en-us
* If you're just toggling between two or three databases, you can publish
the same report 3 times with 3 different names using 3 different data
sources and write a main report that shows/hides the correct subreport based
on whatever criteria you want.
* Use a custom data processing extension. For detailed dicussions see also:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=12f4a06c-57a5-42cf-8e7b-03e0b093129c&sloc=en-us
Native support (expression-based connection strings) is planned to be
included in the next version.
Hope this helps,
Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"chang" <chang@.discussions.microsoft.com> wrote in message
news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> This question has been asked numerous times, but I'm still stumped. I
have
> done several research and some people say it's impossible, while others
say
> it can be done. One I heard is the dataset extension. I'm still a little
> confuse how this work since I'm quite new to working with Reports,
especially
> in Reporting Services. Hopefully someone can help shed some lights here.
> Here's the situation. We have a couple of databases with same fields,
> tables, and schema. Each database belongs to a department within the
> company. I don't want to have to create each report for each different
> database. What I wanted to do is be able to create a report that will be
> able to use a parameter for the different databases. The parameter will
be a
> dynamic one, where the user can just select the database that they want
and
> then click view.
> Can someone shed some lights here?
> Thanks.|||Hello Chang,
What you want to do is pretty easily done - without having to step into
creating custom data extensions...
Bill and I explain this in great detail in our book "Hitchhiker's Guide to
SQL Server 200 Reporting Services" p 298 - 306.
However you may be able to follow these brief instructions:
1) Execute a query in the DataSet Designer that will pull back the fields
that you want. - this should populate the Fields List.
2) Then replace the query string in the DataSet Designer with a Visual
Basic.NET expression (include the =) - for example:-
=iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
xpvs2003.pubs.dbo.sysobjects")
3) Create for yourself the Report Parameter - in the above example this is
called "Source".
In my simple example here in VB.NET expression I'm just returning a Select
String which will obtain it's content from the sysobjects tables in either
the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
dependant upon the parameter.
If the databases are on different servers then what I'd do is include the
full schema name of the target including the servername in the FROM clause -
after having linked the SQL servers (see sp_linked servers in Books On Line)
This should get you started, - but much more comprehensive details and
explanation is in the book.
Best Wishes
Peter Blackburn
www.sqlreportingservices.net
"chang" <chang@.discussions.microsoft.com> wrote in message
news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> This question has been asked numerous times, but I'm still stumped. I
> have
> done several research and some people say it's impossible, while others
> say
> it can be done. One I heard is the dataset extension. I'm still a little
> confuse how this work since I'm quite new to working with Reports,
> especially
> in Reporting Services. Hopefully someone can help shed some lights here.
> Here's the situation. We have a couple of databases with same fields,
> tables, and schema. Each database belongs to a department within the
> company. I don't want to have to create each report for each different
> database. What I wanted to do is be able to create a report that will be
> able to use a parameter for the different databases. The parameter will
> be a
> dynamic one, where the user can just select the database that they want
> and
> then click view.
> Can someone shed some lights here?
> Thanks.|||Thank you Peter and Robert. Those advice are extremely helpful.
Peter I have been following up on your website forum and I found a lot of
neat stuff that I can use for my reports. I'm in the process of getting my
boss to order me a copy of your book.
Regards,
Chang
"Peter Blackburn (www.sqlreportingservice" wrote:
> Hello Chang,
> What you want to do is pretty easily done - without having to step into
> creating custom data extensions...
> Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> SQL Server 200 Reporting Services" p 298 - 306.
> However you may be able to follow these brief instructions:
> 1) Execute a query in the DataSet Designer that will pull back the fields
> that you want. - this should populate the Fields List.
> 2) Then replace the query string in the DataSet Designer with a Visual
> Basic.NET expression (include the =) - for example:-
> =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> xpvs2003.pubs.dbo.sysobjects")
> 3) Create for yourself the Report Parameter - in the above example this is
> called "Source".
> In my simple example here in VB.NET expression I'm just returning a Select
> String which will obtain it's content from the sysobjects tables in either
> the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> dependant upon the parameter.
> If the databases are on different servers then what I'd do is include the
> full schema name of the target including the servername in the FROM clause -
> after having linked the SQL servers (see sp_linked servers in Books On Line)
> This should get you started, - but much more comprehensive details and
> explanation is in the book.
> Best Wishes
> Peter Blackburn
> www.sqlreportingservices.net
>
>
>
> "chang" <chang@.discussions.microsoft.com> wrote in message
> news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > This question has been asked numerous times, but I'm still stumped. I
> > have
> > done several research and some people say it's impossible, while others
> > say
> > it can be done. One I heard is the dataset extension. I'm still a little
> > confuse how this work since I'm quite new to working with Reports,
> > especially
> > in Reporting Services. Hopefully someone can help shed some lights here.
> >
> > Here's the situation. We have a couple of databases with same fields,
> > tables, and schema. Each database belongs to a department within the
> > company. I don't want to have to create each report for each different
> > database. What I wanted to do is be able to create a report that will be
> > able to use a parameter for the different databases. The parameter will
> > be a
> > dynamic one, where the user can just select the database that they want
> > and
> > then click view.
> >
> > Can someone shed some lights here?
> >
> > Thanks.
>
>|||Will Peter, my boss has just ordered me your book from Amazon.com. Can't
wait to get it and join your forum community.
Regards,
Chang
"chang" wrote:
> Thank you Peter and Robert. Those advice are extremely helpful.
> Peter I have been following up on your website forum and I found a lot of
> neat stuff that I can use for my reports. I'm in the process of getting my
> boss to order me a copy of your book.
> Regards,
> Chang
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > Hello Chang,
> >
> > What you want to do is pretty easily done - without having to step into
> > creating custom data extensions...
> >
> > Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> > SQL Server 200 Reporting Services" p 298 - 306.
> >
> > However you may be able to follow these brief instructions:
> > 1) Execute a query in the DataSet Designer that will pull back the fields
> > that you want. - this should populate the Fields List.
> > 2) Then replace the query string in the DataSet Designer with a Visual
> > Basic.NET expression (include the =) - for example:-
> >
> > =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> > xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> > xpvs2003.pubs.dbo.sysobjects")
> >
> > 3) Create for yourself the Report Parameter - in the above example this is
> > called "Source".
> >
> > In my simple example here in VB.NET expression I'm just returning a Select
> > String which will obtain it's content from the sysobjects tables in either
> > the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> > dependant upon the parameter.
> >
> > If the databases are on different servers then what I'd do is include the
> > full schema name of the target including the servername in the FROM clause -
> > after having linked the SQL servers (see sp_linked servers in Books On Line)
> >
> > This should get you started, - but much more comprehensive details and
> > explanation is in the book.
> >
> > Best Wishes
> >
> > Peter Blackburn
> > www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > > This question has been asked numerous times, but I'm still stumped. I
> > > have
> > > done several research and some people say it's impossible, while others
> > > say
> > > it can be done. One I heard is the dataset extension. I'm still a little
> > > confuse how this work since I'm quite new to working with Reports,
> > > especially
> > > in Reporting Services. Hopefully someone can help shed some lights here.
> > >
> > > Here's the situation. We have a couple of databases with same fields,
> > > tables, and schema. Each database belongs to a department within the
> > > company. I don't want to have to create each report for each different
> > > database. What I wanted to do is be able to create a report that will be
> > > able to use a parameter for the different databases. The parameter will
> > > be a
> > > dynamic one, where the user can just select the database that they want
> > > and
> > > then click view.
> > >
> > > Can someone shed some lights here?
> > >
> > > Thanks.
> >
> >
> >|||Will Peter, my boss has just ordered your book for me from Amazon.com. Can't
wait to get the book.
Regards,
Chang
"chang" wrote:
> Thank you Peter and Robert. Those advice are extremely helpful.
> Peter I have been following up on your website forum and I found a lot of
> neat stuff that I can use for my reports. I'm in the process of getting my
> boss to order me a copy of your book.
> Regards,
> Chang
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > Hello Chang,
> >
> > What you want to do is pretty easily done - without having to step into
> > creating custom data extensions...
> >
> > Bill and I explain this in great detail in our book "Hitchhiker's Guide to
> > SQL Server 200 Reporting Services" p 298 - 306.
> >
> > However you may be able to follow these brief instructions:
> > 1) Execute a query in the DataSet Designer that will pull back the fields
> > that you want. - this should populate the Fields List.
> > 2) Then replace the query string in the DataSet Designer with a Visual
> > Basic.NET expression (include the =) - for example:-
> >
> > =iif(Parameters!Source.Value ="NorthWind", "SELECT * FROM
> > xpvs2003.NorthWind.dbo.sysobjects", "SELECT * FROM
> > xpvs2003.pubs.dbo.sysobjects")
> >
> > 3) Create for yourself the Report Parameter - in the above example this is
> > called "Source".
> >
> > In my simple example here in VB.NET expression I'm just returning a Select
> > String which will obtain it's content from the sysobjects tables in either
> > the Pubs or NorthWind example Databases (which are on my xpvs2003 machine)
> > dependant upon the parameter.
> >
> > If the databases are on different servers then what I'd do is include the
> > full schema name of the target including the servername in the FROM clause -
> > after having linked the SQL servers (see sp_linked servers in Books On Line)
> >
> > This should get you started, - but much more comprehensive details and
> > explanation is in the book.
> >
> > Best Wishes
> >
> > Peter Blackburn
> > www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> > "chang" <chang@.discussions.microsoft.com> wrote in message
> > news:EAB6C460-3C29-4B55-9ABD-8CDFB79EC6BF@.microsoft.com...
> > > This question has been asked numerous times, but I'm still stumped. I
> > > have
> > > done several research and some people say it's impossible, while others
> > > say
> > > it can be done. One I heard is the dataset extension. I'm still a little
> > > confuse how this work since I'm quite new to working with Reports,
> > > especially
> > > in Reporting Services. Hopefully someone can help shed some lights here.
> > >
> > > Here's the situation. We have a couple of databases with same fields,
> > > tables, and schema. Each database belongs to a department within the
> > > company. I don't want to have to create each report for each different
> > > database. What I wanted to do is be able to create a report that will be
> > > able to use a parameter for the different databases. The parameter will
> > > be a
> > > dynamic one, where the user can just select the database that they want
> > > and
> > > then click view.
> > >
> > > Can someone shed some lights here?
> > >
> > > Thanks.
> >
> >
> >

multiple datasets

Is it possible to have more than one dataset in a report?

I'm trying to create a parameter that draws its drop down box values from a query that is seperate from the result set.

Thanks

Yes, you can have multiple DataSets in reports. You can set up a DatsSet to be the source for valid parameter values like you describe.

-Chris

Multiple Database Parameter

I have a report that works well, but would like to run it for other
databases. How would I go about this? Could I create some sort of parameter
in the report to call upon a different db?
Thanks,
RyanIf you are on RS 2005 you can have your data source be expression based. It
uses a parameter to determine which database to use. Search index of books
online for the word expressions and then click on data sources.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
>I have a report that works well, but would like to run it for other
> databases. How would I go about this? Could I create some sort of
> parameter
> in the report to call upon a different db?
> Thanks,
> Ryan|||Bruce,
I am found the following expression; "="data source="
&Parameters!ServerName.Value& ";initial
catalog="Parameters!DatabaseName.Value".
When I go to run the report, I am getting an error that says "The
ConnectString expression for the data source â'Dataâ' contains an error:
[BC30277] Type character '&' does not match declared data type 'Object'"
Any thoughts on why I would get this? The connection string is exactly what
i pulled from the microsoft site.
Thanks,
Ryan
"Bruce L-C [MVP]" wrote:
> If you are on RS 2005 you can have your data source be expression based. It
> uses a parameter to determine which database to use. Search index of books
> online for the word expressions and then click on data sources.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
> >I have a report that works well, but would like to run it for other
> > databases. How would I go about this? Could I create some sort of
> > parameter
> > in the report to call upon a different db?
> >
> > Thanks,
> > Ryan
>
>|||I suggest creating a report with just a textbox and the parameter and set
this expression to the textbox. That way you can see what your string is
evaluating to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:A8F14883-6F1B-4E97-90FD-5DCAB8B15F64@.microsoft.com...
> Bruce,
> I am found the following expression; "="data source="
> &Parameters!ServerName.Value& ";initial
> catalog="Parameters!DatabaseName.Value".
> When I go to run the report, I am getting an error that says "The
> ConnectString expression for the data source 'Data' contains an error:
> [BC30277] Type character '&' does not match declared data type 'Object'"
> Any thoughts on why I would get this? The connection string is exactly
> what
> i pulled from the microsoft site.
> Thanks,
> Ryan
> "Bruce L-C [MVP]" wrote:
>> If you are on RS 2005 you can have your data source be expression based.
>> It
>> uses a parameter to determine which database to use. Search index of
>> books
>> online for the word expressions and then click on data sources.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
>> >I have a report that works well, but would like to run it for other
>> > databases. How would I go about this? Could I create some sort of
>> > parameter
>> > in the report to call upon a different db?
>> >
>> > Thanks,
>> > Ryan
>>|||Bruce,
I tried this and no luck. Any other thoughts?
Ryan
"Bruce L-C [MVP]" wrote:
> I suggest creating a report with just a textbox and the parameter and set
> this expression to the textbox. That way you can see what your string is
> evaluating to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> news:A8F14883-6F1B-4E97-90FD-5DCAB8B15F64@.microsoft.com...
> > Bruce,
> > I am found the following expression; "="data source="
> > &Parameters!ServerName.Value& ";initial
> > catalog="Parameters!DatabaseName.Value".
> >
> > When I go to run the report, I am getting an error that says "The
> > ConnectString expression for the data source 'Data' contains an error:
> > [BC30277] Type character '&' does not match declared data type 'Object'"
> >
> > Any thoughts on why I would get this? The connection string is exactly
> > what
> > i pulled from the microsoft site.
> >
> > Thanks,
> >
> > Ryan
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> If you are on RS 2005 you can have your data source be expression based.
> >> It
> >> uses a parameter to determine which database to use. Search index of
> >> books
> >> online for the word expressions and then click on data sources.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
> >> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
> >> >I have a report that works well, but would like to run it for other
> >> > databases. How would I go about this? Could I create some sort of
> >> > parameter
> >> > in the report to call upon a different db?
> >> >
> >> > Thanks,
> >> > Ryan
> >>
> >>
> >>
>
>|||="data source=" & Parameters!ServerName.Value & ";initial
catalog=AdventureWorks"
Above is from the books online. It is not what you are doing. You are
assembling a string that will eventually look like this.
data source=myservername;initial catalog=AdventureWorks
You've got double quotes all over the place. Again, start this with a text
box. Do not put quotes around the = sign. Just off the top of my head I
think this is what you want:
="data source=" & Parameters!ServerName.Value & ";initial catalog=" &
Parameters!DatabaseName.Value
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
news:C8F61505-7D51-43D4-AB32-5D40A963788B@.microsoft.com...
> Bruce,
> I tried this and no luck. Any other thoughts?
> Ryan
> "Bruce L-C [MVP]" wrote:
>> I suggest creating a report with just a textbox and the parameter and set
>> this expression to the textbox. That way you can see what your string is
>> evaluating to.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> news:A8F14883-6F1B-4E97-90FD-5DCAB8B15F64@.microsoft.com...
>> > Bruce,
>> > I am found the following expression; "="data source="
>> > &Parameters!ServerName.Value& ";initial
>> > catalog="Parameters!DatabaseName.Value".
>> >
>> > When I go to run the report, I am getting an error that says "The
>> > ConnectString expression for the data source 'Data' contains an error:
>> > [BC30277] Type character '&' does not match declared data type
>> > 'Object'"
>> >
>> > Any thoughts on why I would get this? The connection string is exactly
>> > what
>> > i pulled from the microsoft site.
>> >
>> > Thanks,
>> >
>> > Ryan
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> If you are on RS 2005 you can have your data source be expression
>> >> based.
>> >> It
>> >> uses a parameter to determine which database to use. Search index of
>> >> books
>> >> online for the word expressions and then click on data sources.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Ryan Mcbee" <RyanMcbee@.discussions.microsoft.com> wrote in message
>> >> news:893663B7-32A2-4814-BB02-B0E6F5834CD7@.microsoft.com...
>> >> >I have a report that works well, but would like to run it for other
>> >> > databases. How would I go about this? Could I create some sort of
>> >> > parameter
>> >> > in the report to call upon a different db?
>> >> >
>> >> > Thanks,
>> >> > Ryan
>> >>
>> >>
>> >>
>>

Multiple Columns in Parameter Field

Hi

I have a report that has a number of parameters in it that the user must select prior to running the report. One of these parameters is the site_ref that they wish to use against the dataset. All my books point to the fact that if you wish, you can select what appears in the parameter field so the user does not get confused. So rather than the user having a list of site_ref which maybe useless to them, they can have a description next to each entry, e.g.

site_Ref Estate_Name
AL Aladdin's Cave
TH Bug's Bunnies Home
XN Aliens Home

Then when the user selects what he wants from list, the actual value that is passed to the parameter is 'TH' for example. In Access I believe this was called Bound to column(?). I can find no where in the 'Report Parameters' dialog box that allows for multiple columns to be displayed. I have written the SQL to create the right data;

Select site_ref, estate_name
From dbo_src_centre_list
Order By site_ref

How can I get the parameter box in Reporting Services to allow the display of both columns but bind to the site ref when it comes to passing the info into the report. None of my books actually tell me how to do it though. I have found how to get one or the other to display and the right one to pass the actually value through, but I would like to see both in list at the same time

Regards

A report parameter valid values list contains a set of value/label pairs. When the report is run, the user sees the label. When the user selects a label, the corresponding value is used as the parameter value.

In your case, you just need to set the label of the report parameter to be bound to the Estate_Name field, while the value is bound to the Site_Ref field.

Additional information on parameters in reports can be found here: http://msdn2.microsoft.com/en-us/library/ms155917.aspx

-- Robert

|||

Thank you Robert. That is exactly what I did, what I was wondering though, is it possible to have two values showing in the parameter field e.g. AC - Aladdin's Cave and tie the value to just the AC.

I will read that link you have sent through and see what direction that pushes me in.

Regards

|||

One way of doing this is to construct the desired label directly in the query:

Select site_ref, site_ref + ' - ' + estate_name AS Label
From dbo_src_centre_list
Order By site_ref

-- Robert

|||

Thank you so much Robert, that is exactly what I wanted. Works perfectly.

Kindest Regards

Multi-Parameter (Select All) Detection in SSRS 2005?

I'd like to know if SSRS provides a way to determine if all rows are selected from a multi_valued parameter dropdown list? I thought of how I could do this programmatically, but didn't want to build something that is already in the product (if it exists that is). Thanks.

There is no built-in functionality, but here are some ideas:

* if the multi value parameter has a pre-defined (constant) list of valid values, you know how many values are available for selection. The report parameters in RS 2005 expose a new property called .Count which tells you the count of selected parameter values (e.g. =Parameters!P1.Count). Hence, you could compare the count of the selected values with the count of the total values.

* if the multi value parameter has a dataset-based valid values list, you could just use the same field in a CountDistinct aggregate function to determine how many valid values are available, e.g. =CountDistinct(Fields!A.Value) and compare it again with the Count of selected values.

-- Robert

|||Thank you Robert. I forgot about CountDistinct.