locked
Base report data on the user running the report? RRS feed

  • Question

  • Guys,

    I'm creating a report which is going to list the tasks a particular user has. Rather than having a drop down list and the user select their name to display a report I'd like the report to 'detect' the user who's trying to access it and then pass this into the report.

    Is this possible? If so how would I go about doing it?

    Say for instance it was a dead simple query of 'select * from tasks where task_owner = X'

    At the moment I'd be producing a dataset of all tasks and then a data set of users, I'd then add a paramater which mapped X to a username.

    We don't have AD tied in with the database which stores this data so I guess I'll need a mapping between AD username and database username. Would the AD usernames be in the format of domain\username or just username?

    Hopefully I've explained myself okay, any help much appreciated :)

    Thursday, June 21, 2012 4:22 PM

Answers

  • The built in Field called User ID in Reporting Services will return the logged in users name in the format of domain\username

    If you must have database users that are not Windows users, then yes I would have a user mapping table in the database where you could relate each database user to a respective domain\username and write a query that includes task owner joined to the mapping table using the database user. 

    User mapping fields: WindowsUser, DatabaseUser

    View tasksowners:

    Select * from tasks t 

    INNER JOIN

    usermapping u

    on

    t.task_owner = u.databaseuser

    In the report add an Internal parameter @UserID with a specified default value of Built In Field User!UserID

    Dataset query would be

    Select * FROM tasksowners WHERE WindowsUser = @UserID


    DWM

    • Proposed as answer by Syed Qazafi Anjum Thursday, June 21, 2012 8:53 PM
    • Marked as answer by Mike Yin Monday, June 25, 2012 1:24 AM
    Thursday, June 21, 2012 4:40 PM