Dynamic Row Level Security

Hi,
Is it possible to configure Reporting Services 2005 so that the same report
will apply different data filters depending on the user running the report ?
i.e A German user will only see German data and an English user will only
see English data even if they enter a parameter for 'All Europe'.
Thanks.RS supports a property User!UserID which returns the identity of the
interactive user (assuming Windows authentication). You can pass the user
identity to the data source as a query parameter to implement data filtering
at the data source.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
"Duncan Allen" <DuncanAllen@.discussions.microsoft.com> wrote in message
news:B76E2612-99F1-490B-AFBF-E11B5280BE43@.microsoft.com...
> Hi,
> Is it possible to configure Reporting Services 2005 so that the same
> report
> will apply different data filters depending on the user running the report
> ?
> i.e A German user will only see German data and an English user will only
> see English data even if they enter a parameter for 'All Europe'.
> Thanks.

dynamic operator ?

Is there anyway we can have dynamic operator in filters '
instead of > i want to put parameters!operator.valueYes but you need to generate your sql dynamically... see my post her
http://www.sqltalk.org/ftopic34616.htm
So you'll need a datasource of > < = >= <= then get you
user to pick from the field
Then dynamically add that parameter into your sq
so you'd have 'WHERE quantity ' + @.operator + @.valueparamate
Then exec the sql

dynamic filters

i am working on a publication which has about 400 tables. i want to define
dynamic filters, but i dont want to use EM.
how can i add the filters on query analyzer?
Create one publication and subscription using dynamic filters. Script it
out, and edit the script. Drop the publication and use the script to deploy
it to the publisher and all subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"uncle ho" <uncle ho@.discussions.microsoft.com> wrote in message
news:9B2CF8CE-A90E-4F75-BE32-785283A01432@.microsoft.com...
> i am working on a publication which has about 400 tables. i want to define
> dynamic filters, but i dont want to use EM.
> how can i add the filters on query analyzer?

dynamic filters

Does anybody know what will happen when I use different functions to filter
rows on articles (dynamic filters). In some articles I want to filter using
the HOST_NAME() function, in other rows I want to use the function
USER_NAME()
Is this possible or should this be avoided ?
Noel,
There shouldn't be any problem per se. What I could immediately think of and
suggest is that you look at any relationships that exist among these tables.
These 'might' cause some trouble, where rows in one article that match one
filter may not match with rows in another related table with another filter
(tricky!!, does this make sense?).
Raj
|||Thanks
My statement was not complete. What we need is the in one article we can
filter on host_name, in another article however, we need to filter on
User_name. So, it is not that we want to place to filters on one article.
"Raj Moloye" <rkmoloye@.hotmail.com> schreef in bericht
news:OrezH3HHEHA.3832@.TK2MSFTNGP10.phx.gbl...
> Noel,
> There shouldn't be any problem per se. What I could immediately think of
and
> suggest is that you look at any relationships that exist among these
tables.
> These 'might' cause some trouble, where rows in one article that match one
> filter may not match with rows in another related table with another
filter
> (tricky!!, does this make sense?).
> Raj
>
|||be careful with user_name(), this resolves to the user kicking off the
agent, which most frequently is your merge agent or distribution agent, and
not the account that the user is logged on with.
"Nol Thoelen" <noel.thoelen@.itomni.be> wrote in message
news:4073c410$0$2070$ba620e4c@.news.skynet.be...
> Thanks
> My statement was not complete. What we need is the in one article we can
> filter on host_name, in another article however, we need to filter on
> User_name. So, it is not that we want to place to filters on one article.
>
> "Raj Moloye" <rkmoloye@.hotmail.com> schreef in bericht
> news:OrezH3HHEHA.3832@.TK2MSFTNGP10.phx.gbl...
> and
> tables.
one
> filter
>

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

Dynamic Filter Operator

Here is what I am trying to do in SSRS 2005.

Setting up filters based on parameters with an expression like this:

=Iif(Parameters!Company.Value = "", "", Fields!company.Value)

In the wizard we are creating, we are also letting the user choose the operator for each parameter that they choose. My question is how can I change the filter dynamically based on the user choosing a specific parameter and also choosing an operator to associate with that parameter?

Example 1: User 1 chooses the Company parameter to filter their report, and they choose the parameter to equal (=) a specific value. So the filter expression would be like the one previously mentioned and the operator would be an equal sign.

Example 2: User 2 chooses the Company parameter to filter their report, and they choose the parameter to be LIKE a specific value. So the filter expression would be like the one previously mentioned and the operator would be LIKE.

How can I do this?

Thanks in advance for your help.

You indicate you have a parameter named "Company"

first:
Create a parameter named "operator" and give it values "equals" and "like"

label value

equals equals
like like

Set the default value if you choose.

then:
Create a parameter named "filter" and leave it blank

Open the "table properties" and select "filter"

1st expression -
for the filter expression like:
=Iif(Parameters!Operator.Value = "equals", "", Fields!company.Value))
for the operator:
select the "like" operator
for the value:
=Iif(Parameters!Operator.Value = "equals", "", Switch(Parameters!Filter.Value = Parameters!Filter.Value, Parameters!Filter.Value & "*", Parameters!Filter.Value = nothing, "*"))

2nd expression -
for the filter expression equals:
=Iif(Parameters!Operator.Value = "like", "", Fields!company.Value))
for the operator:
select the "=" operator
for the value:
=Iif(Parameters!Operator.Value = "like", "", Parameters!Filter.Value)

This only covers one data field so when you select "like" you can enter the leter "A" in the filter parameter and all fields that start with "A" will be returned.
However if you select "equals" you need to know exactly what to type in other wise a dropdown would be great here.
If you leave the filter parameter blank and select "like" it will return all the data.
The options are endless. . .

|||

That is exactly what I was looking for!

Thanks very much

dynamic filter - mobile device

Hi,
I am using mobile devices as subscribers and a desktop as a publisher.
If i want to use dynamic filters, do I really have to use host_name?
If the mobile devices have a web service method called ReturnId() that
returns the unique id that identifies the mobile device and the id can be
verified against the database that is being synchronised,
is it possible to do dynamic filters based on this webservice method?
Thank you.
-HOSTNAME can be used as a parameter to the merge agent and a value hardcoded
there. If the value returned from the webservice never changes then it could
be read when creating the subscription and used to define the merge agent's
job.
HTH,
Paul Ibison
|||Hi,
I am terribly sorry but I do not quite understand.
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
how do I modify your solution accordingly if it is at all possible?
eg, if i arbitrarily set my mobile devices with the ids: 1, 2, and 3 for 3
different devices, how can I still use those values as parameters inside your
dynamic filters?
I am a novice at this, thank you for your patience and advice.
"Paul Ibison" wrote:

> -HOSTNAME can be used as a parameter to the merge agent and a value hardcoded
> there. If the value returned from the webservice never changes then it could
> be read when creating the subscription and used to define the merge agent's
> job.
> HTH,
> Paul Ibison
>
|||I'm assuming you run the merge sync programatically on the mobile device - if
this is the case, you'll be using the "SqlCeReplication" class. This class
has a "HostName" property that can be set to the return value from your
webservice. Please let me know if this makes sense fior your case.
HTH,
Paul Ibison

Dynamic Dynamic Filters

