Parameters option for "All"

hey there, relatively new new SRS, but lots of Crystal background. I'm having
a bit of difficulty with parameters/filters. I have a run-time parameter
setup which then filters the report. The parameters themselves work fine,
but I want them to be optional. In other words, I want a parameter option
that shows me all the records. I would think an expression would work, but
I can't seem to get the syntax to work quite right. Is there an expression
or another setup?My first suggestion is to stay away from filters unless you absolutely have
to use them. With a filter all the data is brought down from the database
and then filtered by RS. What is much better (much higher performance) is to
apply it with the query.
First, how to do an all. Use a union query (or if you have hard coded the
parameters just add this).
select somefield as value, another field as label from a table union
select 'All' as value, 'All' as label
next your query for the data.
Select blah, blahblah from mytable where anotherfield = @.MyParam or @.MyParam
= 'All'
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn" <crmpro@.gmail.com> wrote in message
news:12107632558864480156250@.news.west.cox.net...
> hey there, relatively new new SRS, but lots of Crystal background. I'm
> having a bit of difficulty with parameters/filters. I have a run-time
> parameter setup which then filters the report. The parameters themselves
> work fine, but I want them to be optional. In other words, I want a
> parameter option that shows me all the records. I would think an
> expression would work, but I can't seem to get the syntax to work quite
> right. Is there an expression or another setup?
>

parameters filters

Hi,
I have created a simple report that uses the parameter customer_code as
selection criteria. When users use the report they can only select one
customer code at a time. How can I change this to accept an asterisk for a
range of customers? Is Reporting services able to do this?
xp_aol@.discussions.com> I have created a simple report that uses the parameter customer_code as
> selection criteria. When users use the report they can only select one
> customer code at a time. How can I change this to accept an asterisk for a
> range of customers? Is Reporting services able to do this?
Here is an example how to use IN operator with list of values as a
parameter:
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com

Parameters

Hi all,
Is it possible to group parameters fields into logical groups in report
view?
eg.
filters:
start date:
end date:
colors:
table border:
chart background:
...
TIA,
KamelThere would be nice to have separate control to manage parameters
presentation and logic.
Sometimes there is a need to have a lot of parameters (over 20) and
there is a presentation problem in reportviewer.
Kamel
kamel wrote:
> Hi all,
> Is it possible to group parameters fields into logical groups in report
> view?
> eg.
> filters:
> start date:
> end date:
> colors:
> table border:
> chart background:
> ...
> TIA,
> Kamel

Parameterized filters on publication?

Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?

I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?

Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?

Thanks,

Patrick Kafka

sorry, i'm not following you. Can you give an example of what exactly you want to filter on and what parameters you're referring to, or how you envision your filter to look like?|||

You can achieve what you wish using the SUSER_SNAME filter.

Lets say you have a table: customers (id int, Name varchar(20)

and it has the following values:

(1, ;'Steve')

(2, 'Bill')

(3, 'Peter')

(4, 'Jay')

Lets say your filter is: subset_filterclause='Name=SUSER_SNAME()'

With this filter, when a mobile user comes in (say Bill), he will send value Bill to SUSER_SNAME and hence get row with id=2.

Similarly for Peter, the row with id=3 will be sent.

Is that what you want to achieve?

Parameterized filters on publication?

Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?

I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?

Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?

Thanks,

Patrick Kafka

sorry, i'm not following you. Can you give an example of what exactly you want to filter on and what parameters you're referring to, or how you envision your filter to look like?|||

You can achieve what you wish using the SUSER_SNAME filter.

Lets say you have a table: customers (id int, Name varchar(20)

and it has the following values:

(1, ;'Steve')

(2, 'Bill')

(3, 'Peter')

(4, 'Jay')

Lets say your filter is: subset_filterclause='Name=SUSER_SNAME()'

With this filter, when a mobile user comes in (say Bill), he will send value Bill to SUSER_SNAME and hence get row with id=2.

Similarly for Peter, the row with id=3 will be sent.

Is that what you want to achieve?

Parameterized filters

Hi all;
I know that Parameterized filters allow different partitions of data to be
sent to different Subscribers without requiring multiple publications to be
created (parameterized filters were referred to as dynamic filters in
previous versions of SQL Server). A partition is simply a subset of the rows
in a table; depending on the settings chosen when creating a parameterized
filter, each row in a published table can belong to one partition only
(which produces nonoverlapping partitions) or to two or more partitions
(which produces overlapping partitions).
I need some examples and good resources talking about this.
Any suggestions
Apart from BOL, and the 2 books mentioned in my home page
(www.replicationanswers.com) I don't think you'll find a great deal of
specific material on this. Are there any particular questions you want to ask
about the setup?
Paul Ibison