Pivot Manager (REST)

The Relativity REST API includes a Pivot Manager service that provides operations for working with custom Pivot queries and Pivot profiles. You can use the service to create and run custom Pivot queries and to read and run queries that are based on existing Pivot profiles. The supported operations are equivalent to the supported methods for interacting with Pivot objects in the Relativity Services API. Unlike the Services API however, the Pivot Manager service doesn’t support use of progress indicators or cancellation tokens.

Note that the Pivot Manager service doesn’t provide operations for creating, updating, or deleting Pivot queries that persist in Relativity. This means that you can use the service to create and run a custom query but you can’t store and subsequently update or delete the query or query results in Relativity. Similarly, you can use the service to read an existing Pivot profile and run a query that’s based on the profile, but you can’t create, update, or delete a Pivot profile. Overall, the service is designed to help you transfer data to other applications and tools, such as a third-party software application or web-based visualization tool, for deeper analysis and reporting. For example, you might use the service to implement a Pivot query as part of a larger solution that captures query results, writes the results to a specified table, object, or other location, and then manipulates the results in that location.

Pivot fundamentals

To interact with the Pivot Manager service, you send HTTP(S) requests that use the POST method and specify query conditions in the body of the request.

  • Set the {versionNumber} placeholder to the version of the REST API that you want to use, using the format lowercase v and the version number, for example v1 or v2
  • Set other path parameters in the URLs to the Artifact ID of a given entity, for example setting {workspaceID} to the Artifact ID of a workspace.

Note: To indicate the admin-level context, set the {workspaceID} path parameter to -1.

Set the path parameters as follows:

  • {versionNumber} to the version of the service, such as v1.
  • {workspaceID} to the Artifact ID of the workspace that contains the index.

All Pivot requests use the following base URL:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/

For example, use the following URL to create and run a custom Pivot query:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/queries

Or use the following URL to read and run a query based on a Pivot profile:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/profiles/{pivotProfileID}/execute

Note that the base URL format for the Pivot Manager service is different from most other Relativity REST services.

Like other query-based services of the Relativity REST API, request bodies and server responses for the Pivot Manager service are sent in JSON payloads. The body of a POST request for a query includes a JSON representation of fields, conditions, and other parameters for the query. The body of a response returns all the query results as a single result set; the Pivot Service Manager doesn’t provide built-in support for paging. For information about the options and syntax that you can use for Pivot and other query services in the Relativity REST API, see Query for resources.

Fields

The Pivot Manager service uses a combination of unique fields and fields that are common to the Relativity REST API. The following table lists and describes the required fields for the Pivot Manager service and indicates whether each field is common to the REST API or specific to a type of Pivot query.

Field Type Description Scope
ArtifactTypeID Number The Artifact Type identifier of the type of DTO to query—for example, “10” for documents. Common
GroupBy Object The view field identifier, Artifact identifier, GUID, or name of the field to use for grouping data. Custom Pivot queries only
GroupByDateGrouping Number The calendar-based time interval to use when calculating data for the GroupBy field, if the GroupBy field is a Date field:

0 = Day, month, and year

1 = Month

2 = Year

3 = Month and year

Custom Pivot queries only
objectSetQuery Object The name of a query that defines the criteria that objects or documents must meet to be included in the Pivot query. To query all objects or documents in a workspace, don’t specify a value for the condition of this field. Common
PivotOn Object The view field identifier, Artifact identifier, GUID, or name of the field to use for pivoting data. Custom Pivot queries only
PivotOnDateGrouping Number The calendar-based time interval to use when calculating data for the PivotOn field, if the PivotOn field is a Date field:

0 = Day, month, and year

1 = Month

2 = Year

3 = Month and year

Custom Pivot queries only
pivotProfileId Number The Artifact identifier of the Pivot profile to read and run. Profile-based queries only
RawDataOnly Boolean Whether to apply a Pivot view to query results. If “true”, only the data is returned. Custom and profile-based queries
Timeout Number The maximum amount of time, in seconds, to run the query before timing out. Custom and profile-based queries
Toggles Object The Toggles property on a Pivot profile contains various options that control how data in a chart is displayed. For example, these options control the display of labels, blank values, sub-charts, and others. Custom and profile-based queries
workpsaceId Number The Artifact identifier of the workspace to query. Common

