This blog focuses on customizations and programming for Project Web App, Project Server, Project Professional and Project Standard. Includes User Interface (UI) customizations, Project Server Interface (PSI) and Visual Basic for Applications (VBA) Programming. It also covers Business Intelligence.
Out of the box the Issues & Risks components of Project Server 2007’s collaboration functionality have an architectural problem which surfaces when 100’s of Project Server Workspace sites are created. This problem is forced by the need to allow flexibility in PWS placement within the farm and the ability to assign issues and risks to any user within the farm, not just specific project team members.
This results in a non-scalable multi-site query whenever a team member hits the home page of the Issues and Risks page in Project Web Access. The home page issue is mitigated by removal of the functionality; however the team member still needs a place to see all issues and risks across the Project Server site. These instructions detail how to implement such a place in a scalable manner using the data held in the Project Server Reporting Database in combination with SQL Server Reporting Services.
Caveat: this solution is provided as a workaround; it is not intended to become part of the Project Server 2007 solution and as such will not be localized or migrated/upgraded in future releases. The source of this solution is provided to the customer as-is and should be tested within the customer’s pre-production environment prior to production deployment. Microsoft Product Support Services will not be able to offer full support for this software.
This solution uses the flexibility of the Microsoft Office Project Server 2007 architecture to leverage the following technologies:
- Project Server Reporting DatabaseThis database contains Issue, Risk, Project & Resource data used by this solution. Note that this data is updated on project publish (or explicit WSS site synchronization) so after new Issues & Risks are created the project should be published or the site synchronized in order to make this data available in the reporting database.Team members should subscribe to the Issues & Risks lists for their projects in order to get earlier notification of new data.
- SQL Server Reporting Services 2005 SP2The reports used to provide this solution were built using the Report Designer (available in the “Business Intelligence Workbench”) that ships with SQL Server 2005. The SP2 version of the software was used. Note that the installation section refers to the non-integrated Report Viewer web parts, used to demonstrate the integration. It is recommended that the integrated mode be used for a cleaner interface.The solution uses the Report Viewer web part that must be installed into the farm as described in the installation section.The solution is built as a master report that hosts two sub-reports, one for issues and one for risks – this makes individual reformatting and editing a little bit easier.
- SharePoint Web Part PagesThe report developed to display the list of issues & risks is hosted within the Reporting Services report viewer web part within a web part page that is added to the Project Web Access site. The report assumes that flat lists are being used for Issues and Risks, if folders within the list are to be used then further testing is recommended.
The report should be run using a data connection with credentials that have read-only access to the Reporting database, specifically the following tables:
- MSP_EpmProject: Used to obtain project name and URL details
- MSP_EpmResource: Used to filter data based on the caller (see more below)
- MSP_WssRisk: Data on Risks, updated when the project is published or the site is explicitly refreshed, not when an issue or risk is added to the list.
- MSP_WssIssue: Data on Issues, updated when the project is published or the site is explicitly refreshed, not when an issue or risk is added to the list.
A design aim for the report is to avoid having to register every team member in the reporting database. This is met via SQL Server Reporting Services parameterized filtering on the caller’s userid (this is passed into the query so that only data directly assigned to the caller is returned) – This report was not tested in a farm configuration, it is recommended that it be fully tested in a multi-server configuration before live deployment to ensure that data is filtered.
This report does not respect SharePoint list-item security (which is not enabled by default) – if an issue or risk is assigned to a team member and then secured individually in a manner that denies the team member access they will continue to see the data pertaining to the item in this report, however the link will not display the item form.
This solution will be considerably more performant than the cross-site query that it replaces. However it is recommended that an additional index be created in the reporting database to facilitate the filtering, specifically:
CREATE NONCLUSTERED INDEX [IX_FastAccountLookup] ON [dbo].[MSP_EpmResource]
INCLUDE ( [ResourceNTAccount])
SQL Server 2005 Reporting Services 2005 is already installed in the farm, in non-integrated mode as specific website (in the example below: http://pconlan08:81/ReportServer) – it is recommended that integrated mode be used.
The SQL Server 2005 Reporting Services web parts are installed on every SharePoint server in the farm, installation instructions:
http://msdn2.microsoft.com/en-au/library/ms159772.aspx (note that the CAB file is located in a different location on an X64 server, search for RSWebParts.cab on the Reporting Services server(s))
1. Customize and Deploy the ReportsLoad the attached Reporting Services solution into Business Intelligence Workbench. Edit the Shared Data Source to point to the correct SQL Server and Reporting Database; then set the appropriate credentials (Reporting Services implements a secure credential store)Review the form design and make any customer-requested changes (see Report Configuration Notes below)In Business Intelligence Workbench edit the Project Properties to point to the correct Reporting Services server and Deploy the solution. Use the http://xxxxx:nn/Reports url to validate that the report works as intended before proceeding to the next step.Once tested, use the SQL Server Management Studio to connect to the Report Server and grant appropriate access to the reports to all team members. Test this with a subset of team members to ensure they can access the report.
2. Create a web part page to host the web part for the report- Navigate to the PWA site as a SharePoint administrator, and use the Site Options drop down to select Site Settings/Modify All Site Settings which will bring up the Site Settings page. - Select Site Libraries and Lists which will bring up the Site Libraries and Lists page.- Select Create New Content which will bring up the Create page.- From the menu select: Web Part Page- Fill in the dialog as below:
Note that you may want to place this page in a new document library rather than the Documents library as depicted as you will be granting read access to team members to this library. Note that once created the page can be “hidden” in the library to prevent accidental erasure (to do this edit its properties in the library view).This creates the page, click the “Add a Web Part” orange bar to add the web part. Use the “Advanced Web Part gallery and options” link, select the “Server Gallery” and then the “Report Viewer” web part. This will add the web part to the page. Now configure it as below:Note that the Toolbar and chrome are turned off and a fixed height of 12” is used. No width need be set. The Report Path can contain spaces (no need to %20 escape the string)Save the changes and the web page, navigate to the URL to ensure that the report web part displays as expected and that it contains the report as expected. (Note that if you edit and redeploy the report you will need to restart your IE session to avoid caching the report format)
3. Connect the page to the Project Web Access menuIn PWA Server Settings choose Quick Launch from the Look and Feel set of options.Add a new Entry to the Work Item Group: “Issues and Risks Report”, connect it to the page you created above, as depicted below:The final step in this task is to hide the un-scalable Issues & Risk option. In the Quick Launch dialog click the Issues & Risks link and set its hidden property.The final result should look like:
You have now deployed the report, and added it to the PWA menu for team members to use.
Several SQL Server 2005 Reporting Services features have been used to deliver the reports, these are briefly discussed below.
Team members will click on the new Issues and Risks Report link, this will take them to the page below:
1. Issues and risks are grouped by status (to put Active at the top of each) – a filter could be added to remove items that have been postponed and/or closed if required, this is a trivial change to the reports. Note that if the types of status have been customized this may look slightly different.
2. Within the grouping data is sorted by Due Date and Project Name. If Due data is not set then “Now” is assumed (ie the current date will be used)
3. The Title is set up to be clickable to take the team member to the detailed item form, where the item can then be viewed and/or edited.
4. Dates have had their time element stripped to save space on each row.
5. The report displayed above is actually three reports – the master (that contains the Microsoft Office logo and sub-report objects) and two sub reports.
6. Data is filtered on items assigned to the caller using Reporting Services filtering capabilities – the report has a hidden parameter (a standard Reporting Services feature) that is populated with the callers Windows account, care should be taken to test this in your environment.
7. Further customization could add graphs/additional data to the view.
Jack Li has passed along a great tip on how to improve the performance of building the Project Server Data Analysis Cube:
Recently we have worked with a big enterprise customer using Project Server. They ran into an issue where Project Server reporting component which uses Analysis Server ran very slow during cube population. Eventually, the issue was tracked down to a single query. The query would take 17 hours to finish for 1 year of data. nHowever, if one applied force order to the query, it would finish within minutes.
Customer had tight deadline and need a solution fast. It would be easy if one could just modify the query to add force order option. Unfortunately, the query was generated by Analysis Service based on the cube definition. In other words, it couldn’t be changed.
Enter into plan guide for the rescue. SQL Server 2005 gives you various means to influence the query plan for a query without adding hints to the query itself directly. You don’t need to change the query which may not be possible without changing the application.
The simplest one is the use use sp_create_plan_guide stored procedure to add a query hint like force order etc.
Here is exactly what we did for our customer:
1) We used profiler trace to trace the exact query
2) We copied the exact query text and use sp_create_plan_guide to create the plan guide:
EXEC sp_create_plan_guide N'guide_forceorder',
N'OPTION (force order)'
Steps are fairly easy yet it is tricky to implement. If you are not doing it correctly, SQL Server may not use the plan guide you just created. Here are a few things you need to watch for:
1) ensure you have the exact text. Even if you miss a space character, the plan won’t match. To ensure you get exact text, you should launch profiler trace while running the application to get the query. See http://msdn2.microsoft.com/en-us/library/ms188255.aspx for more details.
2) There are two ways you can verify if SQL actually uses your plan guide. The first one obviously is that your query finishes faster. Another way is that you will see PlanGuideDB word in the xml plan. So if you do set showplan_xml on and then run the query, you will get xml showplan. If you search the text, you will find PlanguideDB.
There are more advanced ways to use plan guide including parameterization or use plan. Please refer SQL Server 2005 books online for these advanced topics.
We’ve added a “Visual How Tos” section to the online SDK, with the first video: Creating Custom Web Parts for Project Server 2007.
A Web Part is a modular unit of information that has a single purpose and is a basic building block of a Web Part Page. Project Web Access uses many Microsoft Office Project Server 2007 Web Parts and can be easily extended with custom Web Parts.
Web Parts in Windows SharePoint Services 3.0 improve upon earlier versions of Web Part technologies. You can use Windows SharePoint Services 2.0 Web Parts and ASP.NET 2.0 Web Parts. You can also use Web Parts in shared Web Part Page documents in a project workspace or team site. The shared documents are stored and managed on a computer running Windows SharePoint Services that is provisioned by Project Server. This Office Visual How To article shows the creation of a custom Web Part for Project Server 2007 that you can use to display the upcoming tasks for a specified project. The code presented in this article is based on the "No PWA Reference" Web Part sample that is included in the Microsoft Office Project 2007 SDK download.
Lately I have been playing around with VS Orcas Beta 2; primarily to play around with VSTO support for Project Client. When trying to add a web reference to our web services (PSIs), at first I could not find the option to do it in the VS IDE. After digging around, I figured it out. Here are the steps to adding a web reference to one of the PSI web services in Visual Studio Orcas Beta 2:
1. Right Click on Service Reference and Select Add Service Reference...
2. Click the Advanced Button:
3. Click the Add Web Reference Button:
This will bring up the familiar web reference UI from Visual Studio 2005 and you can add the web reference just like you did in the past.
I just wanted to give a quick update about the Project Conference that is coming up at the end of October in Seattle. This is going to be our biggest conference ever! With sessions covering everything from how to get started with Project to managing a large EPM solution.
There is entire track of sessions that is focused on the developer. There is going to be a lot of great content presented for both the new and advanced Project developer. In these sessions you also learn lots of tips and tricks for developing against Project 2007. Here is a list of all the developer sessions, including their abstracts:
I will also be doing a Programmability Futures session at the conference. In this session, I will be giving the first sneak peak at what we are planning for the developer in the next release of Project.
Hope to see you at the Conference,
Boris Scholl has just passed along this great news:
After months of development we have published the EPM sync tool on CodePlex for other customers and Partners to try out for themselvesGiven the complexities of the integration, we expect partners to develop solution offerings based on this tool.
Microsoft Office Project Server 2007 (Project Server) and Microsoft Office Project Portfolio Server 2007 (Portfolio Server) integrate via the Portfolio Server gateway. Data is exchanged by running either an import or an export from Portfolio Server. But this exchange is done in bulk and typically batched. There is a desire to have a more granular experience in near real-time. The EPMSync Solution Accelerator is a proposed solution to this problem. The EPMSync Solution Accelerator synchronizes Portfolio Server attributes with Project Server custom fields (and a set of project level built-in fields). The solution is made up of a Windows Service and a number of supporting applications.
CodePlex is Microsoft's open source project hosting web site. You can use CodePlex to create new projects to share with the world, join others who have already started their own projects, or use the applications on this site and provide feedback. A word about Microsoft’s role: Microsoft does not control, review, revise, endorse or distribute the third party projects on this site. Microsoft is hosting the CodePlex site solely as a web storage site as a service to the developer community.
Q: Is the tool supported?
A: There is no support in terms of CSS/PSS. We expect the support being a CodePlex community effort.
Q: Is the tool for free?
A: Yes it is.
Q: Can I distribute the tool and the source code to customers and partners.
A: Customers and Partners can use both. Please point them to the website as they have to agree on the license terms
Q: Can a Partner distribute the tool and code as is?
A: No, but he can point his customer to the website to download it, so he makes sure that the customer agrees with the license terms.
Q: Technically can a customer just install and use it?
A: Yes the customer can, but the customer is responsible for testing it and running it.
Q: Can I suggest changes to it?
A: Yes, join the CodePlex community or send us an email: email@example.com
Q: Will this tool be distributed in other ways (i.e DVDs)
Q: What skills do I need to modify or change the tool?
A: .NET 2.0, C#, T-SQL, PSI
Q: I’m trying to modify the code and do have questions. Who do I ask?
A: Go to the EPMsync discussion forum on CodePlex.