Environment: Project server 2010, MS SQL Server 2008 R2 non- SharePoint Integrated Mode
Objective: To limit users to only be able to access their own Projects/Programs in SSRS Report Parameter.
1. Create a report dataset with below Query and set the data source connection string ProjectServer_Reporting Database..
SELECT R.ResourceNTAccount, P.ProjectOwnerName, P.ProjectName FROM MSP_EpmProject_UserView AS P INNER JOIN MSP_EpmResource AS R ON P.ProjectOwnerResourceUID = R.ResourceUID WHERE (R.ResourceNTAccount = @UserCredentials)
2. Click on Parameter in the Dataset Properties Dialog box then Click the fx (expression button)
3. Select the built-in field which is UserID and Click the Ok button.
4. After you select the built-in “User ID” field in the expression. Screen will look like below. Close the dataset properties dialog box. Build the Report and run now it will give you the desired result. Bingo!!