Retrive Value in SQL

Posted by Write2varun under Sql Server on 10/25/2013 | Points: 10 | Views : 773 | Status : [Member] | Replies : 3
hi

I have to create a SSRS Report to retrieve the value according to this condition:

A,B,C,D,E are Five users think,
A as Junior developer
B as developer
C as senior developer
D as project manager

E as Sr project manager

A reports to B
B reports to C
C reports to D
D reports to E

Then:

when A logs in he can only see his records
when B logs in he can see the records for A,B and not able to see the C,D and E records
when C logs in he can see the records for A,B,C and not able to see the D and E records
when D logs in he can see the records for B,C,D and not able to see the A and E records
and when E logs in he can see the records for C,D,E and not able to see the A,B records

Is there any way to retrieve the value this way?

thank you




Responses

Posted by: Bandi on: 10/25/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
I'm giving the sample input and out put along with the query...


--Sample data
DECLARE @rec TABLE(Emp_ID int, Emp_Name VARCHAR(10), Emp_Mnager_ID INT)
INSERT INTO @rec
SELECT 1, 'Admin', Null UNION ALL
SELECT 2, 'John', 1 UNION ALL
SELECT 3, 'Sam', 2 UNION ALL
SELECT 4, 'Mike', 2 UNION ALL
SELECT 5, 'Jeff', 4 UNION ALL
SELECT 6, 'Ben', 3 UNION ALL
SELECT 7, 'Vicky', 5

/* Sample Data
Emp_ID Emp_Name Emp_Mnager_ID
1 Admin NULL
2 John 1
3 Sam 2
4 Mike 2
5 Jeff 4
6 Ben 3
7 Vicky 5 */

-- Suppose Logged in user is 2 (John), then he should get the details of all sub ordinates (2, 3, 4, 5, 6 and 7 user details)

-- Enter Loggin user is
DECLARE @mgr_id INT= 2

-- Query to get all sub ordinates under him
;WITH
unrolled_branch AS
(
SELECT
emp_id,
emp_name,
emp_mnager_id
FROM
@rec
WHERE
emp_mnager_id = @mgr_id

UNION ALL

SELECT
r.emp_id,
r.emp_name,
r.emp_mnager_id
FROM
@rec r
INNER JOIN
unrolled_branch
ON r.emp_mnager_id = unrolled_branch.emp_id
)
SELECT
*
FROM
unrolled_branch

/*OUTPUT
emp_id emp_name emp_mnager_id
3 Sam 2
4 Mike 2
5 Jeff 4
7 Vicky 5
6 Ben 3
*/


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Write2varun, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/25/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer this link
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f7c21789-6da4-4412-a256-d57a69605fc0/using-authorization-with-reporting-services?forum=sqlreportingservices

this will give you an idea

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Write2varun, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/28/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi varun,
1) Create one dataset with the each user and their level...
2) use the following links for filtering results based on user-role
http://blogs.wrox.com/article/filtering-user-specific-report-data-in-sql-server-reporting-services/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Write2varun, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response