Hello,
I am attempting to use dynamic filters on merge replication to filter rows. In my front end app I am using the ActiveX Merge object to set the Host_Name property. My question is: Is it possible to set the host name to a more complex string?
example:
.HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
The problem I am running into is that the sql wizard for creating the dynamic filters runs a check on the sql statement and won't allow something like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
Technically if I could somehow bypass the wizard to enter the filter rules this should work. Anyone know if this is possible? Or possibly another way to work around this so that you can pass more than 1 value to the where clause? I suppose you could do so
mething like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
Would this be the only way? Any feedback would be greatly appreciated. Thanks in advance.
I'm unsure on what your filtering condition is. For instance your filter
should evaluate to a boolean true or false. Yours evaluates to hostname.
You can use a UDF to extend the functionalty of your filter or you can do
stuff like this
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
or use a subquery
SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
Servername from ServerList where state='ca')
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> Hello,
> I am attempting to use dynamic filters on merge replication to filter
rows. In my front end app I am using the ActiveX Merge object to set the
Host_Name property. My question is: Is it possible to set the host name to a
more complex string?
> example:
> .HostName = "Col = 'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> The problem I am running into is that the sql wizard for creating the
dynamic filters runs a check on the sql statement and won't allow something
like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
> Technically if I could somehow bypass the wizard to enter the filter rules
this should work. Anyone know if this is possible? Or possibly another way
to work around this so that you can pass more than 1 value to the where
clause? I suppose you could do something like:
> SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = Host_Name()
> HostName = "'Val1' OR (Col = 'Val2' AND Col = 'Val3')"
> Would this be the only way? Any feedback would be greatly appreciated.
Thanks in advance.
>
>
|||Yes mine purposely evaluates to hostname. What I am trying to do is set the filter to:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Host_Name()
and then set hostname to something like:
Col = 'Val1' OR Col = 'Val2'
So that the resulting select will look like:
SELECT <published_columns> FROM [dbo].[Table1] WHERE Col = 'Val1' OR Col = 'Val2'
Or whatever other complex WHERE clause that I choose. The problem is that it won't except the above filter. My work around listed above should work (I have yet to test it). Although I am interested in other possibilites to achieve the same results. This s
eems fairly basic unless I am missing something?
"Hilary Cotter" wrote:

> I'm unsure on what your filtering condition is. For instance your filter
> should evaluate to a boolean true or false. Yours evaluates to hostname.
> You can use a UDF to extend the functionalty of your filter or you can do
> stuff like this
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() like 'p%'
> or use a subquery
> SELECT <published_columns> FROM [dbo].[authors] WHERE host_name() in (select
> Servername from ServerList where state='ca')
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Steve" <Steve@.discussions.microsoft.com> wrote in message
> news:D7626D05-A0A6-4F19-963D-8B588A20AC6E@.microsoft.com...
> rows. In my front end app I am using the ActiveX Merge object to set the
> Host_Name property. My question is: Is it possible to set the host name to a
> more complex string?
> dynamic filters runs a check on the sql statement and won't allow something
> like:
> this should work. Anyone know if this is possible? Or possibly another way
> to work around this so that you can pass more than 1 value to the where
> clause? I suppose you could do something like:
> Thanks in advance.
>
>
|||Steve,
you could have a mapping table which relates values (Val1 and Val2) to
hostnames. In your example there would be 2 rows:
-- MAPPINGTABLE --
hostname1 val1
hostname1 val2
If you include this table in the filter, you can achieve the clause you want
to create.
HTH,
Paul Ibison
|||Paul,
I am not familiar with mapping tables and I have been unable to find any information relating to them. Could you provide an example of what you mean or possibly a link to some information? Thanks.
|||Steve,
it's easiest if I explain how I set up a test: I had 2 tables - region and
HostnameLookup - shown below. The HostnameLookup table defines the multiple
values I'm interested in. I use dynamic filters to filter the HostnameLookup
table using HOST_NAME() and a join filter to join to the region table.
Editing the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer means that only 2 regions get replicated. I've listed the
exact text below in case you want to recreate it to test.
HTH,
Paul Ibison
Region
RegionID RegionDescription rowguid
-- -- --
1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
HostnameLookup
RegionDescription Hostname rowguid
-- -- --
Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
Filter Clause for HostnameLookup:
SELECT <published_columns> FROM [dbo].[HostnameLookup]
WHERE HostnameLookup.Hostname = HOST_NAME()
Filter Clause for Region:
< All rows published >
Join Filter:
Filtered table is HostnameLookup
Table to FIlter is Region
SELECT <published_columns> FROM [dbo].[HostnameLookup]
INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
region.regiondescription
Edit the 2nd step on the merge agent's job to include: -Hostname
PaulsComputer
Run the snapshot then merge agents and only 2 regions should be replicated.
|||Hi Paul,
I saw your suggested solution in another web page
http://www.replicationanswers.com/Merge.asp
I was wondering if this Host_Name() is a function that will return the
computer name of the device/desktop that the subscriber database is residing
on?
If I am using mobile devices as subscribers and they have a web service
method called Reader_Id() which returns the unique id of the mobile device,
can I modify your solution accordingly?
Thank you.
"Paul Ibison" wrote:

