CA Clarity PPM Data Providers Used for CA Clarity PPM Web Parts › Using Studio Queries as Data Providers for CA Clarity PPM Web Parts › Query Requirements for Use in Microsoft SharePoint

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 CA Clarity PPM 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 Clarity PPM Data Provider list in the tool panes of CA Clarity 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 Clarity PPM project site is bound to. For a CA Clarity PPM Web Part in a non-CA Clarity 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 Clarity PPM project.

• The following two properties, which enable incremental synchronization with the information in CA Clarity 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 Clarity PPM sub-objects.

  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 Clarity PPM sub-objects.

• 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 Clarity PPM List Web Parts.
• The query is required to be a single-dimension query.

Note the following:

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