I hear this question a lot: how can i restrict my data in my report to a specific users, there are a couple of ways to approach this:
- You can send the current user running the report to the Query/Stored procedure as parameter. Go to the datasource and add a parameter, e.g. @UserID with as value User!UserID. Now the current user is available to the database. If you want to get the login that is currently executing the SP from within the SP, then you can use system_user.
- You can set the visibility of a field based on the current user running the report. Go to the visibility properties of the Field and use a expression te determine True or False, for example use in the expression: “=Iif(User!UserID = “SOMEUSER”, False, True)”, you can use this for rectangles and lists as well.
- Or you can get the data from a Cube and use the features there, more on that in My Blog on Cube security.
One reminder, never make security decisions based on report parameters. Rather use something like Harden MS Reporting Services Using Custom Extensions