> Steve,
> it's easiest if I explain how I set up a test: I had 2 tables - region and
> HostnameLookup - shown below. The HostnameLookup table defines the multiple
> values I'm interested in. I use dynamic filters to filter the HostnameLookup
> table using HOST_NAME() and a join filter to join to the region table.
> Editing the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer means that only 2 regions get replicated. I've listed the
> exact text below in case you want to recreate it to test.
> HTH,
> Paul Ibison
> Region
> --
> RegionID RegionDescription rowguid
> -- -- --
> 1 Eastern 9A6377F0-70DF-4A5C-962D-B41A41EFA82B
> 2 Western C0CAEFAD-7B8C-43D6-87ED-920494A5C60C
> 3 Northern 6CD9AA33-BE2D-4DCF-B447-745A3B86818E
> 4 Southern B4826E41-96D6-457C-8645-DD4984AE3BF5
> HostnameLookup
> --
> RegionDescription Hostname rowguid
> -- -- --
> Northern PaulsComputer 2367C78A-1001-417B-A3B1-1C74B23F8131
> Southern PaulsComputer 4F563C4D-8479-4A7D-A938-490DD514F12A
>
> Filter Clause for HostnameLookup:
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> WHERE HostnameLookup.Hostname = HOST_NAME()
> Filter Clause for Region:
> < All rows published >
> Join Filter:
> Filtered table is HostnameLookup
> Table to FIlter is Region
> SELECT <published_columns> FROM [dbo].[HostnameLookup]
> INNER JOIN [dbo].[Region] ON Hostnamelookup.regiondescription =
> region.regiondescription
> Edit the 2nd step on the merge agent's job to include: -Hostname
> PaulsComputer
> Run the snapshot then merge agents and only 2 regions should be replicated.
>
>

Dynamic and Join Filters with Multipple clauses

Hello,
I have a strange problem with merge replication. Our database has a users
table and an Orders table. we are succesfully using suser_sname() as our
first clause on the users table. This by itself works fine and only that
users data are returned to the PDA. However, when the second clause is
added, this is a clause on orders table ie. active='YES' which returns the
active orders only, then SQL seems to return a union of these 2 clauses
rather than using them both at the same time.
Any ideas?
I've seen this before - the view that is created uses a union rather than an
extra AND clause in the select statement. I suppose a workaround would be to
use an indexed view and join the data yourself, or you could add each filter
to each table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for your prompt reply. I am hacking the view in question and things
seem to work. I would have thought that such view behaivour is rather
strange.
Let's hope Microsoft fix this bug/feature in the future so that it does not
require weird work arounds.
Many thanks
"Paul Ibison" wrote:

> I've seen this before - the view that is created uses a union rather than an
> extra AND clause in the select statement. I suppose a workaround would be to
> use an indexed view and join the data yourself, or you could add each filter
> to each table.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Thanks for the update. I'll take a look at SQL Server 2005 this weekend and
post back to see if the fix exists.
Cheers,
Paul Ibison