Export (0) Print
Expand All

How to: Use a Filter Parameter with PSI Methods

Office 2007

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

The Project Server Interface (PSI) in Microsoft Office Project Server 2007 includes several Web methods that use a string parameter named xmlFilter or filter. You can use the filter parameter with the PSI methods to read Project Server data and return a DataSet that contains specific entities such as projects, resources, custom fields, resource plans, or lookup tables. The filter parameter is an XML string that limits the amount of data that a PSI method returns, reduces the amount of processing necessary in the application, and saves network bandwidth. (Sample code in this article was adapted from code contributed by Brian Smith, Microsoft Corporation.)

The following PSI methods use an XML string filter object in an xmlFilter or filter parameter.

If the xmlFilter or filter parameter is an empty string, except for ReadResourceAssignments, the PSI method returns all of the data available to that method. There can be many thousands of assignments among all of the projects in Project Server, so ReadResourceAssignments requires a filter. If a PSI method returns too much data, it can affect system performance or exceed the limits of a SOAP reply.

Other PSI methods have different types of filter parameters such as a list of GUIDs or a range of dates. This article shows how to create and use an XML filter object. The sample code creates a filter object that returns project, resource, or task custom fields using the ReadCustomFields method. You can create filter objects for other methods and kinds of data using similar techniques.

NoteNote

For the PSI read methods which use a filter, the xmlFilter or filter parameter works with the Criteria operators to filter rows only in the primary DataTable (see Table 1). For example, you can use xmlFilter with the ReadResources method to filter rows in ResourceDataSet.ResourcesDataTable, but not in ResourceDataSet.CalendarExceptionsDataTable. If you try to filter rows in a secondary DataTable, the PSI returns a FilterInvalid exception.

With the exception of the ResourceDataSet.ResourceCustomFields secondary table, however, you can use the Fields.Add method to filter columns in the secondary tables.

Table 1. Primary datatables for PSI methods that use a filter

PSI Method

Primary DataTable

ReadCalendars

CalendarDataSet.CalendarsDataTable

ReadCustomFields

CustomFieldDataSet.CustomFieldsDataTable

ReadLookupTables

LookupTableDataSet.LookupTablesDataTable

ReadLookupTablesMultiLang

LookupTableMultiLangDataSet.LookupTablesDataTable

ReadResources

ResourceDataSet.ResourcesDataTable

ReadResourceAssignments

ResourceAssignmentDataSet.ResourceAssignmentDataTable

ReadResourcePlan

ResourcePlanDataSet.PlanResourcesDataTable

The procedures in this article use Microsoft Visual C# and Microsoft Visual Studio 2005. For the complete sample code of the GetCustomFieldsByEntity method, see Example. For more information about custom fields and lookup tables, see Local and Enterprise Custom Fields. For another code sample using xmlFilter, see Resource.ReadResources.

The Microsoft.Office.Project.Server.Library namespace includes the Filter class. You can set the following properties of a Filter object to limit and organize the data that a PSI method returns in a DataSet.

  • FilterTableName specifies the name of the DataTable you want. For example, a LookupTableDataSet includes three tables (LookupTables, LookupTableMasks, and LookupTableTrees). The table name is a string that you can get from a DataSet object.

  • To filter columns: The Fields property is a collection of column names. You can use the Fields.Add method to specify the columns you want returned, and optionally set the sort order with the SortOrderType enumeration. You can get the column name string from a DataSet object.

    You can use a primary or secondary DataTable to create a column filter, with the exception of the ResourceDataSet.ResourceCustomFieldsDataTable.

  • To filter rows: The Criteria property specifies conditions for the rows to return in the result DataSet. Because you cannot specify the DataTable to use, Criteria works only on the primary DataTable (see Table 1). For example, you can specify fields of the ResourcesDataTable in a filter for ResourceDataSet, but not fields of the ResourceAvailabilitiesDataTable

    You can specify that one or more fields must contain certain values. The rows that match all of the Criteria conditions in the Filter object are included in the returned DataSet. You can use the Filter.FieldOperationType enumeration to set operations such as Equal, GreaterThan, or Contain. You can also use Filter.LogicalOperationType to add And and Or clauses.

After you set the Filter properties, use the GetXml method to get the string for the xmlFilter or filter parameter of the PSI method.

