Appending a custom sql where clause

Hello,
I am in the process of evaluating SSRS 2005 to replace an home grown
reporting tool. In my reporting application all users have access all
the tables\fields in the database. In the home grown tool, data
security is implemented by the following mechanism. When users run
reports, a standard sql where clause is appended to the sql generated
by the reproting tool. This standard where clause has @.userID as the
parameter.
Now is there a way in SSRS 2005 I can append a standard where clause to
every report just before it is run? Does it have an event model, I can
hook into?
Thanks
_Gigi JKIn most cases query parameters are mapped to report parameters but they do
not have to be, they can be mapped to expressions. When mapping a query
parameter to an expression you can map the query parameter to a global
variable. One of the global variables available is User!UserID. This
variable has the user (and their domain). If you don't want the domain then
you would strip the domain off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<gigijk@.gmail.com> wrote in message
news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> Hello,
> I am in the process of evaluating SSRS 2005 to replace an home grown
> reporting tool. In my reporting application all users have access all
> the tables\fields in the database. In the home grown tool, data
> security is implemented by the following mechanism. When users run
> reports, a standard sql where clause is appended to the sql generated
> by the reproting tool. This standard where clause has @.userID as the
> parameter.
> Now is there a way in SSRS 2005 I can append a standard where clause to
> every report just before it is run? Does it have an event model, I can
> hook into?
> Thanks
> _Gigi JK
>|||Bruce,
Thanks for the reply. Sorry I sent the same question to you directly from my
gmail address as well.
In my case, reporting application will be intergrated into another
application which does not use NT auth. Is there a way I could pass in userID
to reporting tool?
On the original issue, what I want to do is to automatically append a where
clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
user_id=@.userID') to every query generated by the reporting tool withou the
user intervention. Would this be posssible.
Once again thank you.
_GJK
"Bruce L-C [MVP]" wrote:
> In most cases query parameters are mapped to report parameters but they do
> not have to be, they can be mapped to expressions. When mapping a query
> parameter to an expression you can map the query parameter to a global
> variable. One of the global variables available is User!UserID. This
> variable has the user (and their domain). If you don't want the domain then
> you would strip the domain off.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <gigijk@.gmail.com> wrote in message
> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
> > Hello,
> > I am in the process of evaluating SSRS 2005 to replace an home grown
> > reporting tool. In my reporting application all users have access all
> > the tables\fields in the database. In the home grown tool, data
> > security is implemented by the following mechanism. When users run
> > reports, a standard sql where clause is appended to the sql generated
> > by the reproting tool. This standard where clause has @.userID as the
> > parameter.
> >
> > Now is there a way in SSRS 2005 I can append a standard where clause to
> > every report just before it is run? Does it have an event model, I can
> > hook into?
> >
> > Thanks
> > _Gigi JK
> >
>
>|||You can have a hidden parameter (i.e. it does not prompt for it but you can
include it when running the report).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"GJK" <GJK@.discussions.microsoft.com> wrote in message
news:D37B86F8-AA55-4FF1-BC01-C426D806AF02@.microsoft.com...
> Bruce,
> Thanks for the reply. Sorry I sent the same question to you directly from
> my
> gmail address as well.
> In my case, reporting application will be intergrated into another
> application which does not use NT auth. Is there a way I could pass in
> userID
> to reporting tool?
> On the original issue, what I want to do is to automatically append a
> where
> clause ( for eg: 'AND sysem_id IN (SELECT System_id FROM Mdu_system where
> user_id=@.userID') to every query generated by the reporting tool withou
> the
> user intervention. Would this be posssible.
> Once again thank you.
> _GJK
> "Bruce L-C [MVP]" wrote:
>> In most cases query parameters are mapped to report parameters but they
>> do
>> not have to be, they can be mapped to expressions. When mapping a query
>> parameter to an expression you can map the query parameter to a global
>> variable. One of the global variables available is User!UserID. This
>> variable has the user (and their domain). If you don't want the domain
>> then
>> you would strip the domain off.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <gigijk@.gmail.com> wrote in message
>> news:1137098143.325204.240880@.g47g2000cwa.googlegroups.com...
>> > Hello,
>> > I am in the process of evaluating SSRS 2005 to replace an home grown
>> > reporting tool. In my reporting application all users have access all
>> > the tables\fields in the database. In the home grown tool, data
>> > security is implemented by the following mechanism. When users run
>> > reports, a standard sql where clause is appended to the sql generated
>> > by the reproting tool. This standard where clause has @.userID as the
>> > parameter.
>> >
>> > Now is there a way in SSRS 2005 I can append a standard where clause to
>> > every report just before it is run? Does it have an event model, I can
>> > hook into?
>> >
>> > Thanks
>> > _Gigi JK
>> >
>>