Before you can use any type of field to group data in a Pivot query, the field must be enabled for grouping. Similarly, a field must be enabled for pivot before you can use it to pivot data. In addition, you can pivot data by using a Date field only if you also use a Date field to group data, and you can pivot data by month only if you also group data by year. For information about getting a list of workspace fields that you can use to group or pivot data, see Helper operations.

Note that you must have permission to view a field before you can use it in a Pivot query. Otherwise, an error occurs when you try to run the query because the Pivot Service Manager uses the permission settings of the active user account.

HTTP headers and status codes

The Pivot Manager service uses the same HTTP(S) headers and status codes as other services in the Relativity REST API. For details about required headers and fields, see HTTP headers. For details about HTTP status codes, see HTTP status codes in Relativity REST APIs.

Create a Pivot profile

To create a Pivot profile, send a request to this URL for the Pivot Manager service:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/profiles

The request includes the fields listed in the following sample JSON code. For more information about these fields, see Pivot profiles on the Relativity Documentation site.

Copy 
{
  "workspaceId": 1015337,
  "profile": {
    "Name": "Create",
    "SortOn": "GrandTotal",
    "SortOrder": "Ascending",
    "PageSize": 10,
    "SwitchSeries": "false",
    "Toggles": {
      "ShowBlankValues": false,
      "ShowLegend": true
      "ShowSubChart": true

    },
    "ObjectType": {
      "Name": "Document"
    },
    "GroupByField": {
      "ArtifactID": 1035357
    },
    "PivotOnField": {
      "ArtifactID": 1035351
    },
    "ChartType": "Bar",
    "ChartOrientation": "Vertical"
  }
}

The response returns a JSON object with the fields set in the new Pivot profile.

Read a Pivot profile

To read and run a query based on a Pivot profile, send a request to the following Pivot Manager service URL:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/profiles/{pivotProfileID}/execute

Ensure that the request includes required headers for the Relativity REST API and required fields for the Pivot Manager service. The required fields are: workspaceId, pivotProfileId, and objectSetQuery.

In the following example, a client reads and runs a query based on a Pivot profile. The profile is stored in a workspace that has the Artifact identifier value “1032131”. The profile has the Artifact identifier value “1081794”. The query isn’t limited to a subset of documents or objects in the workspace, as indicated by a Null value for the objectSetQuery field condition.

Copy 

{
     workspaceId: 1032131, 
     pivotProfileId: 1081794,
     objectSetQuery: { Condition: " " }
}

To see the JSON response to the preceding request, see Sample JSON response. For insight into the Pivot profile settings that generated the response, see the next section, which demonstrates how to execute a custom Pivot query and uses the same settings as the Pivot profile in the preceding example.

Update a Pivot profile

To update a Pivot profile, send a request to this URL for the Pivot Manager service:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/profiles/{pivotProfileID}

The request includes the fields listed in the following sample JSON code. For more information about these fields, see on the Relativity Documentation site.

Copy 

{
  "workspaceId": 1015337,
  "profile": {
    "Name": "Update",
    "SortOn": "GrandTotal",
    "SortOrder": "Ascending",
    "PageSize": 10,
    "SwitchSeries": "false",
    "Toggles": {
      "ShowBlankValues": false,
      "ShowLegend": true
      "ShowSubChart": true
    },
    "ObjectType": {
      "Name": "Document"
    },
    "ID": 1040623,
    "GroupByField": {
      "ArtifactID": 1035357
    },
    "PivotOnField": {
      "ArtifactID": 1035351
    },
    "ChartType": "Bar",
    "ChartOrientation": "Vertical"
  }
}

The response returns a JSON object with the fields set in the updated Pivot profile.

Execute a custom Pivot query

To execute a custom Pivot query, send a request to the following Pivot Manager service URL:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/queries

Ensure that the request includes required headers for the Relativity REST API and required fields for the Pivot Manager service. Required fields are:

  • ArtifactTypeID
  • GroupBy
  • GroupByDateGrouping if you want to group data by using a Date field
  • objectSetQuery
  • PivotOn if you want to pivot data
  • PivotOnDateGrouping if you want to pivot data by using a Date field
  • RawDataOnly
  • Timeout
  • workpsaceId

To determine which fields you want to use to group and pivot data, you can use Pivot Service Manager helper operations to get a list of workspace fields that are enabled for grouping and pivoting.

The following code example defines a custom query that primarily does the following:

  1. Queries all documents in a workspace.
  2. Groups data by the email address stored in a Fixed-Length Text field named “Email From”.
  3. Pivots data based on the values in a Yes/No field named “Designation”.