Procedure 1. Filtering for custom fields by entity:

  1. Set a Web reference to the CustomFields PSI Web service. For example, name the reference WebSvcCustomFields.

  2. Set a reference to the Microsoft.Office.Project.Server.Library.dll assembly. You can copy the assembly from the [Program Files]\Microsoft Office Servers\12.0\Bin directory on the Project Server computer to your development computer. The class in which you add the custom field method must include the following references.

    using System.Web.Services.Protocols;
    using PSLibrary = Microsoft.Office.Project.Server.Library;
    
  3. Create a method that reads custom field data for a specified Project Server entity. For example, the GetCustomFieldsByEntity method has the following parameters:

    • cf is a CustomFields object.

    • entityUid is a GUID that specifies the type of Project Server entity (project, resource, or task).

    • sortOrder is a value from SortOrderTypeEnum in the PSLibrary namespace.

    The cfDataSet object is a CustomFieldDataSet that you assign to the results of the ReadCustomFields call.

    WebSvcCustomFields.CustomFieldDataSet GetCustomFieldsByEntity(
        WebSvcCustomFields.CustomFields cf,
        Guid entityUid,
        PSLibrary.Filter.SortOrderTypeEnum sortOrder)
    { 
        WebSvcCustomFields.CustomFieldDataSet cfDataSet = 
            new WebSvcCustomFields.CustomFieldDataSet();
    
        /* Create and initialize other class variables. */
        /* Create a Filter object and use it with the ReadCustomFields method. */
    
        return cfDataSet;
    }
    
  4. Create and initialize other class variables. You can get the names of the table and columns you need from the CustomFieldDataSet object. For example, the value of tableName becomes "CustomFields" and "MD_PROP_NAME" is the value of nameColumn.

    You can also define operators you need to use for field comparisons and logical operators and and or. In this case, equal is the only operator needed. Variables for field comparison operators and logical operators are not required, but help to clarify the code when you create the filter criteria.

        string tableName = cfDataSet.CustomFields.TableName;
        string nameColumn = 
            cfDataSet.CustomFields.MD_PROP_NAMEColumn.ColumnName;
        string uidColumn = 
            cfDataSet.CustomFields.MD_PROP_UIDColumn.ColumnName;
        string entityUidColumnName = 
            cfDataSet.CustomFields.MD_ENT_TYPE_UIDColumn.ColumnName;
    
        PSLibrary.Filter.FieldOperationType equal =
            PSLibrary.Filter.FieldOperationType.Equal;
    
  5. Create a Filter object and restrict the filter to one table in the DataSet. In this case, the CustomFieldDataSet has only one table, named CustomFields. Other datasets can have more than one table.

        PSLibrary.Filter cfFilter = new PSLibrary.Filter();
        cfFilter.FilterTableName = tableName;
    
  6. Add fields to the filter to specify the columns you want returned. You can optionally set the sort order on a field by using the Field constructor override that has three parameters. The following example gets the MD_PROP_NAME and MD_PROP_UID columns, and sets a specified sort order on the custom field name.

        cfFilter.Fields.Add(
            new PSLibrary.Filter.Field(tableName, nameColumn, sortOrder));
        cfFilter.Fields.Add(new PSLibrary.Filter.Field(uidColumn));
    
  7. Set the filter criteria to specify the rows to return. Filter criteria can get complex; you can see how equal and the other variables help to simplify the expression. In the following example, the MD_ENT_TYPE_UID field must have a value that is equal to the specified entity type GUID. That is, if you call GetCustomFieldsByEntity with a TaskEntity GUID, the method returns a CustomFieldDataSet that contains only task custom fields.

        cfFilter.Criteria = new PSLibrary.Filter.FieldOperator(
                equal, entityUidColumnName, entityUid);
    
  8. Use the GetXml method of the Filter object to create the string value for the xmlFilter parameter of the ReadCustomFields method.

        bool autoCheckOut = false;
        cfDataSet = cf.ReadCustomFields(cfFilter.GetXml(), autoCheckOut);
    

    The GetCustomFieldsByEntity method returns the cfDataSet, which contains only the custom field names and GUIDs for the entity type requested.

You can use the GetCustomFieldsByEntity method in many ways. For example, add a form and a DataGridView to the sample application described in How to: Log on to Project Server Programmatically. Procedure 2 shows an example of how to call GetCustomFieldsByEntity and load a grid with the result.

