Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Pass User Info as a Filter for a Report
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

11 Posts

Posted - 09/07/2012 :  17:40:09  Show Profile  Reply with Quote
Is it possible to pass information from the user that is running a report from Report Manager as a filter for the report? For example, if I only want a person to see information for their department on a report, is there a way to identify their department from their user information and use it as a filter for the report?

Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/08/2012 :  07:11:48  Show Profile  Reply with Quote
There is a built-in field called UserId in SSRS that you can use to get the logged in user. One way would be to define an internal parameter with the default value being this build-in UserId field and send that parameter to your query. Then, in the SQL query, you can filter based on the user.

If you want to filter based on a department, then you can store the UserId to Department relation in SQL table and use that table to determine which department the user belongs to and filter accordingly.
Go to Top of Page

Starting Member

11 Posts

Posted - 09/13/2012 :  16:35:25  Show Profile  Reply with Quote
I created a view that has LDAP info for all our users that includes their Employee Number, Department Number and Login Name.

I can match the built-in field UserID (=User!UserID) to the Login Name field in my view but I don't know how to use this to filter my records so I only see records from the UserID's Department number.

I have tried adding the view as a second "LDAP Info" dataset and then in my Main dataset creating a parameter for =User!UserID and naming it ReportUser. I do not know how to filter my Main dataset for the Department Numbers that match the UserID department number in my second dataset.
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 09/13/2012 :  16:46:02  Show Profile  Reply with Quote
you dont need separate datasets. you just need to tweak query of your main dataset to add a join to user LDAP table and then add the filter based on User!UserID

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000