Export (0) Print
Expand All

Using Project Server Security in SQL Server Reporting Services Reports

Office 2007

Summary: Learn how to use the programmability features of Microsoft SQL Server 2008 Reporting Services to incorporate the security model of Microsoft Office Project Server 2007 in your reports. (20 printed pages)

MVP Icon Stephen C. Sanderlin, MSProjectExperts

December 2009

Applies to:Microsoft Office Project Server 2007, Microsoft SQL Server 2008 Reporting Services

Contents

You can download the code sample for this article from the MSProjectExperts site that describes the forthcoming book, Developer's Guide to Microsoft Project Server 2010.

NoteNote

The book addresses both Project Server 2007 and Microsoft Project Server 2010. This article is adapted from a chapter in that book.

Although Microsoft Office Project Server 2007 includes reporting functionality, many customers want to expand or customize their business intelligence experience beyond the default capabilities. It is not surprising that users often choose Microsoft SQL Server 2008 Reporting Services (SSRS) because of its rich feature set and ability to integrate directly with Windows SharePoint Services 3.0. Project Server 2007 uses Windows SharePoint Services 3.0 as a platform foundation.

Project Server 2007 includes a dedicated reporting database; however, the Project Server system provides no way to restrict access to the information in the reporting database, other than by using the security capabilities of SQL Server 2008. This can be a burden on administrators.

In this article, I present a scenario to demonstrate how developers can combine the programmability features of Project Server 2007 and SSRS to secure the data presented by SSRS reports.

NoteNote

Code for this article was developed on a server running Windows Server 2008 x64 Service Pack 2, Microsoft Office SharePoint Server 2007 x64 Service Pack 2 with the October 2009 Cumulative Update, Project Server 2007 x64 Service Pack 2 with the October 2009 Cumulative Update, Microsoft Visual Studio 2008 Service Pack 1, and SQL Server 2008 Service Pack 1 with Reporting Services and the Business Intelligence Development Studio installed.

Scenario Overview

You are a senior developer in your organization's Microsoft Enterprise Project Management (EPM) practice. This practice designs, deploys, and customizes Project Server 2007 and related products for both internal customers and external customers.

You are currently working on a Project Server 2007 deployment engagement for one of your company's largest clients, Contoso. Moments ago, you received an e-mail message from your team's technical lead informing you of a new "must-have" requirement from the customer.

The customer requested an SSRS report that displays various information about the projects that are contained within their Project Server 2007 deployment. The report must display information only to users who are members of either the Project Managers group or the Administrators group, and must display information in compliance with the user permissions in Project Server.

The @ProjectList parameter in the report calls two methods in the ProjectList class: GetAllowedProjectLabels, which retrieves the project names for use as parameter labels, and GetAllowedProjectValues, which retrieves the project UIDs for use as parameter values. Both of these methods return string arrays to the report.

Figure 1. High-level logic overview of ReportingPSISecurity

High-level logic overview of ReportingPSISecurity

The ProjectList class communicates with the Project Server Interface (PSI) to retrieve the following information about the current user:

  1. Resource information

  2. Resource authorization information

  3. A list of accessible regular projects

  4. A list of accessible master projects

  5. A list of accessible subprojects

The ProjectList class stores the name and UID of each project within the _allowedProjects private member. Both methods called by the @ProjectList report parameter extract the data from _allowedProjects and convert it into a string array for use by SSRS.

When SSRS renders the @ProjectList parameter, the ProjectList instance goes out of scope, which destroys the data stored in _allowedProjects. Because the list of projects that the current user can access is assembled by the class only once per report execution, you can be confident that the name and UID of a project will match in the report parameter.

If the current user is not a member of the Project Managers group or the Administrators group or does not have access to at least one project in Project Server, the ProjectList class returns an empty array and the SSRS report parameter does not contain any values. This behavior prevents the report from rendering and fulfills the customer's security requirements.

Although you can also return the list of allowed projects from the ProjectList class and process the data by using code embedded in the report, I prefer to consolidate all of the code into a single class library.

With the design finalized, you can start to develop the PsiProxies class library that your report uses to communicate with the PSI. You use the following procedures:

  • Create the class library project and set references.

  • Prepare the ProjectList class for further development.

  • Create a ProjectDerived class for impersonation.

  • Develop the ProjectInfo class.

  • Develop the ResourceInfo class.

  • Test whether a user is a project manager or administrator.

  • Filter resource data for the current user of the report.

  • Retrieve resource information from the PSI.

  • Retrieve a list of projects that the current user can access.

  • Create public interfaces for the ProjectList class.