Procedure 2. Testing the GetCustomFieldsByEntity method:

  1. Add a reference to Microsoft.Office.Project.Server.Library. Instantiate a CustomFields object as a class variable, and add the context information as shown in the LogonDemo application (set the Url, Credentials, and CookieContainer properties). In the following example, the Web reference to the CustomFields Web service is named WebSvcCustomFields.

    using PSLibrary = Microsoft.Office.Project.Server.Library;
    . . .
    public static WebSvcCustomFields.CustomFields customFields =
        new WebSvcCustomFields.CustomFields();
    
  2. Add a class variable for the entity GUID, as in the following example.

    private Guid cfEntityUid = 
                new Guid(PSLibrary.EntityCollection.Entities.ProjectEntity.UniqueId);
    
  3. Add a method that calls GetCustomFieldsByEntity and loads the grid. For example, in the LoadGrid method, the DataGridView object name is dgViewCustomFields.

    private void LoadGrid()
    {
        // Create a sort order variable; for example, sort descending
        PSLibrary.Filter.SortOrderTypeEnum descSortOrder = 
            Microsoft.Office.Project.Server.Library.Filter.SortOrderTypeEnum.Desc;
        
        // Create a CustomFieldDataSet and assign the results to it
        WebSvcCustomFields.CustomFieldDataSet dsCustomField;
        
        dsCustomField = GetCustomFieldsByEntity(customFields, cfEntityUid, descSortOrder);
    
        // Create a DataView with the CustomFields table.
        DataView dataView = new DataView(dsCustomField.CustomFields);
        
        // Assign the data to the grid.
        dgViewCustomFields.DataSource = dataView;
        dgViewCustomFields.AutoResizeColumns();
        dgViewCustomFields.Refresh();
    }
    
  4. On a form, add three RadioButton objects. Name them radProject, radTask, and radResource, and then label them Project, Task, and Resource. The following example shows the CheckedChanged event handlers for the radio buttons.

    // Each radio button sets the appropriate GUID for the entity.
    private void radProject_CheckedChanged(object sender, EventArgs e)
    {
        if (radProject.Checked)
        {
            cfEntityUid = new Guid(PSLibrary.EntityCollection.Entities.ProjectEntity.UniqueId);
            LoadGrid();
        }
    }
    
    private void radTask_CheckedChanged(object sender, EventArgs e)
    {
        if (radTask.Checked)
        {
            cfEntityUid = new Guid(PSLibrary.EntityCollection.Entities.TaskEntity.UniqueId);
            LoadGrid();
        }
    }
    
    private void radResource_CheckedChanged(object sender, EventArgs e)
    {
        if (radResource.Checked)
        {
            cfEntityUid = new Guid(PSLibrary.EntityCollection.Entities.ResourceEntity.UniqueId);
            LoadGrid();
        }
    }
    

When you run the test application and click Resource, you can see the grid contains the following data for the default resource custom fields. The custom fields are sorted by name in descending order.

MD_PROP_UID

MD_PROP_NAME

0000740f-cf67-4b93-a405-f0c12c5bc942

Team Name

000039b7-8bbe-4ceb-82c4-fa8c0c400284

RBS

0000783f-de84-434b-9564-284e5b7b3f49

Cost Type

Following is the complete sample code for the GetCustomFieldsByEntity method.

/// <summary>
/// Gets the name and GUID of all custom fields for the specified entity.
/// </summary>
/// <param name="cf">CustomFields object</param>
/// <param name="entityUid">GUID of entity type fo project, resource, or task</param>
/// <param name="sortOrder">Sort order enum</param>
/// <returns>CustomFieldDataSet with selected custom fields for the entity</returns>
WebSvcCustomFields.CustomFieldDataSet GetCustomFieldsByEntity(
    WebSvcCustomFields.CustomFields cf,
    Guid entityUid,
    PSLibrary.Filter.SortOrderTypeEnum sortOrder)
{
    WebSvcCustomFields.CustomFieldDataSet cfDataSet = 
        new WebSvcCustomFields.CustomFieldDataSet();

    string tableName = cfDataSet.CustomFields.TableName;
    string nameColumn = cfDataSet.CustomFields.MD_PROP_NAMEColumn.ColumnName;
    string uidColumn = cfDataSet.CustomFields.MD_PROP_UIDColumn.ColumnName;
    string entityUidColumnName = cfDataSet.CustomFields.MD_ENT_TYPE_UIDColumn.ColumnName;

    // Define an operator for comparing a field with data.
    PSLibrary.Filter.FieldOperationType equal = 
        PSLibrary.Filter.FieldOperationType.Equal;
    
    // Create a Filter object.
    PSLibrary.Filter cfFilter = new PSLibrary.Filter();

    // Restrict the filter to one table.
    // (the only table in the case of the CustomFieldsDataSet)
    cfFilter.FilterTableName = tableName;

    // Add fields to the filter to limit the columns you want returned.
    // Set the sort order on the name column (optional for the Field constructor).
    cfFilter.Fields.Add(new PSLibrary.Filter.Field(tableName, nameColumn, sortOrder));
    cfFilter.Fields.Add(new PSLibrary.Filter.Field(uidColumn));

    // Use the Criteria property to set conditions for the rows you want returned.  
    // You can use multiple sets of operators, and connect the conditions with logical operators.
    // In this case, the MD_ENT_TYPE_UID field must have a value equal to the entityUid. 
    cfFilter.Criteria = new PSLibrary.Filter.FieldOperator(equal, entityUidColumnName, entityUid);

    // The GetXml method creates the xmlFilter parameter for ReadCustomFields 
    bool autoCheckOut = false;
    cfDataSet = cf.ReadCustomFields(cfFilter.GetXml(), autoCheckOut);

    return cfDataSet;
}
Show:
© 2014 Microsoft