0

I recently encountered a requirement which said "Manager should see all record but Associates only appropriate subset. I will explain my requirement with below example.

I have a table.

CREATE TABLE [dbo].[Empolyee](
    [EmpID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Salary] [int] NULL,
    [Grade] [char](1) NULL
)

and some data

insert into Empolyee values(1, 'John', 200, 'C')

insert into Empolyee values(2, 'Jim', 100, 'D')

insert into Empolyee values(3, 'Jane', 500, 'A')

insert into Empolyee values(4, 'Jack', 150, 'C')

insert into Empolyee values(5, 'Jil', 300, 'B')

insert into Empolyee values(6, 'Josh', 350, 'B')

A is the highest grade, D is lowest.

I want to employee detail report in SSRS

simply showing

select * from Empolyee.

Now the requirement is user should be able to see his/her record and people at lower Grade.

E.g. If Jane see report she see should see all 6. If Jack see report she see should see only 2(his and Jim's)

I could see this is case of role based security but how to implement this?

Pritesh
  • 1,938
  • 7
  • 32
  • 46

1 Answers1

2
Select B.[Name] AS [Name2],B.[Salary] AS [Salary2],B.[Grade] AS [Grade2] 
from Empolyee A
Cross jOIN Empolyee B
WHERE A.Grade <= B.Grade
and A.Name like 'Jack' -- mention your user here
AND B.EmpID NOT IN (Select EmpID FROM Empolyee C 
 WHERE C.Grade = A.Grade and C.Name <> A.Name)
Asif
  • 2,657
  • 19
  • 25
  • wow! can handle it query level.... Thiw we can control records shown! Can we control Columns... e.g. A can see salary column all other can not. something like this can be implemented? – Pritesh Jul 06 '12 at 12:59
  • 1
    That can easily be achieved by setting the visibility property for the salary column Example =iif(Parameters!Grade.Value = "A",False,True) – praveen Jul 06 '12 at 14:30
  • @Pritesh yes it can be acheived. Have my answer work for you? – Asif Jul 06 '12 at 17:18
  • @Asif yes now i am able to control no. of record... How to control columns? – Pritesh Jul 07 '12 at 02:39