Procedure 1. To create the class library project and set references

  1. Open Visual Studio 2008.

  2. Create a Class Library project named PsiProxies. Name the solution ReportingPsiSecurity, and select the Create directory for solution check box. Click OK.

  3. In Solution Explorer, right-click the PsiProxies project, and then click Properties. In the PsiProxies dialog box, on the Signing tab, select the Sign the assembly check box, and then create a new keyfile. Close the PsiProxies dialog box.

  4. In Solution Explorer, rename Class1.cs to be ProjectList.cs.

  5. In Solution Explorer, right-click the PsiProxies project, and then click Add Reference. In the Add Reference dialog box, add a reference to the Microsoft.Office.Project.Server.Library.dll assembly. Following is the default location of the assembly: %ProgramFiles%\Microsoft Office Servers\12.0\Bin\

    NoteNote
    If you are developing on a 64-bit server, you should manually browse to [Drive]\Program Files instead of using the %ProgramFiles% environment variable. Because Visual Studio 2008 is a 32-bit application running under the WOW64 x86 emulator, %ProgramFiles% resolves to [Drive]\Program Files (x86) in a 64-bit environment.
  6. In the Add Reference dialog box, on the .NET tab, add a reference to System.Web.Services.dll.

  7. In the Add Web Reference dialog box, add a Web reference to the Resource Web service and name it ResourceSvc. Following is the default location of the Resource Web service: http://servername/instancename/_vti_bin/psi/resource.asmx

    NoteNote
    To access the Add Web Reference dialog box in Visual Studio 2008, right-click the PsiProxies project in Solution Explorer, and then click Add Service Reference. In the Add Service Reference dialog box, click Advanced. In the Service Reference Settings dialog box, click Add Web Reference.
  8. Add a Web Reference to the Project PSI Web service and name it ProjectSvc. Following is the default location of the Project Web service: http://servername/instancename/_vti_bin/psi/project.asmx

  9. Open AssemblyInfo.cs, and then add the following assembly attribute:

    [assembly: AllowPartiallyTrustedCallers]
    

    For more information about assembly attributes, see Attributes (C# Programming Guide) and Applying Attributes. For more information about the AllowPartiallyTrustedCallersAttribute class, see AllowPartiallyTrustedCallersAttribute Class.

The Microsoft.Office.Project.Server.Library namespace contains enumerations and classes used by the PSI and event base methods.

For more information about finding and using PSI Web services, see Finding the PSI Web Services and Using the PSI in Visual Studio.

Procedure 2. To prepare the ProjectList class for further development

  1. Open ProjectList.cs, and then add the following lines to the global references section.

    using System.Net;
    using System.Security.Permissions;
    using PSLib = Microsoft.Office.Project.Server.Library;
    
  2. Remove the following lines from the global references section of ProjectList.cs.

    using System.Linq;
    using System.Text;
    
  3. Add the following private class constants and read-only fields to the ProjectList class.

    private const string _lcid = "1033";
    private const string _projectPsiLocalPath = "/PSI/Project.asmx";
    private const string _pwaUrl = "http://epmdemo/litware";
    private const string _pwaImpersUrl = "http://epmdemo:56737/EPM_SSP";
    private const string _resourcePsiVirtualPath =     
        "/_vti_bin/PSI/Resource.asmx";
    private readonly Guid _adminGroupUid = 
        new Guid("69fc9d0d-0b5f-4f4a-a9ef-c05a60eb5236");
    private readonly Guid _pmGroupUid = 
        new Guid("13ea50ff-dcd1-455a-844a-4df08af90808");
    private readonly Guid _pwaSiteUid = 
        new Guid("d48ac1f3-7ab7-4bda-a7d8-e45c22773218");
    
    NoteNote
    You must change the preceding constants to match your environment. To obtain the Project Web Access site ID, see Procedure 4.1 in How to: Write a Simple Impersonation Application. To obtain the LCID, see Locale ID (LCID) Chart. To obtain the group UID for a Project Server security group, open the Server Settings page in Project Web Access, click Manage Groups, and then click the group that you want. The group UID is the GUID of the groupUid option in the URL. For example, the following URL has a group UID of 69fc9d0d-0b5f-4f4a-a9ef-c05a60eb5236: http://servername/instancename/layouts/PWA/Admin/AddModifyGroup.aspx?groupUid=69fc9d0d-0b5f-4f4a-a9ef-c05a60eb5236
    NoteNote
    I simplified parts of this article's sample code. If you intend to deploy this sample code in production, consider retrieving the Project Web Access site ID, group UIDs, and URLs dynamically at run time instead of storing them as global constants in the ProjectList class.
  4. Add the following private class field to the ProjectList class.

    private List<ProjectList> _allowedProjects;
    
  5. Decorate the PsiProxies class with the following attribute.

    [PermissionSet(SecurityAction.Assert, Unrestricted = true)]
    

    This attribute decoration permits the PsiProxies class and all downstream callers to assert any permissions that they need, without restriction. For more information, see Using the Assert Method.

    For more information about class attributes, see Attributes (C# Programming Guide) and Applying Attributes. In addition, see PermissionSetAttribute Class (System.Security.Permissions).

  6. Add an empty constructor to the ProjectList class.

    public ProjectList()
    {
    }
    

SSRS executes any custom assemblies that it invokes in the security context of the process identity account of the SSRS instance. To retrieve a list of projects that the current user can access, you must use impersonation when communicating with the Project PSI Web service. Otherwise, the Web service simply returns a list of projects that the process identity account of the SSRS instance can access.

For more information about impersonating another user by using the PSI, see Using Impersonation in Project Server.

NoteNote

Although most of the sample code in this article is fully compatible with Project Server 2010, the method of impersonating the credentials of another user through the PSI has changed significantly since Project Server 2007. The impersonation functionality was under development as of the date of this article.

Procedure 3. To create a ProjectDerived class for impersonation

  1. In Solution Explorer, add a class named ProjectDerived.cs to the PsiProxies project.

  2. Replace the contents of ProjectDerived.cs with the following code.

    using System;
    using System.Net;
    using PSLibrary = Microsoft.Office.Project.Server.Library;
    
    namespace PsiProxies
    {
        class ProjectDerived : ProjectSvc.Project
        {
            private static String _contextString = String.Empty;
    
            protected override WebRequest GetWebRequest(Uri uri)
            {
                // Override the GetWebRequest method and 
                // add the two Web request headers.
                WebRequest _webRequest = base.GetWebRequest(uri);
                if (_contextString != String.Empty)
                {
                    _webRequest.UseDefaultCredentials = true;
    
                    _webRequest.Credentials = 
                        CredentialCache.DefaultNetworkCredentials;
    
                    _webRequest.Headers.Add("PjAuth", _contextString);
                    _webRequest.Headers.Add(
                        "ForwardFrom", "/_vti_bin/psi/project.asmx");
    
                    _webRequest.PreAuthenticate = true;
                }
                return _webRequest;
            }
    
            public static void SetImpersonationContext(
                bool isWindowsUser, String userAccount, 
                Guid userGuid, Guid trackingGuid, 
                Guid pwaSiteId, String lcid)
            {
                _contextString = GetImpersonationContext(isWindowsUser, 
                    userAccount, userGuid, trackingGuid, 
                    pwaSiteId, lcid);
            }
    
            private static String GetImpersonationContext(
                bool isWindowsUser, String userAccount, 
                Guid userGuid, Guid trackingGuid, 
                Guid pwaSiteId, String lcid)
            {
                PSLibrary.PSContextInfo _contextInfo =
                    new PSLibrary.PSContextInfo(isWindowsUser, 
                                                userAccount,
                                                userGuid, 
                                                trackingGuid, 
                                                pwaSiteId, lcid);
                String _contextString =
                    PSLibrary.PSContextInfo.SerializeToString(
                        _contextInfo);
                return _contextString;
            }
        }
    }
    

You must now create a simple class to store the information about each project that the current user can access. Because the project information is loaded into a report parameter, you need to store only the name and UID of the project.

Procedure 4. To develop the ProjectInfo class

  1. In Solution Explorer, add a class named ProjectInfo.cs to the PsiProxies project.

  2. Remove the following lines from the global references section of ProjectInfo.cs.

    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
  3. Modify the ProjectInfo class declaration to add an internal access modifier.

    using System;
    
    namespace PsiProxies
    {
        internal class ProjectInfo
        {
        } // ProjectInfo
    }
    
  4. Add two automatic properties to the ProjectInfo class: one stores the project UID and another stores the project display name.

    internal Guid ProjectUid { get; set; }
    internal string ProjectName { get; set; }
    

Now that you have a class to store project information, you must create a similar class to store information about the current user. You use this information when communicating with the Project PSI Web service through the impersonation class that you created in Procedure 3.

Procedure 5. To develop the ResourceInfo class

  1. In Solution Explorer, add a class named ResourceInfo.cs to the PsiProxies project.

  2. Remove the following lines from the global references section of ProjectInfo.cs.

    using System.Linq;
    using System.Text;
    
  3. Modify the class declaration of ResourceInfo to add an internal access modifier.

    using System;
    using System.Collections.Generic;
    
    namespace PsiProxies
    {
        internal class ResourceInfo
        {        
        } // ResourceInfo
    }
    
  4. Add five automatic properties to the ResourceInfo class: the first stores a Boolean value that indicates whether the user has a Windows authentication account, the second stores the user UID, the third stores the UIDs of the user groups, the fourth stores the user account name (for example, DOMAIN\User), and the fifth stores the user display name.

    internal bool IsWindowsUser { get; set; }
    internal Guid Uid { get; set; }
    internal List<Guid> Groups { get; set; }
    internal string Account { get; set; }
    internal string Name { get; set; }
    

Because the customer has specified that this report may present data only to users who are in either the Project Managers group or the Administrators group, you must parse the Groups property of the ResourceInfo class to ensure that the current user is a member of one of these groups.

Procedure 6. To test whether a user is a project manager or administrator

  1. In ProjectList.cs, create a method named IsProjectManagerOrAdmin.

    private bool IsProjectManagerOrAdmin(ResourceInfo resource)
    {
    } // IsProjectManagerOrAdmin()
    
  2. Develop the IsProjectManagerOrAdmin method. This method parses a ResourceInfo object to validate that the user is a member of either the Project Managers group or the Administrators group.

        return (resource.Groups.Contains(_adminGroupUid) ||     
                resource.Groups.Contains(_pmGroupUid));
    

The PSI uses various UIDs to identify entities in the system (for example, resources). Because you are interested in retrieving information about only the current user, you can use the filter functionality of the ReadResource method in the Resource Web service to limit the information that you retrieve. The preferred approach to building a filter is to create a dedicated method that assembles the filter and returns the filtering XML.

Procedure 7. To filter resource data for the report's current user

  1. In ProjectList.cs, create a method named BuildResourceFilter.

    private static string BuildResourceFilter(string username)
    {
    } // BuildResourceFilter()
    
  2. Develop the BuildResourceFilter method. This method creates a filter that restricts the resource information returned by the PSI so that it returns information only for the current user.

    PSLib.Filter _resFilter = new PSLib.Filter();
    
    using (ResourceSvc.ResourceDataSet _resData = 
        new ResourceSvc.ResourceDataSet())
    {
        _resFilter.FilterTableName = _resData.Resources.TableName;
        _resFilter.Fields.Add(
            new PSLib.Filter.Field(
                _resData.Resources.RES_UIDColumn.ColumnName));
        _resFilter.Fields.Add(
            new PSLib.Filter.Field(
                _resData.Resources.RES_IS_WINDOWS_USERColumn.
                    ColumnName));
        _resFilter.Fields.Add(
            new PSLib.Filter.Field(
                _resData.Resources.WRES_ACCOUNTColumn.ColumnName));
        _resFilter.Fields.Add(
            new PSLib.Filter.Field(
                _resData.Resources.RES_NAMEColumn.ColumnName));
    
        _resFilter.Criteria =
            new PSLib.Filter.FieldOperator(
                PSLib.Filter.FieldOperationType.Equal,
                _resData.Resources.WRES_ACCOUNTColumn.ColumnName, 
                username);
    } // End using
    return _resFilter.GetXml();
    

    For more information about filter parameters, see How to: Use a Filter Parameter with PSI Methods. In addition, see the ResourceDataSet Class (WebSvcResource).

After you create a method to build the filter that you want, your next task is to create a method that uses the filter to communicate with the Resource Web service to retrieve and extract the information that you want.

Procedure 8. To retrieve resource information from the PSI

  1. In ProjectList.cs, create a method named GetResourceInfo.

    private static ResourceInfo GetResourceInfo(string username)
    {
    } // GetResourceInfo()
    
  2. Develop the GetResourceInfo method by using the Resource Web service. The method retrieves the user account, display name, UID, whether the user is a Windows authentication user, and the user security groups. It then populates a ResourceInfo object with this information and returns it to the calling method.

    // PSI declarations.
    ResourceSvc.Resource _resourcePsi = null;
    ResourceSvc.ResourceDataSet _resData = new ResourceSvc.ResourceDataSet();
    ResourceSvc.ResourceAuthorizationDataSet _authData = 
        new ResourceSvc.ResourceAuthorizationDataSet();
    
    try
    {
        // Initialize PSI ref.
        _resourcePsi = new ResourceSvc.Resource()
        {
            Url = _pwaUrl + _resourcePsiVirtualPath,
            Credentials = CredentialCache.DefaultCredentials
        };
    
        #region Retrieve resource information
        // Retrieve resource data by using a filter.
        _resData = 
            _resourcePsi.ReadResources(BuildResourceFilter(username), 
                                       false);
    
        // Should receive at one result only.
        if (_resData.Resources.Count == 1)
        {
            // Populate basic resource information.
            ResourceInfo _resInfo = new ResourceInfo
            {
                Account = _resData.Resources[0].WRES_ACCOUNT,
                IsWindowsUser = 
                    _resData.Resources[0].RES_IS_WINDOWS_USER,
                Name = _resData.Resources[0].RES_NAME,
                Uid = _resData.Resources[0].RES_UID,
                Groups = new List<Guid>()
            };
    
            #region Retrieve group membership information
            // Retrieve resource authorization information.
            _authData = 
                _resourcePsi.ReadResourceAuthorization(_resInfo.Uid);
    
            // Populate resource authorization information.
            if (_authData.GroupMemberships.Count > 0)
            {
                foreach (ResourceSvc.ResourceAuthorizationDataSet.
                    GroupMembershipsRow _groupRow in 
                    _authData.GroupMemberships)
                {
                    _resInfo.Groups.Add(_groupRow.WSEC_GRP_UID);
                } // End foreach
            } // End if
            #endregion // Retrieve group membership information.
    
            return _resInfo;
        } // End if
        #endregion // Retrieve resource information.
    
        return null;
    } // End try
    
    finally
    {
        // Dispose of PSI objects.
        if (_resourcePsi != null) _resourcePsi.Dispose();
        if (_resData != null) _resData.Dispose();
        if (_authData != null) _authData.Dispose();
    } // End finally
    

    For more information, see Resource Class (WebSvcResource), Resource.ReadResources Method (WebSvcResource), and ResourceAuthorizationDataSet Class (WebSvcResource).

Now that you have all of the information necessary for impersonation and for determining whether the current user is permitted to execute the report, your next task is to build a method that communicates with the Project PSI Web service by using impersonation, to retrieve a list of projects that the user may access.

Procedure 9. To retrieve a list of projects that the current user may access

  1. In ProjectList.cs, create a method named GetAllowedProjects.

    private void GetAllowedProjects(string username)
    {
    } // GetAllowedProjects()
    
  2. Develop the GetAllowedProjects method by using impersonation against the Project PSI Web service. This method retrieves a list of projects that the current user may access and extracts the name and UID of each project into a ProjectInfo object. This object is then added to a class field named _allowedProjects.

    // PSI declarations.
    ProjectDerived _projectImpPsi = null;
    ProjectSvc.ProjectDataSet _projectData = null;
    
    try
    {
        // Initialize Project impersonation PSI ref.
        _projectImpPsi = new ProjectDerived
        {
            Url = _pwaImpersUrl + _projectPsiLocalPath,
            Credentials = CredentialCache.DefaultCredentials
        };
    
        // Retrieve caller's resource information from the PSI.
        ResourceInfo _callerResInfo = GetResourceInfo(username);
    
        // Test whether user is found.
        if (_callerResInfo == null)
            return;
    
        // Test whether user is a member of the PM or Admin groups.
        if (!IsProjectManagerOrAdmin(_callerResInfo))
            return;
    
        // Set the impersonation context string.
        ProjectDerived.SetImpersonationContext(
            _callerResInfo.IsWindowsUser,
            _callerResInfo.Account,
            _callerResInfo.Uid,
            Guid.NewGuid(),
            _pwaSiteUid,
            _lcid);
        #region Retrieve the list of allowed projects
        // Get regular projects.
        _projectData = _projectImpPsi.ReadProjectStatus(
            Guid.Empty,
            ProjectSvc.DataStoreEnum.PublishedStore,
            String.Empty,
            (int) PSLib.Project.ProjectType.Project);
    
        // Merge in master projects.
        _projectData.Merge(
            _projectImpPsi.ReadProjectStatus(
                Guid.Empty,
                ProjectSvc.DataStoreEnum.PublishedStore,
                String.Empty,
                (int) PSLib.Project.ProjectType.MasterProject));
    
        // Merge in subprojects.
        _projectData.Merge(
            _projectImpPsi.ReadProjectStatus(
                Guid.Empty,
                ProjectSvc.DataStoreEnum.PublishedStore,
                String.Empty,
                (int) PSLib.Project.ProjectType.InsertedProject));
        #endregion // Retrieve the list of allowed projects.
    
        #region Build the list of allowed projects
        // Extract project name and UID.
        if (_projectData.Project.Count > 0)
        {
            // Initialize the instance's project reference collection.
            _allowedProjects = new List<ProjectInfo>();
    
            for (int n = 0; n < _projectData.Project.Count; n++)
            {
                _allowedProjects.Add(new ProjectInfo
                {
                    ProjectName = _projectData.Project[n].PROJ_NAME,
                    ProjectUid = _projectData.Project[n].PROJ_UID
                });
            } // End for
    
            // Sort the list alphabetically.
            _allowedProjects.Sort((x, y) => 
                x.ProjectName.CompareTo(y.ProjectName));
        } // End if
        #endregion // Build the list of allowed projects.
    } // End try
    
    finally
    {
        // Dispose of PSI objects.
        if (_projectImpPsi != null) _projectImpPsi.Dispose();
        if (_projectData != null) _projectData.Dispose();
    } // End finally
    

    For more information about the methods used in this procedure, see Project Methods (WebSvcProject). For more information about the ProjectDataSet class, see ProjectDataSet Class (WebSvcProject).

With your internal code for the PsiProxies class library complete, you must now add public interfaces to the ProjectList class so that your SSRS report can interact with it.

Procedure 10. To create public interfaces for the ProjectList class

  1. In ProjectList.cs, create a method named GetAllowedProjectLabels.

    public string[] GetAllowedProjectLabels(string username)
    {
    } // GetAllowedProjectLabels()
    
  2. In ProjectList.cs, create a method named GetAllowedProjectValues.

    public string[] GetAllowedProjectValues(string username)
    {
    } // GetAllowedProjectValues()
    
  3. Develop the GetAllowedProjectLabels method. This method extracts the name of each project from the _allowedProjects class instance member and adds it to a string array for use by SSRS.

    // Has the list already been created?
    if (_allowedProjects == null)
        GetAllowedProjects(username);
    
    // Extract the project names from the list
    // for use as parameter labels.
    if (_allowedProjects != null)
    {
        string[] _projectLabels = new string[_allowedProjects.Count];
    
        for (int n = 0; n < _allowedProjects.Count; n++)
        {
            _projectLabels[n] = _allowedProjects[n].ProjectName;
        } // End for
    
        return _projectLabels;
    } // End if
    
    return new string[0];
    
  4. Develop the GetAllowedProjectValues method. This method extracts the name of each project from the allowedProjects class instance member and adds it to a string array for use by SSRS.

    // Has the list already been created?
    if (_allowedProjects == null)
        GetAllowedProjects(username);
    
    // Extract the project UIDs from the list
    // for use as parameter values.
    if (_allowedProjects != null)
    {
        string[] _projectvalues = new string[_allowedProjects.Count];
    
        for (int n = 0; n < _allowedProjects.Count; n++)
        {
            _projectvalues[n] = 
                _allowedProjects[n].ProjectUid.ToString();
        } // End for
    
        return _projectvalues;
    } // End if
    
    return new string[0];
    

After you add the public interfaces for the ProjectList class, the PsiProxies class library is complete.

Your next task is to develop a report that uses the PsiProxies class library to communicate with the PSI. You use the following procedures:

Create the report server project and Project Work Report.

Deploy the PsiProxies assembly for use in Report Designer.

Add the PsiProxies assembly to the Project Work Report.

Configure the @ProjectList report parameter.

Deploy the PsiProxies assembly to the report server.

Deploy the Project Work Report to the report server.

Procedure 11. To create the report server project and Project Work Report

  1. In Solution Explorer, right-click the ReportingPsiSecurity solution. Click Add, and then click New Project. Create a.NET Framework 3.5 Report Server project and name it ReportingPsiSecurity.

  2. In Solution Explorer, right-click the Shared Data Sources folder, and then click Add New Data Source.

  3. In the Shared Data Source Properties dialog box, name the data source instancenameRdb (for example, LitwareRdb). Set the Type to Microsoft SQL Server, and then enter a connection string for the Project Server instance Reporting database in the Connection string text box. For example, to connect to a database named Litware_ProjectServer_Reporting on a computer running SQL Server EPMDEMO, type Data Source=epmdemo;Initial Catalog=Litware_ProjectServer_Reporting

  4. In Solution Explorer, right-click the Reports folder, and then click Add New Report. On the Select the Data Source page of the Report Wizard dialog box, select the shared data source that you created in step 3.

  5. On the Design the Query page of the Report Wizard dialog box, enter the following query in the Query string text box, and then click Next.

    SELECT
    MEPUV.ProjectName,
    MERUV.ResourceName AS ProjectOwnerName,
    MEPUV.ProjectWork,
    MEPUV.ProjectActualWork
    FROM
    MSP_EpmProject_UserView MEPUV
    INNER JOIN
    MSP_EpmResource_UserView MERUV
    ON MEPUV.ProjectOwnerResourceUid = MERUV.ResourceUid
    WHERE
    MEPUV.ProjectUID IN (@ProjectList)
    ORDER BY
    MEPUV.ProjectName
    
  6. On the Select the Report Type page of the Report Wizard dialog box, select Tabular, and then click Next.

  7. On the Design the Table page of the Report Wizard dialog box, select ProjectName, ProjectOwnerName, ProjectWork, and ProjectActualWork from the Available fields list, click Details, and then click Finish.

  8. On the Completing the Wizard page of the Report Wizard dialog box, name the report Project Work Report, and then click Finish.

  9. Format the report as you want. At a minimum, I suggest that you set the Format property for the ProjectWork detail cell and the ProjectActualWork detail cell to n2.

With the base report complete, your next task is to deploy the PsiProxies assembly to the Report Designer folder so that you can test the PSI interface through Visual Studio 2008.

Procedure 12. To deploy the PsiProxies assembly for use in Report Designer

  1. Save all changes and close Visual Studio 2008.

  2. Copy PsiProxies.dll and Microsoft.Office.Project.Server.Library.dll from the build location of the PsiProxies project to the Report Designer folder. Following is the default location of this folder for Visual Studio 2008: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.

    For more information about how to deploy a custom assembly to SSRS, see Deploying a Custom Assembly.

  3. Edit the RSPreviewPolicy.config file. Following is the default location of this folder for Visual Studio 2008: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.

    For more information about this SSRS Security Policy file and SSRS Code Access Security, see Secure Development (Reporting Services).

  4. Add the following code directly below the last CodeGroup section in RSPreviewPolicy.config.

    <CodeGroup
            class="UnionCodeGroup"
            version="1"
            PermissionSetName="FullTrust"
            Name="PsiProxies"
            Description="PsiProxies">
        <IMembershipCondition
                class="UrlMembershipCondition"
                version="1"
                Url="Path\PsiProxies.dll"
        />
    </CodeGroup>
    <CodeGroup
            class="UnionCodeGroup"
            version="1"
            PermissionSetName="FullTrust"
            Name="PsLib"
            Description="PsLib">
        <IMembershipCondition
                class="UrlMembershipCondition"
                version="1"
                Url="Path\Microsoft.Office.Project.Server.Library.dll"
        />
    </CodeGroup>
    
    NoteNote
    You must change the Url attributes to match the correct path for your environment.
  5. Save and close RSPreviewPolicy.config.

Now that Visual Studio 2008 has access and permission to the PsiProxies assembly, you must add the appropriate references to the report.

Procedure 13. To add the PsiProxies assembly to the Project Work Report

  1. Open the ReportingPsiSecurity solution in Visual Studio 2008.

  2. In Solution Explorer, open Project Work Report.rdl.

  3. On the Report menu, click Report Properties.

  4. On the References tab of the Report Properties dialog box, under Add or remove assemblies, click Add . Click the ellipsis button next to the new row that appears.

  5. Browse to the Report Designer folder and select the PsiProxies.dll assembly. Following is the default location of this folder for Visual Studio 2008: %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies.

  6. Under Add or remove classes, click Add. Type PsiProxies.ProjectList as the Class Name and projList as the Instance Name, and then click OK.

    For more information about how to add assembly references to a report, see How to: Add an Assembly Reference to a Report (Reporting Services).

Now that the report has a reference to the PsiProxies assembly, you must add the necessary invocations of the ProjectList class to the appropriate report parameter.

Procedure 14. To configure the @ProjectList report parameter

  1. In Report Data, expand the Parameters folder, right-click the @ProjectList parameter, and then click Parameter Properties.

  2. On the General tab of the Report Parameter Properties dialog box, select the Allow multiple values check box, and type Projects: in the Prompt text box.

  3. On the Available Values tab of the Report Parameter Properties dialog box, click Specify values, and then click Add.

  4. Type =Code.projList.GetAllowedProjectLabels(User!UserID) as the Label, type =Code.projList.GetAllowedProjectValues(User!UserID) as the Value, and then click OK.

    For more information, see Accessing Custom Assemblies Through Expressions.

You should now have a fully functional report. Click the Preview tab to render the report.

NoteNote

To render the report, you must be running Visual Studio 2008 under an account that is a member of the Project Managers group or the Administrators group in Project Server and that has access to at least one project in Project Server. Otherwise, the Projects parameter is empty and the report does not render.

To deploy the ReportingPsiSecurity solution, you must first perform an XCopy deployment of the PsiProxies assembly to the report server.

Procedure 15. To deploy the PsiProxies assembly to the report server

  1. Copy PsiProxies.dll from the build location of the PsiProxies project to the report server bin folder. Following is the default location of the report server bin folder: %ProgramFiles%\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin.

    For more information about how to deploy a custom assembly to SSRS, see Deploying a Custom Assembly. For more information about the SSRS Security Policy file and SSRS Code Access Security, see Secure Development (Reporting Services).

  2. Edit the rssrvpolicy.config file. Add the following code directly below the existing <CodeGroup> section for the URL membership "$CodeGen$/*".

    <CodeGroup
            class="UnionCodeGroup"
            version="1"
            PermissionSetName="FullTrust"
            Name="PsiProxies"
            Description="PsiProxies">
        <IMembershipCondition
                class="StrongNameMembershipCondition"
                version="1"
                PublicKeyBlob="PublicKeyBlobGoesHere"
        />
    </CodeGroup>
    
    NoteNote
    You must change the PublicKeyBlob attribute in the previous XML to match the public key of your PsiProxies.dll assembly. Do not use the public key token. The entire public key is required. The PublicKeyBlob attribute must appear completely on a single line. You can extract the public key of an assembly by using the sn.exe -Tp command. For more information, see Strong Name Tool (Sn.exe).
  3. If you installed SSRS on a server where SharePoint or Project Server are already installed, the rssrvpolicy.config file should already contain a CodeGroup element named SharePoint_Server_Strong_Name. If not, or if the CodeGroup is missing, copy the Microsoft.Office.Project.Server.Library.dll assembly to the global assembly cache and add the following entry to rssrvpolicy.config directly above the CodeGroup that you added in step 3.

    <CodeGroup
            class="UnionCodeGroup"
            version="1"
            PermissionSetName="FullTrust"
            Name="SharePoint_Server_Strong_Name"
            Description="This code group grants SharePoint Server code     
                         full trust. ">
        <IMembershipCondition
                class="StrongNameMembershipCondition"
                version="1"
                PublicKeyBlob="00240000048000009400000006020000002400005
                               25341310004000001000100AFD4A0E7724151D5DD
                               52CB23A30DED7C0091CC01CFE94B2BCD85B3F4EEE
                               3C4D8F6417BFF763763A996D6B2DFC1E7C29BCFB8
                               299779DF8785CDE2C168CEEE480E570725F2468E7
                               82A9C2401302CF6DC17E119118ED2011937BAE969
                               8357AD21E8B6DFB40475D16E87EB03C744A5D3289
                               9A0DBC596A6B2CFA1E509BE5FBD09FACF"
        />
    </CodeGroup>
    
    NoteNote
    You must remove the carriage returns from the Description attribute and the PublicKeyBlob attribute. The Description and PublicKeyBlob attributes must appear completely on a single line.
  4. Save and close the rssrvpolicy.config file.

After deploying the PsiProxies.dll assembly, your next task is to deploy the Project Work Report by using Visual Studio 2008.

NoteNote

The report solution requires the identity account for the target instance of SQL Server Reporting Services to have administrator access to Project Server. You must also add the identity account to the Shared Services Provider (SSP) instance of Project Server as a process account.

Procedure 16. To deploy the Project Work Report to the report server

  1. In Solution Explorer, right-click the ReportingPsiSecurity project, and then click Properties.

  2. On the General tab of the ReportingPsiSecurity Property Pages dialog box, type the URL of the report server in the TargetServerUrl property field, and then click OK. For example, type http://epmdemo/reportserver.

  3. In Solution Explorer, right-click the ReportingPsiSecurity project, and then click Deploy.

    The report should now be available from your target report server.

Combining Project Server with SQL Server 2008 Reporting Services provides organizations of all sizes with powerful business intelligence capabilities. Although each product already offesrs an impressive array of programmability features, you can use the extensibility features of SQL Server 2008 Reporting Services to interface with the Project Server API and create compelling reporting solutions for your customers.

Show:
© 2014 Microsoft