User level usage at the Report level.

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.

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.


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,
Cladi

Here is the way i was able to get by it

CREATE PROCEDURE GetFundingSummary
@OrganizationID int,
@StartDate DateTime,
@EndDateDateTime,

AS

DECLARE @orgNamevarchar(30)
DECLARE @orgTypevarchar(15)
DECLARE @lastLoadDateDateTime

/* Get Organization Name, Type and Last Load Date */
SELECT@orgName = Name,
@orgType = OrganizationType,
@lastLoadDate = dbo.GetLastLoadStatusDate()
FROM dbo.vOrganization
WHERE OrganizationId = @OrganizationId

if @orgType = 'CEO'
SELECT
@orgName As OrganizationName,
@orgType As OrganizationType,
blah,
blah
Else -- If they are another role
If @orgType = 'VP'
Select
blah ,
blah ,



then in the report for any field you want to hide say a list for instance you want to go to there Visibility property and type this in the expression

=IIF(OrganizationType = “CEO”, false, true)...

I hope this helps...