Project Programmability

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.
 
 

  • Project Programmability and Business Intelligence

    EPM 2007 Test Data Population Tool

    • 2 Comments

    Christophe just passed along this information:

    The EPM 2007 Test Data Population Tool solution starter was published on CodePlex today: http://www.codeplex.com/EPMDatapop

    MWSnap048 2007-11-26, 19_26_50.jpg

    The EPM 2007 Test Data Population Tool enables you to load large amounts of EPM data: resources, projects, tasks, assignments into a Project Server 2007 database. You can then use this data to test loads and help your organization plan for your Project Server 2007 deployment. This tool is similar to the EPM 2003 Data Population utility but contains more features plus since the sources are provided you can customize it to fit your specific needs.  It was written by EPM World Wide Center of Excellence (WW COE) to generate data needed to perform performance and scalability labs for large and complex EPM deployments.

    The EPM 2007 Test Data Population Tool is a Win Form application that leverages the Project Server Interface (PSI) and/or WinProj (Project Professional).

    This solution starter includes a 15 pages document, a setup.exe to deploy it, as well as all source code.

    A special thank you to Mike Shughrue and Michael Jordan from the EPM WW COE for creating and testing the tool extensively during scalability labs, as well as Boris Scholl from the Product Marketing Group for reviewing the code and documentation. 


    About CodePlex

    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&A

    Q: Is the tool supported?

    A: There is no support in terms of CSS/PSS. We expect the support being a CodePlex community effort. Please note that the customization code uses standard supported web service calls available out of the box in EPM2007.

    Q: Is the tool free?

    A: Yes.

    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 CodePlex 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: Can a customer install the customization and use it?

    A: Yes the customer can, but he/she 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: epmssdev@microsoft.com

    Q: Will this tool be distributed in other ways (i.e. DVDs)?

    A: No.

    Q: What skills do I need to modify or change the tool?

    A: C#, Project Server Interface, and a good understanding of the EPM 2007 data schema.

    Q: I’m trying to modify the code and do have questions. Who do I ask?

    A: Go to the EPMDatapop discussion forum on CodePlex.

    Regards,

    Christophe Fiessinger

  • Project Programmability and Business Intelligence

    Tied Mode between Timesheet & My Tasks

    • 1 Comments

    We have published today an EPM 2007 solution starter that introduces Tied Mode between Timesheet & My Tasks on CodePlex: http://www.codeplex.com/EPMTSST

    MWSnap024 2007-11-15, 16_16_05.jpg

    Project Server 2007 provides for full separation between the project-focused My Tasks “Statusing” functionality and the time-period-focused Timesheet functionality, together with the ability to manually import data from one set of functionality to the other to keep them synchronized.

    This solution attempts to address this frustration by treating the timesheet system as the master system and attempting to synchronize the data into the My Tasks functionality (including task approvals) whenever a timesheet is saved (similar to Project Server 2003 integrated (“Tied”) timesheet and statusing functionality).

    This solution starter includes a 18 pages document as well as source code and a compiled DLL ready to deploy and test.

    A special thank you to Julie Ripoteau and Olivier Laymand from MCS France for testing the code extensively, as well as Patrick Conlan and Chris Boyd from the Product Group for reviewing the code and documentation. 

  • Project Programmability and Business Intelligence

    Local Custom Fields

    • 1 Comments

    Introduction

    This post contains interim documentation for tables in the Published database of a RTM-edition Project Server 2007 installation. Entities in this schema are subject to change without notice. This documentation is made available on a “best efforts” basis, Microsoft Product Support Services have not been trained in its usage and will not offer technical support for issues related to custom queries against this schema. This schema should not be updated by custom code, any updates to the data may break Project client cache code.

    This post contains two sections:

    - Schema fragments, showing the core tables required to traverse local custom field data

    - Sample queries, showing how to extract custom field and lookup table data for a specific project

    Many of the principles of local custom fields were initially developed in earlier Project Server versions and further questions may be answered by the server schema documentation supplied with those versions, for example:

    DOCS/PJDB.HTM // Primary source of custom field schema documentation

    DOCS/PJSVR.HTM // Additional enumerations

    Both of these files are supplied on the Project Server 2003 installation media.

    Joining Publishing and Reporting

    It is recommended that you implement views in the Reporting database that reference the four tables below as well as MSP_PROJECT_RESOURCES (as local resources are aggregated in the Reporting database)

    For example:

    CREATE VIEW dbo.MSP_PROJECT_RESOURCES AS
    SELECT * FROM [PUBLISHED_DB].dbo.MSP_PROJECT_RESOURCES
    

    Note that if the Reporting Database has been installed on a separate SQL Server instance that you will have to use a four part name in the views including the linked server name.

    Schema (Fragment)

    This section documents the relevant attributes and entities required to extract local custom field data for reporting purposes.

    This table holds the master list of all local custom fields in use, keyed on the Proj_UID. Obtain the MS_PROP_UID (which keys the custom field values in the next two tables) from the MD_PROP_NAME or by using the MSP_WEB_VIEW_FIELDS table (get the WFIELD_UID that corresponds to the WFIELD_NAME_OLEDB of the local custom field that you want) – we use “Magic GUIDs” to identify local custom fields (ie 000039b7-8bbe-4ceb-82c4-fa8c0b400033 is always TaskText1)

    This table contains the values (or a pointer to a lookup table value) for task local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry MD_LOOKUP_TABLE_UID in the previous table data is held in the appropriate *_VALUE column.

    This table contains the values (or a pointer to a lookup table value) for resource local custom fields. Obtain the MD_PROP_UID of the field and then query into this table to get the various values. Note that the rules for Duration Format have not changed since those documented in the Project Server 2003 documentation.

    Depending on the type of the field (FIELD_TYPE_ENUM taken with the presence of a lookup table entry, MD_LOOKUP_TABLE_UID in the Project Custom Fields table, data is held in the appropriate *_VALUE column.

    This table contains lookup table values pointed to by the previous two tables. Assuming either text or hierarchical selection the LT_VALUE_TEXT and LT_VALUE_FULL are most interesting. (_FULL contextualizes the selection and corresponds to the value stored in the enterprise equivalents in the Reporting database.

    Working With Project Task and Resource Custom Fields

    This section contains a series of queries to get you started. These all return tables that can be joined (use an outer join to ensure that data doesn’t get stripped) onto our Reporting USERVIEW views to obtain custom field data.

    Note 1: I’m assuming that the majority of data will be in text custom fields or outline codes, the *_custom_field_values tables contain other buckets for the other field types.

    Note 2: I haven’t qualified these query snippets with a PROJ_UID so they will return all data, you should remember to add this field for project-specific reports otherwise performance will be sub optimal.

    Note 3: I’ve packaged the resource outline code query in two ways, the second as a sample function to make usage a little bit simpler, as the function abstracts out all the joining to get the values.

    Join Table of Task_UID and a text Custom Field
    SELECT task.task_uid, tlcf.text_value 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'CustomFieldName'
    Join Table of Res_UID and a text Custom Field
    SELECT res.res_uid, rlcf.text_value 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceCustomFieldName'
    Join Table of Task_UID and an Outline Code
    SELECT task.task_uid, tloclt.lt_value_full, tloclt.lt_value_text 
    FROM dbo.msp_tasks AS task 
    INNER JOIN dbo.msp_task_custom_field_values AS tlcf 
    ON (task.task_uid = tlcf.task_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS tloclt 
    ON (tlcf.code_value = tloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (tlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'TaskOutlineCodeName'
    Join Table of Res_UID and an Outline Code
    SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = N'RescourceOutlineCodeName'
    Sample Function for Ease of Use
    CREATE FUNCTION dbo.Custom_ResOC (@OutlineCodename NVARCHAR(50)) 
    RETURNS TABLE AS 
    RETURN (SELECT res.res_uid, rloclt.lt_value_full, rloclt.lt_value_text 
    FROM dbo.msp_project_resources AS res 
    INNER JOIN dbo.msp_proj_res_custom_field_values AS rlcf 
    ON (res.res_uid = rlcf.res_uid) 
    INNER JOIN dbo.msp_project_lookup_table_structures AS rloclt 
    ON (rlcf.code_value = rloclt.lt_struct_uid) 
    INNER JOIN dbo.msp_project_custom_fields AS plcf 
    ON (rlcf.md_prop_uid = plcf.md_prop_uid) 
    WHERE plcf.md_prop_name = @OutLineCodename) 
    GO 
    -- Can be used in a join clause
    SELECT * FROM dbo.Custom_ResOC(N'ResourceOutlineCodeName') 
    GO
    

  • Project Programmability and Business Intelligence

    Check out the Post on Scalable Issues & Risks Report

    • 1 Comments

    It seems that somehow I messed up and the post "Scalable Issues & Risks Report" ended up showing up in the past. So in case you missed it:

    http://blogs.msdn.com/project_programmability/archive/2007/09/28/scalable-issues-risks-report.aspx

    Chris

  • Project Programmability and Business Intelligence

    Office Project Server 2007 Developer Training

    • 2 Comments

    Microsoft  Office Enterprise Project Management University (EPMU) is offering a range of new courses in Project Server and Project Portfolio Sever 2007 including Developer Training.

    Office Project Server 2007 Developer Training

    • Designed for Developers extending, developing and integrating Office Project Server 2007
    • 3 Day Course
    • USD$550

    For full details of all courses visit www.msepmu.com

  • Project Programmability and Business Intelligence

    Synchronizing Membership Provider for Project Workspaces

    • 2 Comments

    Back on May 17th I posted code that showed how to update the workspace data with the RDB:

    http://blogs.msdn.com/project_programmability/archive/2007/05/17/syncing-project-workspaces-with-the-rdb.aspx 

    Here is a similar program to synchronize project workspace membership. The interesting twist is that I show how to be kinder to the Project Queue by adding a few jobs at a time and waiting for them to complete.

    Here is the code: 

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net;
    using System.Data;
    using System.Web.Services.Protocols;
    using System.Diagnostics;
    using PSLibrary = Microsoft.Office.Project.Server.Library;

    namespace WorkspaceUpdate
    {
        class Program
        {
            static void Main(string[] args)
            {
                int count = 0;
                bool verbose = false;           // Verbose Output switch
                Guid job = Guid.Empty;          // The latest job submitted to the queue.
                int timeOut = 60;               // Default timeout before terminating the queue job

                string ls_projURL = "";
                const string PROJECT_SERVICE_PATH = "_vti_bin/psi/Project.asmx";
                const string WSSINTEROP_SERVICE_PATH = "_vti_bin/PSI/WSSInterop.asmx";
                const string WSQUEUESYSTEM_SERVICE_PATH = "_vti_bin/PSI/QueueSystem.asmx";

                if (args.Length == 0 || args.Length > 3)
                {
                    Message();
                }
                else if (args[0] == "/?")
                {
                    Message();
                }
                else
                {
                    ls_projURL = args[0];

                    if (args.Length > 2 && args[2].ToLower() == "verbose")
                    {
                        verbose = true;
                    }

                    try
                    {
                        timeOut = Convert.ToInt32(args[1]);
                    }
                    catch
                    {
                        Event("Warning: Invalid timeout, defaulting to 60 seconds.", EventLogEntryType.Warning, verbose);
                    }
                   
                    WSProject.Project ws_Project = new WSProject.Project();
                    WSSInterop.WssInterop ws_WssInterop = new WSSInterop.WssInterop();
                    WSQueueSystem.QueueSystem qsWS = new WSQueueSystem.QueueSystem();
                  
                    if (!ls_projURL.EndsWith("/"))
                    {
                        ls_projURL += "/";
                    }

                    try
                    {


                        ws_Project.Url = ls_projURL + PROJECT_SERVICE_PATH;
                        ws_Project.Credentials = CredentialCache.DefaultCredentials;

                        ws_WssInterop.Url = ls_projURL + WSSINTEROP_SERVICE_PATH;
                        ws_WssInterop.Credentials = CredentialCache.DefaultCredentials;

                        qsWS.Url = ls_projURL + WSQUEUESYSTEM_SERVICE_PATH;
                        qsWS.Credentials = CredentialCache.DefaultCredentials;
                       

                        Guid lo_projGUID;
                        string ls_projName;
                       
                        WSProject.ProjectDataSet lo_projs = null;
                        WSProject.ProjectDataSet lo_projDS;

                        try
                        {
                            lo_projs = ws_Project.ReadProjectList();

                            DataRowCollection lo_projects = lo_projs.Tables[lo_projs.Project.TableName].Rows;

                            for (int i = 0; i < lo_projects.Count; i++)
                            {
                                lo_projGUID = new Guid(lo_projects[i][0].ToString());
                                ls_projName = lo_projects[i][1].ToString();

                                try
                                {
                                    lo_projDS = ws_Project.ReadProjectEntities(lo_projGUID, 1, WSProject.DataStoreEnum.PublishedStore);

                                    // Check if the Project has a Workspace
                                    if (lo_projDS.Tables[lo_projDS.Project.TableName].Rows[0][lo_projDS.Project.WSTS_SERVER_UIDColumn.ColumnName] != null && lo_projDS.Tables[lo_projDS.Project.TableName].Rows[0][lo_projDS.Project.WSTS_SERVER_UIDColumn.ColumnName].ToString() != "")
                                    {
                                        Message("Synchronizing Workspace for Project" + ls_projName, verbose);
                                       
                                        //Wait to let the server process the work
                                        if (count % 4 == 3)
                                        {
                                            if (WaitForQueue(timeOut, job, qsWS) == false)
                                            {
                                                Event("Warning: Queue Job not Processed for Project:" + ls_projName + " Check Project Server Queue.", EventLogEntryType.Warning, verbose);
                                            }
                                        }

                                        job = Guid.NewGuid();

                                        ws_WssInterop.QueueSynchronizeMembershipForWssSite(lo_projGUID, job);


                                        count++;
                                    }
                                    else
                                    {
                                        Message("Notice: Project" + ls_projName + " does not have a workspace.", verbose);
                                    }
                                }
                                catch (SoapException lo_ex)
                                {
                                    PSLibrary.PSClientError psiError = new PSLibrary.PSClientError(lo_ex);
                                    PSLibrary.PSErrorInfo[] psiErrors = psiError.GetAllErrors();

                                    if (psiErrors.Length == 1)
                                    {
                                        if (psiErrors[0].ToString() == "ProjectNotFound")
                                        {
                                            Message("Notice: Project" + ls_projName + " is not published.", verbose);
                                        }
                                    }

                                }
                            }

                            Event("Successfully Synchronized Membership for Workspaces", EventLogEntryType.Information, verbose);
                        }

                        catch (WebException lo_ex)
                        {
                            Event("Error:" + lo_ex.Message, EventLogEntryType.Error, verbose);
                        }
                        catch (Exception lo_ex)
                        {
                            Event("Unknown Error:" + lo_ex.Message, EventLogEntryType.Error, verbose);
                        }
                    }
                    catch (UriFormatException lo_ex)
                    {
                        Event("Unknown Error:" + lo_ex.Message, EventLogEntryType.Error, verbose); ;
                    }
                }
            }

            private static bool WaitForQueue(int timeOut, Guid jobId, WSQueueSystem.QueueSystem qsWS)
            {
               

                int sleep = 3;
                int timeSlept = 0;        // Total time slept (seconds)
                bool jobSuccess = false;
                string xmlError;

                WSQueueSystem.JobState jobState; // Status of the queue job

                timeOut = timeOut * 1000;

                while (true)
                {
                    jobState = qsWS.GetJobCompletionState(jobId, out xmlError);

                    if (jobState == WSQueueSystem.JobState.Success)
                    {
                        jobSuccess = true;
                        break;
                    }
                    else if (jobState == WSQueueSystem.JobState.Unknown
                        || jobState == WSQueueSystem.JobState.Failed
                        || jobState == WSQueueSystem.JobState.FailedNotBlocking
                        || jobState == WSQueueSystem.JobState.CorrelationBlocked
                        || jobState == WSQueueSystem.JobState.Canceled)
                    {
                        jobSuccess = false;
                        break;
                    }
                    else if (timeSlept > timeOut)
                    {
                        jobSuccess = true;
                        //qsWS.CancelJobSimple(jobId);
                        break;
                    }

                    System.Threading.Thread.Sleep(sleep * 1000);

                    timeSlept = +sleep * 1000;
                }

                return jobSuccess;
            }

            static private void Message()
            {
                System.Console.WriteLine("");
                System.Console.WriteLine("WorkspaceUpdate url timeout [verbose]");
                System.Console.WriteLine("  url - The URL to the project server.");
                System.Console.WriteLine("  timeout - Seconds to wait for the queue job to process the User Sync");
                System.Console.WriteLine("  verbose - An optional parameter that outputs progress.");
            }

            static private string Message(string as_msg, bool verbose)
            {
                as_msg = DateTime.Now.ToString() + ":" + as_msg;

                if (verbose)
                    System.Console.WriteLine(as_msg);

                return as_msg;
            }


            static private void Event(string as_msg, EventLogEntryType eventType, bool verbose)
            {
                EventLog lo_eventLog = new EventLog();
                lo_eventLog.Source = "WorkspaceUpdate Sync Job";

                as_msg = Message(as_msg, verbose);
               
                lo_eventLog.WriteEntry(as_msg, eventType, 3652);

            }
        }
    }

  • Project Programmability and Business Intelligence

    Writing and Debugging Event Handlers for Project Server 2007

    • 1 Comments

    Another Visual How-to for Project development is published on MSDN: Writing and Debugging Event Handlers for Project Server 2007

     

    In less than 12 minutes, the video shows the development, testing, and debugging of a simple event handler for publishing projects. The OnPublishing event handler cancels publishing if the project name does not satisfy a specified condition, and then logs an application event on the server. The video shows the use of Visual Basic code; the related article includes both Visual Basic and C# code.

     

    The Visual How-to is based on the Project SDK article, How to: Write and Debug a Project Server Event Handler.

  • Project Programmability and Business Intelligence

    Scalable Issues & Risks Report

    • 2 Comments

    Introduction

    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.

    Architectural Notes

    This solution uses the flexibility of the Microsoft Office Project Server 2007 architecture to leverage the following technologies:

    - Project Server Reporting Database
    This 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 SP2
    The 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 Pages
    The 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.

    Security Notes

    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.

    Scalability and Performance Notes

    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:

    MSP_EpmResource:

    CREATE NONCLUSTERED INDEX [IX_FastAccountLookup] ON [dbo].[MSP_EpmResource] 
    ( 
    [ResourceName] ASC 
    ) 
    INCLUDE ( [ResourceNTAccount]) 
    Go
    

    Installation Notes

    Assumptions:

    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))

    Installation Steps:

    1. Customize and Deploy the Reports

    Load 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:
    Menu4.JPG
    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 menu

    In 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.

    Report Implementation Notes

    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.

    Patrick Conlan

  • Project Programmability and Business Intelligence

    Visual How To: Creating a Custom Web Parts for Project Server 2007

    • 2 Comments

    We’ve added a “Visual How Tos” section to the online SDK, with the first video:  Creating Custom Web Parts for Project Server 2007.

    Overview  

    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.

  • Project Programmability and Business Intelligence

    EPMsync Tool Released to CodePlex

    • 1 Comments

    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.

     

     

    About CodePlex

    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&A

     

    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: epmssdev@microsoft.com

     

    Q: Will this tool be distributed in other ways (i.e DVDs)

    A: Nope

     

    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.

     

     

  • Project Programmability and Business Intelligence

    Project Conference 2007 is Quickly Approaching

    • 0 Comments

    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,

    Chris Boyd

  • Project Programmability and Business Intelligence

    Improve the Performance of Building the Project Server Data Analysis Cube

    • 3 Comments

    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'<exact query>',

        N'SQL',

        NULL,

        NULL,

    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.

     

     

  • Project Programmability and Business Intelligence

    Adding a Web Reference Using Visual Studio Orcas Beta 2

    • 1 Comments

    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...

    image

    2. Click the Advanced Button:

    image

    3. Click the Add Web Reference Button:

    image

    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.

    Chris Boyd  

  • Project Programmability and Business Intelligence

    New Technical Article: "Importing Project 2007 Tasks from Excel Using a Managed Code Add-In"

    • 1 Comments

    Jim has just posted a new technical article. Here are some details he sent me: 

     

    The article "Importing Project 2007 Tasks from Excel Using a Managed Code Add-In" is now published in the Technical Articles section of the Project 2007 SDK.

     

    Article URL:

    http://msdn2.microsoft.com/en-us/library/bb738433.aspx 

     

    Associated download:

    http://www.microsoft.com/downloads/details.aspx?familyid=416b606a-88c8-496f-9ad3-9afa09780e12&displaylang=en 

     

    The article:

      -- Explains how to develop a Project add-in that integrates with Excel, using Visual Studio 2008

      -- Summarizes the differences between C# and VB development for add-ins

      -- Includes extensive C# and Visual Basic code examples

      -- Explains examples of routines that validate and convert subsets of Excel task data for import

      -- Handles multiple task predecessors

      -- Shows a way to handle task predecessors that would create COM exceptions
      -- Shows how to configure ClickOnce for deployment of the add-in

     

    The download includes the complete C# and Visual Basic.NET code. Visual Studio Tools for Office (3.0), in Visual Studio 2008 (Beta 2), now includes templates for Project 2003 and Project 2007 add-ins. The new templates and ClickOnce publishing make it *much* easier to develop, deploy, and update add-ins for all Office applications.

     

    You can download the Beta 2 release of Visual Studio 2008 from http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx.

  • Project Programmability and Business Intelligence

    Working with the Custom Fields Data Table

    • 1 Comments

    There have been a number of inquiries with regards to the custom field column definitions. So here they are:

    MD_AGGREGATION_TYPE_ENUM: Specifies the roll up type for a custom field.

    MD_ENT_TYPE_UID: Specifies the type of entity (project, task, or resource) for the custom field.

    MD_LOOKUP_TABLE_UID: Specifies the GUID of the lookup table to use for the custom field.

    MD_PROP_CHECKOUTBY: GUID of the user who has the custom field checked out for modification.

    MD_PROP_CHECKOUTDATE: Date the custom field was checked out for modification.

    MD_PROP_DEFAULT_VALUE: GUID of the default value in a lookup table for a custom field.

    MD_PROP_FORMULA: Formula that calculates the value of a custom field.

    MD_PROP_GRAPHICAL_INDICATOR: Definition of a graphical indicator for a custom field.

    MD_PROP_GRAPHICAL_INDICATOR_TOOLTIP: Specifies whether the graphical indicator has a tool tip.

    MD_PROP_ID: Integer ID of the enterprise custom field.

    MD_PROP_ID_SECONDARY: Assignment custom field ID for resource and task custom fields.

    MD_PROP_IS_LEAF_NODE_ONLY: Specifies whether the custom field value must be a leaf node in the lookup table.

    MD_PROP_IS_REQUIRED: Specifies whether the custom field is required when the entity (project, resource, or task) is created.

    MD_PROP_MAX_VALUES: Specifies whether the custom field has a single value or uses a multi-value lookup table.

    MD_PROP_NAME: Name of the custom field.

    MD_PROP_PROJ_SUMM_GRAPHICAL_INDICATOR: Definition of the project summary graphical indicator for the custom field.

    MD_PROP_ROLLDOWN_TO_ASSN: Specifies whether the resource or task custom field rolls down to assignments.

    MD_PROP_SUMM_GRAPHICAL_INDICATOR: Definition of the custom field graphical indicator for the summary task.

    MD_PROP_TYPE_ENUM: Type of the custom field.

    MD_PROP_UID: GUID of the custom field.

    MD_PROP_UID_SECONDARY: Assignment custom field GUID for resource and task custom fields.

    MD_PROP_USE_FOR_MATCHING: Specifies whether to use the resource custom field for matching generic resources.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Customizing E-Mail for Project Server Notifications

    • 5 Comments

     

    Jim Corbin has written a draft article on how to customize e-mail notifications from Project Server:  

     

    Here is a draft of an article and source code that will be published in the Project 2007 SDK update late this year. The draft article Customizing Notifications.doc is in the attached NotificationsEventHandler.zip.

     

    The article explains how to create an OnSending event handler for notifications, and shows the code for extracting relevant information from the notification XML data. If the event handler matches a notification type (specific alerts and reminders you want to customize), it transforms the XML data to a text or HTML e-mail using your custom XSLT file, sends the e-mail to the proper recipient, and then cancels the OnSending pre-event so that Project Server doesn’t send the default notification.

     

    The article includes a discussion of how to extract the default XSLT files for notification e-mails from the Published database. The download includes the English language XSLT files, but you need to extract them for other languages.  The article also includes a discussion of how to configure an SMTP virtual server for a test installation of Project Server.

  • Project Programmability and Business Intelligence

    VSTO Add-In Support for Project 2003 and Project 2007

    • 9 Comments

    I am very excited about the Visual Studio Beta 2 release, as it now has support for Project 2003 and Project 2007 add-ins! This makes coding and debugging managed code very easy in Project Client. It also makes it really simple to call into the PSI from Project Client to retrieve and update data on the server because you have the complete .Net framework at your disposal.

    You can download Visual Studio Beta 2 to try it out for yourself:

    http://msdn2.microsoft.com/en-us/vstudio/default.aspx

    I plan to do a few complex blog posts with regards to VSTO over the next couple of months, but to get started, here is a basic example:

    1. Download and install VS Beta 2 from: http://msdn2.microsoft.com/en-us/vstudio/default.aspx

    2. Open VS Beta 2 and Create a New Project selecting Project Add-in:

    image

    3. Give it a name and click OK

    4. Add a Windows Form called SimpleDemoForm to the project:

    image

    5. A label to the form called: lblProjectName

    6. In the ThisAddIn.cs class file add the following code in the startup method:

                SimpleDemoForm frmSimpleDemoForm = new SimpleDemoForm(Application);
                frmSimpleDemoForm.Show();

    This code will load and show the form we created in step 4. Note that we pass the Application into the form's constructor.

    7. View the code for the form and have it match the following:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using MSProject = Microsoft.Office.Interop.MSProject;
    
    namespace SimpleDemo
    {
        public partial class SimpleDemoForm : Form
        {
            private MSProject.Application Application;
    
            public SimpleDemoForm(MSProject.Application Application)
            {
                InitializeComponent();
                this.Application = Application;
            }
    
            private void SimpleDemoForm_Load(object sender, EventArgs e)
            {
                lblProjectName.Text = Application.ActiveProject.Name;
            }
        }
    }

    Couple of things to note with this code sample. We have referenced Microsoft.Office.Interop.MSProject and we have passed the Application variable to the form so that we can manipulate the project in the same way we can manipulate it in VBA. In the form load method, we read the project name and show it on the form.

    8. Build and run the application.

    This should launch Project automatically and load the add-in for you, with no configuration work involved. You can also add any debug breaks to troubleshoot your code.

    Here is what you should see when you build and run the add-in:

    image

    Chris Boyd

  • Project Programmability and Business Intelligence

    Checked Out Resources

    • 0 Comments

    Phil Smail has this great code snippet for providing better error handling with regards to checked out resources:

    While working with the PSI I wanted to check out a number of resources. Obviously the situation can arise where some of those resources are already checked out and I wanted to report that information to the client.

    Unfortunately the PSClientError object only gives you information that there were resources already checked out but not which ones were checked out. The data is contained with the XML for the Exception so I wrote a helper function to extract this information out

    The output from the code looks like the following:

    The code is as follows:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net;
    using System.Web.Services.Protocols;
    using System.Xml;
    
    namespace GetError
    {
        class Program
        {
            private const string RESOURCESWEBSERVICE = "/_vti_bin/PSI/Resource.asmx";
            static GetError.ResourceWsc.Resource Resource = new GetError.ResourceWsc.Resource();
    
    
            static void Main(string[] args)
            {
                String PWAURL = args[0];
                Resource.Url = PWAURL + RESOURCESWEBSERVICE;
                Resource.Credentials = CredentialCache.DefaultCredentials;
                Guid[] restypeGuids = new Guid[2];
                //Change these GUIDS to res Guids in your system
                restypeGuids[0] = new Guid("{f461c3f7-e0c9-414f-ae1a-41abe862f709}");
                restypeGuids[1] = new Guid("{4e5d7702-7748-4a18-bc5d-7beef7004188}");
    
                Console.WriteLine("Trying to check out resources");
    
                try
                {
                    Resource.CheckOutResources(restypeGuids);
                }
                catch (SoapException ex)
                {
                    String tempxml = ex.Detail.InnerXml;
                    ExtractError(tempxml);
                }
                Console.ReadLine();
            }
    
            static void ExtractError(String xmlstr)
            {
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(xmlstr);
                foreach (XmlNode node in doc.DocumentElement.FirstChild.ChildNodes)
                {
                    String ErrString = "";
                    if(node.Name.Equals("item"))
                    {
                        //We know its one error
                        XmlNode n = node.Attributes.GetNamedItem("value");
                        ErrString = "Error: Resource - " + n.Value;
                        XmlNode childn = node.FirstChild;
                        if (childn.Name.Equals("error"))
                        {
                            XmlNode no = childn.Attributes.GetNamedItem("name");
    
                            switch (no.Value)
                            {
                            case "CICOAlreadyCheckedOutToYou":
                                ErrString += " Resource already checked out to you";
                                break;
                            case "CICOCheckedOutInOtherSession":
                                ErrString += " Resource checked out in another session";
                                break;
                            case "CICOCheckedOutToOtherUser":
                                ErrString += " Resource already checked out to another user";
                                break;
                            }      
                        }
                    }
                    Console.WriteLine(ErrString);
                }
            }
        }
    }
    
    
  • Project Programmability and Business Intelligence

    SDK Update

    • 0 Comments

    There is an updated version of the Project SDK:

     

    Project 2007 SDK online 

    Project 2007 SDK download

     

    New and updated topics since last April's release include:

    ·         Task Start and Finish Dates

    ·         Extending the Project Workspace Template

    ·         SetAssignmentWorkData Schema Reference

    ·         Changes for Custom Project Guides (additional information for deployment)

    ·         Using the Project.Open Event

     

    In addition, there is more information and examples for the Queue and the event receiver methods, along with several documentation bug fixes in other PSI reference topics.

     

    The download also includes an updated XML schema reference for Project Standard and Project Professional (mspdi_pj12.xsd), and a redistribution license for the Microsoft.Office.Project.Server.Library.dll assembly.

  • Project Programmability and Business Intelligence

    Using ReadCustomFieldsByEntity(...)

    • 0 Comments

    Phil Smail, a fellow PM on the Project Team, just passed along a helpful hint: 

     

    I am writing a cool little app and I wanted to pull back all the Resource Custom fields. Thankfully there’s a PSI to do this call ReadCustomFieldsByEntity, where the entity can be Project, Resource or Task. This is pretty neat except that the SDK docs don’t mention what the potential GUIDs options can be. After a bit of digging around I found out what the correct item was within the Project.Server.Library assembly.

     

    using PSLibrary = Microsoft.Office.Project.Server.Library;

    ...

    CustomFieldsDataSet ds = CustomFields.ReadCustomFieldsByEntity(new Guid(PSLibrary.EntityCollection.Entities.ResourceEntity.UniqueId));

    ...

  • Project Programmability and Business Intelligence

    Project 2007 Conference Sessions are now available!

    • 0 Comments
    The session list for the Project 2007 Conference has been posted to: http://www.msprojectconference.com Go to http://www.msprojectconference.com/tracks.htm to see all the sessions.
  • Project Programmability and Business Intelligence

    Getting at the Task Time Phased Data

    • 3 Comments

    I have been asked many times how to get the task time phased data from the PSI. Unfortunately there is no way to get this through the PSI. I suggest that you either go to the RDB for this information or the cubes. Here is a quick example.

    I created a project with one task and broke the work down over a week:

    clip_image002

    I saved and published the project so that it would make its way into the reporting database. Here is the query I wrote to retrieve this data:

    SELECT     
      MSP_EpmTask_UserView.TaskName,
      MSP_EpmAssignmentByDay_UserView.TimeByDay,
      MSP_EpmAssignmentByDay_UserView.AssignmentWork
    FROM         
      MSP_EpmAssignmentByDay_UserView 
        INNER JOIN MSP_EpmTask_UserView 
    ON MSP_EpmAssignmentByDay_UserView.TaskUID = MSP_EpmTask_UserView.TaskUID WHERE (MSP_EpmTask_UserView.TaskName = 'Task 1')

    Here is the result of the query:
    image

    Chris Boyd

  • Project Programmability and Business Intelligence

    Syncing Project Workspaces with the RDB

    • 2 Comments

    It has been a while since I have done a blog post, so I figured I would share with you something that I am currently working on. We have run into the scenario where users are updating information on Project Workspaces and Project Manager wants to report on the data that the users are entering in the Workspaces. That information does not make it into the RDB until after a project publishes. To keep the RDB in sync with the Project Workspace, I wrote the following app that we run as a scheduled job:

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Net;
    using System.Data;
    using System.Web.Services.Protocols;
    using System.Diagnostics;
    using PSLibrary = Microsoft.Office.Project.Server.Library;

    namespace WorkspaceRDBUpdate
    {
      class Program
     
    {
        static void Main(string[] args)
        {
          int count = 0;
         
    bool verbose = false;
         
    string ls_projURL = "";
         
    const string PROJECT_SERVICE_PATH = "_vti_bin/psi/Project.asmx";

          if (args.Length == 0 || args.Length > 2)
          {
            System.
    Console.WriteLine("WorkspaceRDBUpdate url [verbose]");
            System.
    Console.WriteLine(" url – The URL to the project server.");
            System.
    Console.WriteLine(" verbose – An optional parameter that outputs progress.");
          }
         
    else
         
    {
            ls_projURL = args[0];
            
           
    if (args.Length > 1 && args[1].ToLower() == "verbose")
            {
              verbose =
    true;
            }

            WSProject.Project ws_Project = new WSProject.Project();

            if (!ls_projURL.EndsWith("/"))
            {
              ls_projURL +=
    "/";
            }

            ws_Project.Url = ls_projURL + PROJECT_SERVICE_PATH;
            ws_Project.Credentials =
    CredentialCache.DefaultCredentials;

            Guid lo_projGUID;
           
    string ls_projName;

     

            WSProject.ProjectDataSet lo_projs = null;

            WSProject.ProjectDataSet lo_projDS;

            try
           
    {
              lo_projs = ws_Project.ReadProjectList();
             
    DataRowCollection lo_projects = lo_projs.Tables[lo_projs.Project.TableName].Rows;

              for (int i = 0; i < lo_projects.Count; i++)
              {
                lo_projGUID =
    new Guid(lo_projects[i][0].ToString());          
               
    ls_projName = lo_projects[i][1].ToString();

                try
               
    {
                  lo_projDS = ws_Project.ReadProjectEntities(lo_projGUID, 1, WorkspaceRDBUpdate.WSProject.
    DataStoreEnum.PublishedStore);

                  // Check if the Project has a Workspace

                  if (lo_projDS.Tables[lo_projDS.Project.TableName].Rows[0][lo_projDS.Project.WSTS_SERVER_UIDColumn.ColumnName] != null && lo_projDS.Tables[lo_projDS.Project.TableName].Rows[0][lo_projDS.Project.WSTS_SERVER_UIDColumn.ColumnName].ToString() != "")
                  {
                   
    if (verbose)
                      System.
    Console.WriteLine("Synchronizing Workspace for Project " + ls_projName);

                    ws_Project.QueueSynchronizeProjectWorkspace(
    Guid.NewGuid(), lo_projGUID, false);
                    count++;
                  }
                 
    else
                 
    {
                    if (verbose)
                      System.
    Console.WriteLine("Notice: Project " + ls_projName + " does not have a workspace.");
                  }
                }
               
    catch (SoapException lo_ex)
                {
                  PSLibrary.
    PSClientError psiError = new PSLibrary.PSClientError(lo_ex);
                  PSLibrary.
    PSErrorInfo[] psiErrors = psiError.GetAllErrors();

                  if (psiErrors.Length == 1)
                  {
                   
    if (psiErrors[0].ToString() == "ProjectNotFound")
                    {
                     
    if (verbose)
                        System.
    Console.WriteLine("Notice: Project " + ls_projName + " is not published.");
                    }
                  }
                }
              }

              Event("Successfully Synchronized " + count + " Projects with the RDB", EventLogEntryType.Information);

            }
            
    catch (WebException lo_ex)
            {
             
    if (verbose)
                System.
    Console.WriteLine("Error: " + lo_ex.Message);

              Event("Error: " + lo_ex.Message, EventLogEntryType.Error);
            }
            
    catch (Exception lo_ex)
            {
              
    if (verbose)
                System.
    Console.WriteLine("Unknown Error: " + lo_ex.Message);

              Event("Unknown Error: " + lo_ex.Message, EventLogEntryType.Error);
            }
          }
        }

        static private void Event(string as_msg, EventLogEntryType eventType)
        {
         
    EventLog lo_eventLog = new EventLog();    
         
    lo_eventLog.Source =
    "Workspace RDB Sync Job";
          lo_eventLog.WriteEntry(as_msg, eventType, 3652);
        }
      }
    }

    This application is fairly straightforward. It reads all the projects and checks if a workspace exists. If one exists, it forces the RDB to be updated by calling QueueSynchronizeProjectWorkspace(…). It logges events to the event log, which makes troubleshooting easy if administrator decide to run it as a scheduled job.

    Chris Boyd

  • Project Programmability and Business Intelligence

    Checking Errors

    • 3 Comments
    Here is a great recommendation from Jim Corbin:

    There are two main places to check for configuration and runtime errors when you are developing solutions for Project Server. The Unified Logging Service (ULS) trace logs can be more detailed than the application event log.

    · Application events   In the Start menu on the Project Server computer, click Run, and then type eventvwr. In the left pane of the Event Viewer window, click Application to see the events logged by Project Server, Windows SharePoint Services, ASP.NET, SQL Server, custom event handlers, and other applications. The Project Server event sources include ProjectQueueService and pjevtsvc.

    · ULS You can configure the ULS trace log to record specific or all categories and levels of activities in Project Server and SharePoint. To view the trace logs, you can use Windows Notepad, Microsoft Excel, or the Log Viewer add-in feature for SharePoint. Log Viewer is a useful download that is available from CodePlex.

    To configure the ULS trace log for a specific Project category:

    1. Open the SharePoint 3.0 Central Administration application, click Operations, and then click Diagnostic logging in the Logging and Reporting section.

    2. In the Event Throttling section, select a specific category such as Project Server - Server-Side Events. If you select Project Server - General, all Project categories will be logged.

    3. Select the Verbose level for the least critical event to report in the trace log.

    Caution   Run verbose logging only when you need it. Especially on a production server, select only a specific category. The size of logs can grow to be large. To turn off all logging, select the empty category and None for the trace log least critical event. To record relatively few events, select High or Monitorable.

    4. Use the default path for the trace logs, for example, C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\LOGS\. If you use the Log Viewer add-in for SharePoint, it looks for trace logs in the default path.

    5. The default maximum number of log files to maintain is 96.

    6. The default number of minutes to use a log file is 30. That is, ULS tracing creates a new log file after 30 minutes. Log files include the date and time in the filename, for example, SERVERNAME -20070424-1612.log.

    To use the Log Viewer add-in for SharePoint:

    1. Download and install the Log Viewer feature from CodePlex SharePoint 2007 Features. See the Releases tab for the list of downloads. The release notes include installation instructions.

    2. The Log Viewer is a global feature. After it is installed, click Operations on the Central Administration page, and then click View Unified Logging Service in the Utilities section.

    3. On the Unified Logging Service (ULS) Logs page, select a file to view, and then select the Project Server category. That shows all of the specific categories such as Project Server Queue, Project Server Server-Side Events, Project Server Reporting, and so forth.

    4. To see all events in the ULS log, leave the trace severity drop-down list blank. If you select Verbose, that shows only the verbose level events.

    5. Click Go.

    Log Viewer add-in for SharePoint showing a ULS log for Project

    68-4526-aeaa-db69b79e60da.gif

  • Project Programmability and Business Intelligence

    April update of the Project 2007 SDK is now available on MSDN

    • 0 Comments

    The April update of the SDK is now available on MSDN. It includes many final versions of articles that have been published as posts on this blog.  

    ·         SDK Download: http://www.microsoft.com/downloads/details.aspx?FamilyId=2672F6F9-7028-4B30-99A2-18CB1EED1ABE&displaylang=en .
    The pj12ProjectSDK.exe download includes (new and updated items in blue bold):

    o   pjSDK2007.chm (conceptual topics and managed code reference – the same topics as on the MSDN online library)

    o   pj12ReportingDB.chm (Reporting database schema reference and Visio diagrams)

      • OLAPCubeSchemas.xls (dimensions, measures, and properties of the Project Server OLAP cubes)
      • XML data interchange schema for Project 2007 (mspdi_pj12.xsd plus supplemental schemas for enterprise custom fields)
      • Statusing schemas (ChangeList.xsd and SetAssignmentWorkData.xsd)
      • Project Portfolio Server 2007 Database Reference (AccountData and AccountIndex)
      • Redistribution license for the Microsoft.Office.Project.Server.Library.dll
      • SDK code samples:
        1. ChangeXML Utility: generate, validate, and test ChangeXML parameters for the UpdateStatus method
        2. Custom Web Parts: two samples; one uses the Microsoft.Office.Project.Server.PWA.dll assembly, and one does not
        3. Impersonation: two samples; a simple console application and the Impersonation Web Application
        4. Login Demo
        5. OLP Example
        6. Project Guide: default Project Guide files, and the sample Critical Path custom Project Guide updated for Project 2007
        7. Project Workspace
        8. ProjTool: a sample application to help generate data and test many of the PSI methods
        9. PSI Extensions: develop and test a custom PSI Web service
        10. Report Pack: seven sample reports using SQL Server Reporting Services 2005 (SP1)
        11. Test Event Handler
    • SDK Online: Project 2007 SDK in MSDN online library: http://msdn2.microsoft.com/en-us/library/ms512767.aspx
      • Object Model Maps: The Project 2007 VBA Reference was also recently posted on MSDN, along with four pages of the new Project object model maps. For example, click any object in Application and Projects Object Map to go to the topic for that object. You can also see the object model maps in VBA Help when you use Project Standard or Project Professional. In the Project Help window, click the Connection Status menu at the lower right, and then select Show content from Office Online.
    • Portal:  Project Developer Portal in the Office Developer Center on MSDN includes links to the Project 2007 SDK,  Project 2007 blogs, as well as the Project 2003 SDK.

     

    What’s new in pjSDK2007.chm and the online SDK:

     

    In addition to new code samples and schemas in the download, the April update of the SDK includes 97 conceptual topics (28 new since the RTM release) and many updates in the managed code reference topics. Major new and updated conceptual topics include the following:

    ·         XML Schema References for Project (15 topics; includes the ChangeList Schema Reference)

    ·         Changes for Custom Project Guides 

    ·         Custom Fields in OLAP Cubes (in the Local and Enterprise Custom Fields topic)

    ·         How to: Generate ChangeXML for Statusing Updates

    ·         Project Server Report Pack (9 topics)

    ·         Walkthrough: Creating a PSI Extension 

    ·         Walkthrough: Develop an Impersonation Web Application

                                                                                                                                                       

    Other updates include enumeration descriptions, PSI method permissions, TimesheetDataSet properties, and a VBA sample for Project Deliverables.

     

    For related content, see the Office Developer Centers,  SharePoint Developer Center, and the SharePoint Server 2007 Developer Portal.

Page 9 of 11 (255 items) «7891011