Environment: Project server 2010, MS SQL Server 2008 R2 non- SharePoint Integrated Mode
Objective: To limit users to being only 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!!
Client Requirement: Recently, we have received the new Report requirement on User permissions across projects. However, we didn’t find any out of the box report so we have created the custom report. Report requirement mock report is pasted below:
Note: User Permission against said Project we have categorized as per below logic:
- Project managers who have published a project or who have Save Project permissions on a project are added to the Project Managers (Microsoft Office Project Server) site group.
- Team members with assignments in a project are added to the Team members (Microsoft Office Project Server) site group.
- Other Project Server users with View Project Workspace permission on a project are added to the Readers (Microsoft Office Project Server) site group.
Solution: To achieve above report we have fetched data from the following databases –
1. ProjectServer_Reporting Datbase for resources those are assigned in projects and owner of projects.
2. ProjectServer_PublishedDatabase for resources those are part of projects but haven’t assigned to any task in the Project.
Please note in below query we have used the Union all operator to combine the result-set of two SELECT statement to add the project resources data those are not assigned to any activity in the projects.
SQL Query Developed:
SELECT DISTINCT PRR.ResourceName as UserName, PRR.ResourceNTAccount as UserFullName, case when PRR.ResourceIsActive=1 then ‘Active’ else ‘Inactive’ end as ResourceStatus, P.ProjectName, case when PRR.ResourceUID=p.ProjectOwnerResourceUID then ‘ProjectManager’ when A.ResourceUID is not null then ‘Team Member’ else ‘Reader’ end as ProjectPermission,
p.ProjectWorkspaceInternalHRef as ProjectUrl FROM dbo.MSP_EpmProject_UserView P
INNER JOIN (Select R.ResourceName,R.ResourceNTAccount,R.ResourceIsActive,R.ResourceUID,PR.Proj_UID
from Badal_Published.dbo.MSP_PROJECT_RESOURCES PR
INNER JOIN dbo.MSP_EpmResource_UserView R ON PR.RES_UID = R.RESOURCEUID UNION ALL
Select R.ResourceName,R.ResourceNTAccount,R.ResourceIsActive,R.ResourceUID,PR.ProjectUID as Proj_UID from DBO.MSP_EpmProject_UserView PR
INNER JOIN dbo.MSP_EpmResource_UserView R ON PR.ProjectOwnerResourceUID = R.RESOURCEUID)as PRR on p.ProjectUID=PRR.Proj_UID
LEFT OUTER JOIN dbo.MSP_EpmAssignment_UserView A ON P.PROJECTUID = A.PROJECTUID and a.ResourceUID=PRR.ResourceUID
where ResourceNTAccount is not NUll
Hope this helps you.
Note: Direct access to the Published DB is not supported so please try at your own risk.