Filter Expression

Hi

I have some data that need to be filtered based on a SET of Id's.
If it's about a single ID, then i would pass it as a parameter in a
stored procedure and use it within the the WHERE Clause, but here those
ID's are determined in run time and I can't simply create a a stored
procedure for an unknown amount of ID's.
I looked into the SQL Server 8.0 Manual but had no examples how to use
the Function Filter.
Generaly, how can filter some records based on a set of ID's?

Best regardscoosa (coosa76@.gmail.com) writes:
> I have some data that need to be filtered based on a SET of Id's.
> If it's about a single ID, then i would pass it as a parameter in a
> stored procedure and use it within the the WHERE Clause, but here those
> ID's are determined in run time and I can't simply create a a stored
> procedure for an unknown amount of ID's.
> I looked into the SQL Server 8.0 Manual but had no examples how to use
> the Function Filter.
> Generaly, how can filter some records based on a set of ID's?

FROM tbl t
JOIN iter_intlist_to_tbl(@.list_of_ids, DEFAULT) f ON t.id = f.number

iter_intlist_to_tbl is table-valued function that unpacks a space-
separared list of integers into table which you then join with.

Code at http://www.sommarskog.se/arrays-in-...ist-of-integers.
The rest of the article provides more discussion about this kind of problem.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

filter by UserID

I have many reports that are filtered by the userID of the person running
the report. Aside from rolling my own security is there a way for me to use
RS to pass this ID. Since the user is logging in via Windows Authentication
I have their login name. I was thinking of using this to query the UserID
from a custom table and then use that as a hidden parameter for all the
sp's. The problem I have though is how to get that UserId prior to anything
on the report happening.
Thanks,
ShawnThe user!userid global variable can be used as a input to your query. Do the
following, create a query parameter. Click on the ..., go to parameters and
then map the query parameter to the global variable (it has domain so you
might want to strip off the domain). To map it choose expressions and that
brings up the expression builder. Next go to layout, report parameters and
remove the parameter that was automatically created for you by RS when you
created the query parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Shawn Mason" <shawn@.issda.com> wrote in message
news:%23YFHG1L$EHA.3416@.TK2MSFTNGP09.phx.gbl...
> I have many reports that are filtered by the userID of the person running
> the report. Aside from rolling my own security is there a way for me to
use
> RS to pass this ID. Since the user is logging in via Windows
Authentication
> I have their login name. I was thinking of using this to query the UserID
> from a custom table and then use that as a hidden parameter for all the
> sp's. The problem I have though is how to get that UserId prior to
anything
> on the report happening.
> Thanks,
> Shawn
>|||Here's something I did, to strip off the domain, as Bruce says:
SELECT e.EMPLID, e.NAME
FROM EMPLTABLE e
WHERE (e.ID = RIGHT(@.UserID, LEN(@.UserID) - CHARINDEX('\', @.UserID)))
It will use whatever you write after the \ in a domain\username scenario.
Kaisa M. Lindahl
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OiMDkHM$EHA.3908@.TK2MSFTNGP12.phx.gbl...
> The user!userid global variable can be used as a input to your query. Do
the
> following, create a query parameter. Click on the ..., go to parameters
and
> then map the query parameter to the global variable (it has domain so you
> might want to strip off the domain). To map it choose expressions and that
> brings up the expression builder. Next go to layout, report parameters and
> remove the parameter that was automatically created for you by RS when you
> created the query parameter.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Shawn Mason" <shawn@.issda.com> wrote in message
> news:%23YFHG1L$EHA.3416@.TK2MSFTNGP09.phx.gbl...
> > I have many reports that are filtered by the userID of the person
running
> > the report. Aside from rolling my own security is there a way for me to
> use
> > RS to pass this ID. Since the user is logging in via Windows
> Authentication
> > I have their login name. I was thinking of using this to query the
UserID
> > from a custom table and then use that as a hidden parameter for all the
> > sp's. The problem I have though is how to get that UserId prior to
> anything
> > on the report happening.
> >
> > Thanks,
> >
> > Shawn
> >
> >
>