sql server reporting services: Hi friends,
I have one issue. In our organization there are CEO, Managers, asst
managers, TeamLead, Teammembers.

There is a report which displays all of them data. But what I want is
if the user is CEO then he should be able to see all other's data in
that report.

if the user is Managers then they should be able to see all asstmanager
and teamlead and members data in the same report.

if the user is Asstmanager then he should be able to see teamlead and
members data in the same report and NOT his superior's data.

like that Teammembers should be able to see their own data and other
team members data too. But not superiors data.

How can we implement this at report level? We don't have any active
directory setup. I want just only using system_user from sqlserver.
Thanks in advance,
Chris

Like these many reports are there. So the model should be become common
to all reports. Really I am waiting for a good suggestion and advices.
Thanks,
Chris

I agree...but even the user has read only, they will be able to see the
entire report right? that is even though he is a asst manager, he can
read the data if the query contains 'all' parameter. What I want is the
reader should be able to retreive the data upto his level of permission
in the report.
Sorry I am very new to this area. hence I am not that much clear to you
beleive.
So how can we implement that?

Thanks a ton for your detailed reply. I got some clarity.
Yea we have domain names and the reports accessability is based on
domainname\userid.
so Is there any way to access part of the report to the users basing on
their credentials?
Regards,
Chris.

Are you saying that you are coming in using Anonymous. Note that how you get
to the data to report on is different from how the users are being
validated. I have all my reports use a special read only user for
credentials (I have the SQL Server set to mixed mode). However, they are
still all validated as themselves. Given that you can use the User!userid
global variable as an input to a query where the user is mapped to their
category (CEO etc).


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services


[quoted text, click to view]

Let me explain further. You say you don't have active directory. Are you
saying you have no domains, no users setup etc. No local users setup on the
Reporting Services server? How are you planning on giving access rights to
the reports.

Note that you can setup IIS to allow anonymous access but then you are going
to have other troubles (when anonymous you will find that all the Report
Manager administration selections are inaccessible).

Each data source is setup with the credentials to use when running the
report. It is possible to setup the report to run as the user of the report.
I don't do that, I have the report run as a particular user that I have
setup specifically for reporting. However, I setup the roles for the reports
for particular users. Two different things the credentials used to run the
report and validating the user to see what role they have with Reporting
Services (browse role, admin role, etc). Even if I am in the admin role when
I run a report it runs using a different credential.

The user!userid global variable says who is running the report.

Try this, put a textbox on a report. Have nothing else, just the textbox.
Set the textbox to this expression:

=User!Userid

Run the report and see what you get


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services


[quoted text, click to view]

Try creating a report as I suggested with a single textbox (no datasets) and
set it to an expression (you can build the expression with the expression
builder). You will see that when you run the report you know who is running
it. All you need is a mapping table and then based on that you either filter
the data or have your query do it.

For instance

select * from sometable where sometable.department id in (select
department_id from mygroups where userid = @UserID)

then instead of having UserID be mapped to a report parameter you map it to
the global parameter.

Note that subscriptions will not work with this because the userid is not
available when a subscription is running.


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services


[quoted text, click to view]