You've been asked to build a sales report for your client. One of the requirements is to have the sales region automatically filter to the sales reps region when the user accesses the report. You have the report built, but are not sure how to set the report to automatically filter based on the specific user of the report.

I this article I will walk you through how to construct parameters to default values based on user credentials. For demonstration purposes I am using Sales Region as it is a common filter in sales reports. However, you can use these same instructions for any number of parameters.

(Note: The following article was written using SQL Server 2008 R2 and backwards compatibility has not been tested.)

Assumptions:

• You have access to a sql server database and the ability to create tables.
• You have a Reporting Services report already built and need to change the report behavior to default parameter values based on user credentials.

Create Report Users Table

You'll need a place to store user credentials and parameter values. To do this, create a report users table in your sql server database.

CREATE TABLE [dbo].[ReportUsers](
 [UserID] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [nvarchar](50) NOT NULL,
 [SalesRegion] [nvarchar](50) NOT NULL
) ON [PRIMARY]

At this point you don't need to populate the table with any data. We'll get to that in a moment. The key fields are UserName which will be the value passed from the Reporting Services report and SalesRegion which will hold the parameter value.

You'll need to ensure that your parameter matches the data type of the field you are creating in your report users table. For example, if your parameter is nvarchar(100) you'll need to make your SalesRegion field nvarchar(100).

Create UserID Parameter in Your Report

To create the UserID parameter, start by creating a parameter in reporting services.

Setup the General Settings:

• Data Type must be Text
• Visibility is set to Hidden (your choice here, but users won't be changing this value so Hidden works best).

Click on Available Values and enter [&UserID] into the Label and Value boxes.

• [&UserID] is a built in variable in reporting services.
• You can add this value using the expressions button or simply type it into the label and value fields.

Click on Default Values and [&UserID] into the value box.

Click ok.

One last step, you need to ensure that the parameter you just created is at the top of the list of parameters for your report.

If parUserID is not at the top of the list, click on the parameter and click the up arrow until it is. Your parameters should now look something like this.

(Note: Reporting services sets parameters in order or precedence based on the top to bottom list. Our UserID parameter needs to be first on the list in order for the Sales Region parameter to receive its value.)

Sales Region Data Set

So far you've done the basic stuff. Now comes the part I found most difficult. Rest assured though, I am going to take you through this step-by-step and show you some tricks along the way.

I'm assuming you already have a sales region parameter setup. So let's take a look at how you constructed that parameter.

First open the SalesRegion data set.

In order for the Sales Region parameter to accept the UserID parameter, you need to setup your query to identify the user and find the correct sales region value. The query below does this for you:

IF @parUserID IN (SELECT UserName FROM dbo.ReportUsers)
BEGIN
   SELECT DISTINCT 
     SalesRegion
   FROM datatable 
   WHERE 
      (SalesRegion IN
      (SELECT SalesRegion
       FROM ReportUsers
      WHERE (UserName IN (@parUserID))))
END
ELSE
 SELECT DISTINCT 
 SalesRegion
 FROM datatable
 WHERE SalesRegion IN ('Central', 'West', 'East')

Add this query to your Sales Region data set. As shown in the screenshot.

This SQL statement is doing two major things for you. First it is looking for the user's UserID in the ReportUser's table we created. And secondly, if that userID is not found it is selecting all sales regions. This is critical when you have users who are not assigned a sales region or are not in your report user table.

Sales Region Data Set #2

Create another data set and this time call the data set "dsSalesRegion2". Set your query as such:

SELECT DISTINCT 
 SalesRegion
 FROM datatable
 WHERE SalesRegion IN ('Central', 'West', 'East')

Your datasets on your report should now look like this:

Sales Region Parameter

Open your sales region parameter and go to the Available Values settings. You're going to set your Available Values settings to the Sales Region #2 data set. This comes in handy when you have users are not assigned to specific regions such as sales directors or GM level users.

Set the default values:

You have effectively setup the sales region parameter to default based on the user credentials and also setup the parameter to display all regions. This allows your users to select any region they want even though the report auto defaults.

Report User Data

Now that you have your report user table created, your userID parameter setup and your sales region parameters ready to go, all you need to do is add data to your report user table.

The UserID variable in Reporting Services uses DOMAINNAME\username as it's format. So when you add users to your report user table, you'll need to use this same format when inserting records. For example:

INSERT INTO dbo.ReportUsers
VALUES
 (
 'ACME\jsmith',
 'Central'
 )

I suggest you first add yourself to this table so you can unit test the report. If you have done everything correctly, the next time you run the report your sales region should automatically default to the value you entered into the report users table.

Helpful Tips

1. At first you may want to make your parUserID parameter visible to help with testing.
2. You cannot Cache a temporary copy of the report in report manager if you using a Shared Dataset with stored credentials.
3. If you only want report users to view their default region, you can set the Available Values in the Sales Region Parameter to use the dsSalesRegion data set instead of the dsSalesRegion2 data set.

Conclusion

SQL Server Reporting Services built-in functionality and some strategic SQL code is all you need to default parameter values based on user credentials. In this article, I explained how to accomplish this using a common filter such as sales region. You should now be able to take this knowledge and apply it to any user driven parameter.