To optimize workspace performance and query results, the example also limits the number of results returned and specifies a timeout value for stopping the query.

Copy 

{
     //Specify which workspace to query.
     workspaceId: 1032131,
     settings: {
          /*
          Set the Artifact Type identifier of the DTO to query. This example queries documents, which 
          have an ArtifactTypeID value of “10”.
          */
          ArtifactTypeID: 10, 
          
          //Group data by using the “Email From” field, which has a view field identifier value of “1000566”.
          GroupBy: { 
               ViewFieldID: 1000566    
          },
          
          //Pivot data by using the “Designation” field, which has a view field identifier value of “1000555”.
          PivotOn: {
               ViewFieldID: 1000555
          },
        
          //Limit the results to a specific number of columns and rows.
          MaximumNumberOfColumns: 50,
          MaximumNumberOfRows: 1200,

          //Run the query for no more than 1,020 seconds (17 minutes).
          Timeout: 1020,

          //Return only the data in query results; do not apply Pivot view settings to the results.
          RawDataOnly: false,

          //Query all documents in the workspace; do not limit the query to a subset of documents. 
          ObjectSetQuery: { Condition: " " },
     } 
}

Export a Pivot query

To export a Pivot query, send a request to this URL for the Pivot Manager service:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/queries/export

The request includes the fields listed in the following sample JSON code. For more information about these fields, see on the Relativity Documentation site.

The response contains the Content Type, and Content field, which has been truncated in the following example. It also includes a Success field, which indicates the status of the export.

Copy 

   {
  "ContentType": "string",
  "Content": "string",
  "Success": true
}

Sample JSON response

The following JSON sample shows the response to the custom Pivot query and the profile-based query discussed in the preceding sections. The Results fields report the total number of documents (891) that are in the workspace and contain data in the Email From field. They also report how many of those documents are coded as responsive (True) and non-responsive (False).

Subsequent objects are grouped by the email address stored in the Email From field. The object fields report the applicable email address, the number of documents tagged as responsive (True) and non-responsive (False) for the address, and the total number of documents that have the address in the Email From field. The last four fields in the response provide information about the Pivot query overall—the query returned 277 objects, a Query object was not used, and the query ran successfully.

Helper operations

To help you determine which fields you can use to group and pivot data in a Pivot query, the Pivot Manager Service provides the following helper operations:

  • getGroupByFieldsForDocuments – Returns a list of fields that are in a workspace and enabled for grouping data.
  • getPivotOnFieldsForDocuments – Returns a list of fields that are in a workspace and enabled for pivoting data.

Note that the list of fields that is returned includes only those fields that you have permission to view. As is the case with Pivot queries, the helper operations use the permission settings of the active user account.

getGroupByFieldsForDocuments

To get a list of all the fields that you can use to group data in a workspace, send an HTTP(S) POST request to the following URL:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/fields/group-by/{artifactTypeID}

Required fields for the request are workspaceId and ArtifactTypeId. For example, the following request returns a list of fields that are in a workspace that has an Artifact identifier value of “1234567”, apply to the Document object, can be used to group data, and the active user has permission to view:

Copy 

{
     workspaceId: 1234567,
     ArtifactTypeId: 10
};

The JSON response returns the Artifact identifier, view field identifier, and name of each field:

Copy 

[
  {
    "ArtifactID": 0,
    "ViewFieldID": 0,
    "Guids": [
      "string"
    ],
    "Name": "string"
  }
]

When you create a custom query, you can use any of those identifiers to specify the field that you want to use to group data.

getPivotOnFieldsForDocuments

To get a list of all the fields that you can use to pivot data in a workspace, send an HTTP(S) POST request to the following URL:

Copy 
<host>/Relativity.REST/api/relativity-pivot/{versionNumber}/workspaces/{workspaceID}/fields/pivot-on/{artifactTypeID}

Required fields for the request are workspaceId and ArtifactTypeId. In the following example, the request returns a list of fields that are in a workspace that has an Artifact identifier value of “1234567”, apply to the Document object, can be used to pivot data, and the active user has permission to view:

Copy 

{
     workspaceId: 1234567,
     ArtifactTypeId: 10
};

The JSON response returns the Artifact identifier, view field identifier, and name of each field:

Copy 
[
  {
    "ArtifactID": 0,
    "ViewFieldID": 0,
    "Guids": [
      "string"
    ],
    "Name": "string"
  }
]

When you create a custom query, you can use any of those identifiers to specify the field that you want to use to pivot data.