SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Reports Data - Security Levels


SSRS Reports Data - Security Levels

Author
Message
TomGuide
TomGuide
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 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
calvo
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1808 Visits: 4008
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.
TomGuide
TomGuide
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 76
Thanks alot for your quick response and can you please explain bit detail pl?
calvo
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1808 Visits: 4008
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search