CA PPM Data Providers Used for Web Parts

The Microsoft SharePoint Connector can use cappm data providers as the source for Web Parts. The data providers that are available in Web Parts are stock and custom subobjects of projects and Studio queries.
The Microsoft SharePoint Connector can use 
 data providers as the source for Web Parts. The data providers that are available in Web Parts are stock and custom subobjects of projects and Studio queries.
The available data providers depend on the server, proxy user, and project for the project site. For a non-
 project site, the available data providers depend on the Web Part. When configuring a Web Part, the following factors affect the list of data providers:
  • The configuration of the selected server
  • The access rights of the selected proxy user
  • The partition of the selected project
Verify that your
 Studio queries conform to certain requirements before you use them as data providers in Microsoft SharePoint. For example, you can add a 
 Web Part to a project site or to an external project site. In either case, the query executes on the particular 
 server that retrieves the information.
Stoplights Available in List Web Parts
You can define a display mapping for numeric attributes. Display mappings represent values using a series of icons or colors. Map values or ranges of values to icons or colors in lists and forms instead of, or in addition to, the numeric value. The set of available icons is fixed in 
. The system uses a standard set of images with the corresponding color.
To make these mappings available in list Web Parts, all numeric attributes are represented twice. The attribute name (
) is the value. The attribute name with a trailing asterisk (
*) is the icon. Columns for some stoplights appear by default in list Web Parts. To display more stoplight columns, modify the list view.
Project Sub-Objects Accessible from List Web Parts
Sub-objects of 
 projects can be stock objects or custom objects created in the organization implementation of 
. Team, Risks, and Tasks are examples of stock objects. Additionally, each sub-object can have predefined stock attributes and user-defined custom attributes.
You can define the following kinds of attributes for 
  • Large strings
  • Multi-valued lookups
  • CA PPM
    Document attachments
  • Time-varying attributes
The attributes are not exposed in Microsoft SharePoint.
The following table provides information about the stock 
 project sub-objects that you can access using 
 list Web Parts and the associated pages in 
 from which the lists are retrieved.
Data Provider
Application Page
Project: Properties: Baseline
Change Request
Project: Risks/Issues/Changes: Change Requests
Project Document Manager (the Documents subtab that displays on the project's Collaboration tab)
Project: Risks/Issues/Changes: Issues
Project: Team: Requisitions
Project: Risks/Issues/Changes: Risks
Project: Tasks: Work Breakdown Structure
Project: Team: Staff
The columns that appear by default in the All Items view of the list in a list Web Part are the columns that are in the System partition default list view for that object in 
. Additional information about some of the available data providers is included in the following list:
  • Documents:
     All the documents contained in the 
    CA PPM
     project Document Manager appear in a non-hierarchical list. The Parent Folder column, which appears by default, indicates each document's parent folder.
    Document Manager security is honored when you access documents. Therefore, you can provide appropriate access rights to the
    CA PPM
    proxy user account for the project Document Manager to allow, or disallow access to certain documents.
  • Task:
    CA PPM
     list Web Part that displays tasks from a project displays all the tasks in the entire work breakdown structure, regardless of the level that they are at. That is, the CA Clarity List Web Part displays task breakdown from the WBS page of project tasks. Additional fields, such as Work Breakdown Structure (WBS) Level and WBS Sequence, can be added by as columns by modifying the Web Part view.
    CA PPM
    list Web Part to display tasks in the same order as in the project Work Breakdown Structure (WBS) page, sort the Web Part by the WBS Sequence attribute. To show the tasks at a certain level of the work breakdown structure, filter the list by the WBS Level attribute. For example, to show all top-level tasks, filter the list to show only those items whose WBS Level is 1.
Using Studio Queries as Data Providers for Web Parts
You can use the Studio queries that you create and configure in 
 as data providers for Web Parts. However, the queries require conforming to certain requirements before using them as data providers for Web Parts.
Using Studio queries as data providers for Web Parts can produce unexpected results if the queries do not conform to the requirements described in this section. Standard Studio queries used for grid and chart portlets in
do not work in Microsoft SharePoint. Studio queries created for use in Microsoft SharePoint do not work in
Query Requirements for Use in Microsoft SharePoint
The queries created using Studio need to meet the following requirements before you can use them as data providers for 
 Web Parts:
  • The query ID begins with “sp_qry_”.
    “sp_qry_” is in lower case.
  • The query category is Project.
    Queries that do not belong to the Project category do not appear in the 
    CA PPM
     Data Provider list in the tool panes of 
    CA PPM
     Web Parts.
  • The WHERE statement in the NSQL query includes a filter that confines the scope of the query to the project that the 
    CA PPM
     project site is bound to. For a 
    CA PPM
     Web Part in a non-
    CA PPM
     project site, the project that the Web Part is bound to.
    You can write a WHERE statement that restricts the query to run on a particular 
    CA PPM
  • The following two properties, which enable incremental synchronization with the information in 
    CA PPM
     Web Parts, are included in the NSQL query:
    • change_key
      A string value that changes whenever the query-returned values change. Using the LAST UPDATED DATE, cast to a string, is a good choice for 
      CA PPM
    • merge_key
      A string value that uniquely and persistently identifies each record retrieved by the query. It is used as a key to determine the items in SharePoint required to be updated, created, and deleted. Using the ID, cast to a string, is a good choice for 
      CA PPM
  • For the query, define a link that begins with sp_link_ to allow the user to be able to use the Link to Clarity feature provided in 
    CA PPM
     List Web Parts.
  • The query is required to be a single-dimension query.
  • Do not omit the change_key and merge_key properties from the NSQL query. If you omit these properties from the query, all items for that list in SharePoint will be completely replaced when you synchronize the list. This will have undesirable results, such as synchronization being slow, all items in the list being incorrectly shown as new and changed, and excessive notifications.
  • LAST_UPDATED_DATE from multiple source tables can be concatenated in a query to generate a single change key that will change whenever any data in the source tables change.
  • The first string property in the query will become the “Title” field (column) in SharePoint.
  • The first six fields in the query will by default be included in the All Items view in SharePoint.
  • Only the properties defined in the query will appear as fields and available choices for creating charts in SharePoint. Metrics will not.
Restrict a Studio Query to Execute on a Particular Project
You can include the following WHERE statement to restrict queries to run on particular project in 
. The WHERE statement has the following format:
project ID = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
  • project ID
    Any valid SQL expression that can represent the ID of the project.
The right-hand side of the statement retrieves the associated project ID for the 
 project site. The statement retrieves the associated project for the Web Part in a non-
 project site.
You can include the WHERE statement multiple times in the same query. Use multiple WHERE statements in a query that includes UNION expressions or WHERE statements.
Write Useful Studio Queries for Microsoft SharePoint
Use the following hints to write useful queries to use as data providers for 
 Web Parts. Or, to display important project information on the sites that you create. More hints are included in other sections of these articles.
  • Filtering the items in a list by using a Studio query is generally faster and better than retrieving all of the items and then filtering the list in the SharePoint view. Therefore, consider writing Studio queries to filter down large lists.
     If Work Breakdown Structure has a large number of tasks, use a query to retrieve only key or current tasks.
  • Write and use NSQL queries that use the WBS Level attribute to filter a task list to show tasks that are at the top level of the work breakdown structure (where WBS Level equals 1).