can view be dynamically filtered like an MSAccess query?

In MSAccess you can dynamically filter a query by adding square brackets [ ]
to the criteria section and a prompt inside the brackets [enter filter code]
.
Then you can open the query directly and be prompted for a filter. I tried
this in the view without any success.
I was approached today by an End user who uses an Access ADP to interface
with our sql server. The user needs to pull/look at data from a large
table, and currently there is no mechanism for filtering the respective tabl
e
in the Access ADP. Since several users are using the same ADP, I don't want
to modify it. I was thinking of creating a view that could be opened from
the ADP. But rather than having the user call me everytime he needs to
change the criteria, is there a way to make the view dynamic?
Thanks,
RichRich,
You can use an inline table-valued function. You can not pass parameters to
a view.
Inline User-Defined Functions
http://msdn.microsoft.com/library/d...>
_08_73lf.asp
AMB
"Rich" wrote:

> In MSAccess you can dynamically filter a query by adding square brackets [
]
> to the criteria section and a prompt inside the brackets [enter filter cod
e].
> Then you can open the query directly and be prompted for a filter. I tri
ed
> this in the view without any success.
> I was approached today by an End user who uses an Access ADP to interface
> with our sql server. The user needs to pull/look at data from a large
> table, and currently there is no mechanism for filtering the respective ta
ble
> in the Access ADP. Since several users are using the same ADP, I don't wa
nt
> to modify it. I was thinking of creating a view that could be opened from
> the ADP. But rather than having the user call me everytime he needs to
> change the criteria, is there a way to make the view dynamic?
> Thanks,
> Rich|||Thanks - very . I will give this a shot. The only thing is that - afte
r
reading the article - it looks like the user needs to write a sql statement.
If the user is using an Access ADP - where does he write the statement to us
e
the inline function? In the article it looked like the user had QA on his
desktop.
"Alejandro Mesa" wrote:
> Rich,
> You can use an inline table-valued function. You can not pass parameters t
o
> a view.
> Inline User-Defined Functions
> http://msdn.microsoft.com/library/d...
es_08_73lf.asp
>
> AMB
> "Rich" wrote:
>|||I figured it out. The function will show up in the user interface. The use
r
can invoke the function the same way as a query in MSAccess and will be
prompted for input. Very . Thanks again for the suggestion.
"Rich" wrote:
> Thanks - very . I will give this a shot. The only thing is that - af
ter
> reading the article - it looks like the user needs to write a sql statemen
t.
> If the user is using an Access ADP - where does he write the statement to
use
> the inline function? In the article it looked like the user had QA on his
> desktop.
> "Alejandro Mesa" wrote:
>

Can report results be filtered by Windows user login?

Good morning,

I'm in the process of creating a report to show employees and managers holiday and absence information. Is there a way of filtering the results of the report based on who is running the report, so that employees could only see their own information and managers could only see theirs and their subordinates information?

What I was hoping to do was create a lookup table which cross-references Windows logins with employee numbers and then use this information to pass a parameter to the SQL query, but I don't know how to retrieve the login from the machine being used to view the report.

I've heard about row level security and it seems ideal in theory but I fear the implimentation of row level security would be far beyond my meagre knowledge.

Any constructive suggestions welcomed.

Thanks,

Paul

Hello Paul,

Whenever you create an expression, take a look under the Globals node. One your options is User!UserID, this will give you the Windows login of the user running the report.

Hope this helps.

Jarret

|||

Jarret,

I came across the same solution a little earlier, works a treat.

Thanks,

Paul