Filter SSRS Report Content or Visibility based on User ID

Challenge

Using one standard report template (RDL file), restrict and customize the report content for individual users. Users in Location A can see their own data but not that of Location B. Accommodate exceptions such as managers that may be authorized to see data from both Location A and Location B. We don’t want to use local groups to administer SSRS report access and security permissions.

I have hundreds of Locations so hundreds of RDL files with their own ACLs is not an acceptable maintenance proposition.

Solution

Use the Windows User ID of the report viewer to determine visibility properties of report elements. Use the SSRS User!UserID object (which contains the visiting users Windows User ID) as both a report Variable and a Parameter. Each can be used for slightly different things.

Create a database table (called Vistors) containing their Windows User IDs and their authorized Location ID. Users like managers may have multiple entries if they are authorized to view multiple Locations. The easiest way is to create an Excel .csv and use the SQL Server data import tool (or bulk copy) to get it into the table.

In Report Builder setup Data Sources so you can access this Visitor table then setup a Visitor Data Set with

SELECT UserID, LocationID FROM Visitors WHERE UserID = (@Visitor)

Create a hidden Visitor parameter with a default value using the following expression

=Right(User!UserID, Len(User!UserID) – InStr(User!UserID, “\” ))

When the report is loaded, the Users!UserID object gets populated and passed to the Data Set query. The query then returns any LocationIDs for the Visitor.

From here you can use a nested parameter to select data by matching LocationIDs. You can also assign User!UserID to a global report variable called Visitor and use this variable to set the report item visibility propert.

Why the two different methods? You can’t use report Variables in a Dataset Query (in the TSQL). You can’t use Parameter values in the Visibility property of a report item such as a Tablix. It is also not possible to use Parameters!Users.Value as an attributes in a Lookup() expression from within a Tablix. Pretty stupid, huh?

To make the Variable in Report Builder, right click on the report background and select the Variables tab of the report properties dialog box and use the same expression

=Right(User!UserID, Len(User!UserID) – InStr(User!UserID, “\” ))

In the Report Builder set the report element Visibility property (of say a Tablix) with the following expression

=IIF(Variables!Visitor.Value = Lookup(Fields!Facility.Value, Fields!Facility.Value, Fields!UserID.Value, “Users”), False, True)

Run and test the report.

The Results

If the report viewer’s user ID is in the Visitors table, the tablix should be visible. If not, the tablix is  hidden.

For example, the Parameter value can be used in the TSQL query to filter the data set on page load using syntax like,

SELECT * FROM table WHERE some_field IN (@Visitor)

The global report variable provides a means to set the Visibility property of the report object (Tablix).

Advertisements

2 thoughts on “Filter SSRS Report Content or Visibility based on User ID

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s