Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Reports Data - Security Levels Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 12:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:12 AM
Points: 30, Visits: 76
Hi Floks,
Could you please kindly give some ideas for this below issue.

I have the below data in the data base

emp no empname esal eloca edob egrade
1111 ABC 1000 NZ 01/01/1960 A
2222 BCA 2000 IR 01/01/1961 B
3333 BAC 3000 US 01/01/1962 C
4444 BBC 4000 US 01/01/1963 D
5555 ACB 6000 UK 01/01/1964 E

Please answer for the below scenarios.

1) When the user execute the report he should only see his data only - how will it be possible..Because we have got single report for all users to execute?

2) for some other scenario for the same report - IF either 3333,4444 execute the report ,then they need to get only data relevant to the location US
how this kind of ristriction is possible for business users?

3) I have not given any example here, however the question is If i ran the report then i need to check my data and the person who are working under me related data should be visible in my report and if my manager run the data he need to check my data and the person who are working under me data as well...How this can be done?

Thanks for your kind help

Regards



Post #1474767
Posted Wednesday, July 17, 2013 1:07 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 1,264, Visits: 3,595
Here's one solution

There is a Built-in Field named UserID (=User!UserID). This userID is the user's name that is passed through the reporting system when the report is run. This can either be saved in a hidden/internal variable and used in the query returning your main data set or used to populate another empNo variable after cross referencing your user table and the UserID variable.


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1474774
Posted Wednesday, July 17, 2013 1:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:12 AM
Points: 30, Visits: 76
Thanks alot for your quick response and can you please explain bit detail pl?
Post #1474775
Posted Wednesday, July 17, 2013 1:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:28 AM
Points: 1,264, Visits: 3,595
User!UserID displays the name of the user (domain name or other credentials passed in) executing the report, assign this field to a parameter in your report.
report parameter: @userID
value: =User!UserID

With some string manipulation, use this parameter in your main query returning results
SELECT
empNo,
empName,
esal
FROM table
WHERE empName = @userID

If empName != @userID you will have to cross reference a user table (if you have one) with the userID variable. You'll need another dataset and another report parameter.

SELECT
empno
FROM userTable
WHERE networkID = @userID

report parameter: @empNo
value: =First(Fields!empno.Value, "dataset name")

Then select your main dataset
SELECT
empNo,
empName,
esal
FROM table
WHERE empNo = @empNo

This should get you something to start with, it will require some tweaking on your part depending on your database.


______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #1474782
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse