This article is to exlain how to use Userid and ReportName from Reporting Service 2005. The goal of this article is to show an alert message to users when they are not allowed to see a report. I think this is more explicit than using Reporting Services security. We can also extend this method by filtering rows depending on the user.

First, you have to create a report server project within BIDS 2005. Add an element on this report, for example a table component. Then you need to create 2 variables :

• User (String type, Internal)
• ReportName (String type, Internal)

You will use these variables to store the user name and the current report name. The UserID global contains the domain name and the user name :

<domain name>\<user name>

To get the user name only, we will have to split the variable like this:

=CStr(Choose(2,Split(User!UserID,"\"))

This value will be stored in a report variable like this :

To get the report name, it is simple. We only need to store it to a variable like this :

In order to filter correctly the report, we will have to create a SQL table which contains the name of the user and the report associated. This table will be created in a user db :

CREATE TABLE UserRights(
User varchar(50),
Report varchar(50),
Visible bit)

Here is some sample data, with two users and a report.

The request to check the user right will be :

--Request that check user rights
SELECT count(*) as OK 
FROM UserRights 
WHERE User = @User and Report = @ReportName and Visible = True

We need to add a new dataset we will name TestReport that contains the request above. I suggest you create a stored procedure for that. For hiding the element, you need to edit the visibility property and add an expression :

The expression will look like this :

When the user is not allowed to see the report, you will have to add a textbox element and use the expression above on the visibility property. You just have to switch the True/False like this :

=IIf( Sum(Fields!OK, "TestReport", False, True)

Finally we need to display an error message to the user whom cannot see the report :

And choose an "expression" as a value :

You can easily hide report from non allowed users by using this technique. This method can be extended by adding column in the users table wich will be used to filter a query and hide non allowed lines.