SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dodaniel
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?

sunitabeck
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

dodaniel
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

visakh16
Very Important crosS Applying yaK Herder

India
52317 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
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000