SSRS Reports Data - Security Levels

  • 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

  • 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.[/url] Get your answers faster.

  • Thanks alot for your quick response and can you please explain bit detail pl?

  • 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.[/url] Get your answers faster.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply