Filtering User-Specific Report Data in SQL Server Reporting Services
Due to security requirements, a lot of organizations have a need to display data specific to the user accessing the report. With increasing emphasis on standards compliance, many organizations are taking the protection of private information more seriously. There are a couple of different techniques for creating user-specific reports. This example explores two techniques.
What You’ll Need
A Matrix report
An understanding of query parameters
An understanding of report filters
Designing the Report
Creating a user-specific report requires the underlying data source to know the DomainUsername (UserID) of the user accessing the report in order to retrieve user specific data. Retrieving user-specific data requires the underlying data source to have a relationship between users and the data they have permission to view. This relationship can be defined in the underlying data source using appropriate table structures.
For instance, if an employee can only view sales data from a region he/she has access to in the relational database, there must be some relationship between the UserID and the Region. This relationship can be observed in the DimEmployee table of the AdventureWorksDW2008 sample database for SQL Server 2008 or AdventureWorksDW database for SQL Server 2005. Each employee record in the DimEmployee table contains a UserID and the related SalesTerritoryKey.
Once the underlying data source has established a relationship between the UserID and the data it has access to, it’s time to think about how to display user specific data in the report. There are two ways of displaying user specific data:
Use report filters to filter user-specific data based on the user accessing the report.
Use query parameters to restrict data returned by the data source for the user accessing the report.
NOTE. We are assuming that the report data source uses a predefined service account, not Windows Security, to access the underlying data source.
This example demonstrates using both the options to display user specific data. You use the AdventureWorksDW for SQL Server 2005 or 2008 sample database to display sales information for the sales territories a user has access to. A simple query is used to return the reseller sales amount, sales territory, calendar year, and employee first name, last name, and UserID (loginID). This example uses Report Builder 2.0.
1. Start by designing a data source for the AdventureWorksDW2008 or AdventureWorksDW sample database.
2. Create a dataset query to include the FactResellerSales, DimSalesTerritory, DimEmployee, and DimDate tables, using the following SQL script:
INNER JOIN dbo.DimSalesTerritory ST
ON RS.SalesTerritoryKey = ST.SalesTerritoryKey
INNER JOIN dbo.DimEmployee E
ON RS.EmployeeKey = E.EmployeeKey
INNER JOIN dbo.DimDate T
ON RS.OrderDateKey = T.DateKey
NOTE. In AdventureWorksDW database for SQL Server 2005, Date table is called DimTime instead of DimDate and DateKey column is called TimeKey.
3. Add a Matrix report item to the report body.
4. Select the SalesTerritoryCountry field in the Row cell, CalendarYear field in the Column cell, and SalesAmount field in the Data cell.
At this point, you have retrieved reseller sales data for all employees. Let’s preview the report to see what it looks like. As you can see in Figure P7-60, it’s just a simple report that summarizes reseller sales amount for all employees over calendar year and sales territory.
Now you want to view the sales information for territories to which the user accessing the report has access. As discussed earlier, there are two possible ways of achieving this: by using report filters or by using query parameters. Let’s first look at using a report filter to view user specific data.
1. Right-click on Dataset and click Dataset Properties.
2. Select Filters on the Dataset Properties and click Add to add a new filter.
3. In the newly created blank filter, select LoginID field for Expression field, Equal to Operator, and enter the User!UserID expression for Value (Figure P7-61).
4. Click OK to close the Expression and the Dataset Properties dialog box.
Reporting Services captures the DomainUsername of the user accessing the report and stores it in a global object called UserID. In the SSRS 2008 report designer and Report Builder 2.0, these objects are referred to as Built-in Fields. The
filter uses the value of this object to compare the UserID of the user accessing the report to the LoginID dataset field, thus, providing user specific data by filtering the data where LoginID is equal to the login name of the user accessing the report. Let’s preview the report to view the results.
As you can see in Figure P7-62, the report is blank and doesn’t display any data. This is the correct behavior because you are accessing the report using your credentials, and the AdventureWorks sample database doesn’t have your credentials. So, there is no relationship between your credentials and the data you have access to; in other words, you don’t have access to any data.
To validate your implementation, you need to replace one of the employee’s credentials in the DimEmployee table with your credentials. This way, you should see the data the employee has access to.
1. Connect to the AdventureWorksDW database you are using for this report.
2. Replace the LoginID field value of an Employee with your credentials (DomainUsername). In this example, we are going to update the record where EmployeeKey value is equal to 285.
3. Open a new query in SQL Server Management Studio, connect to the same database as the report and run the following query.
setLoginID=’ your credentials ‘
Your credentials should be in the form of DomainUsername. If your computer isn’t part of the domain, the credentials should be in the form of ComputerNameUsername. If you would like to find out the credentials SSRS stores in the UserID global variable, drag a textbox onto the report surface, enter the User!UserID expression, and preview the report.
4. Once you have replaced the credentials of an employee with your credentials, preview the report. As you can see in Figure P7-63, the data is now restricted to United States sales territory because the employee with EmployeeKey 285 is related to United States sales territory only. If you do not see any data, refresh the report to clear the cache.
Now that you have created a user-specific report using report filters, let’s look at the second option for achieving the same result. The second option uses query parameters to restrict data returned by the data source.
1. Right-click on Dataset and select Dataset Properties.
2. Select Filters on the Dataset Properties dialog box and delete the Filter created earlier.
3. Select Query on the Dataset Properties dialog box and update the query by inserting the following where clause:
where E.LoginID = @ReportUser
4. This creates a parameter called @ReportUser. Select Parameters on the Dataset Properties dialog box and enter the following expression for the parameter value (see Figure P7-64):
5. Click OK to close the Expression and Dataset Properties dialog boxes.
Again, you are using the UserID global variable to pass the DomainUsername of the user accessing the report as a parameter value for our query. This restricts data at the data-source level, as the query only returns data for the user accessing the report. It is important to understand the differences between the two options demonstrated in this example.
Preview the report to test the results. As you can see in Figure P7-66, the data is exactly the same as when you used the report filter option (see Figure P7-65).
User-specific reports help organizations comply with increased emphasis on standards, protecting private information, and specific security requirements. In this example you looked at two different techniques for creating user-specific reports: using report filters to filter data at the report level and using query parameters to restrict data at the data-source level. It’s important to note that both techniques require the underlying data source to contain a relationship between the UserID and the data the user has permission to view. However, both techniques differ in how they filter the data, so it’s also important to understand the differences between the two techniques.
This article is excerpted from Part VII: "Filtering and Parameterization"
of the book "Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports " by Paul Turley, Robert M. Bruckner (ISBN: 978-0-470-56311-3, Wrox, 2010, Copyright Wiley Publishing Inc.)Source: blogs.wrox.com