Custom Pivot API


The custom pivots API allows you to take existing list and pivot reports and represent them as JSON. This enables developers to pull related data from multiple types of records in a single API query.

There are two types of reports that you can pull via this API: list or pivot. See the documentation below for a full explanation of their formats.

The :report_id parameter is the ID of a saved view in Aha!. To find the ID of an existing report, choose the report from the Views -> Saved views dropdown in Aha!. Then take the large number of the end of the URL that looks like: /bookmarks/custom_pivots/6434552458299516367. In this case the :report_id is 6434552458299516367.

GET /api/v1/bookmarks/custom_pivots/:report_id

List view

The list view responds with a JSON object with three attributes: pagination, columns, and rows.

The columns attribute is an array of objects that give detailed information about the columns of the list. The objects include the name of the table, the field, and a human readable title attribute.

The rows attribute is an of array of arrays. Each object within the inner array contains the value of that for a specific column. Each object contains attributes for various representations of the data. The fields are:

  • id: The unique id of the object
  • plain_value: A string representation of the object
  • html_value: An HTML representation of the object. This is the same HTML that is used in Aha! to represent the value in our reports.
  • rich_value: If the object is a reference to another object (such as a tag), the, this will be the object representation. For instance, a tag would be representated as: { id: "6013053275679792248", name: "API" }

The thirds attribute is pagination. This works like everywhere else in the Aha! api and is documented (here)[/api#pagination]

Parameters

Name Description
report_id The ID of the saved view in Aha! to report on.
view The type of API response you want. This should either be list or pivot. It defaults to list

GET /api/v1/bookmarks/custom_pivots/801750833?view=list

Example CURL command

curl -g "https://secure.aha.io/api/v1/bookmarks/custom_pivots/801750833?view=list" -X GET \
	-H "Authorization: Bearer 15b60d42d4bc417284a246ced6877b0bf13fb4aca415f7b55f7006bc3694a8ab" \
	-H "X-Aha-Account: account1" \
	-H "Content-Type: application/json" \
	-H "Accept: application/json"

Request

Authorization: Bearer 15b60d42d4bc417284a246ced6877b0bf13fb4aca415f7b55f7006bc3694a8ab
X-Aha-Account: account1
Content-Type: application/json
Accept: application/json

Response

Status: 200 OK
  Content-Type: application/json; charset=utf-8
{
  "pagingation": [
    {
      "total_records": 3,
      "total_pages": 1,
      "current_page": 1
    }
  ],
  "columns": [
    {
      "table": "projects",
      "field": "name",
      "title": "Product name"
    },
    {
      "table": "releases",
      "field": "name",
      "title": "Release name"
    },
    {
      "table": "features",
      "field": "name",
      "title": "Feature name"
    },
    {
      "table": "features",
      "field": "reference_num",
      "title": "Feature reference #"
    },
    {
      "table": "features",
      "field": "status",
      "title": "Feature status"
    }
  ],
  "rows": [
    [
      {
        "id": 131414752,
        "plain_value": "Project 1",
        "html_value": "<a data-drawer-url=\"/projects/PRJ1\" href=\"/projects/PRJ1\">Project 1</a>",
        "rich_value": "Project 1"
      },
      {
        "id": 278327321,
        "plain_value": "Release 1",
        "html_value": "<a data-drawer-url=\"/releases/PRJ1-R-1\" href=\"/releases/PRJ1-R-1\">Release 1</a>",
        "rich_value": "Release 1"
      },
      {
        "id": 303873333,
        "plain_value": "Another Third Feature",
        "html_value": "<a data-drawer-url=\"/features/PRJ1-3\" href=\"/features/PRJ1-3\">Another Third Feature</a>",
        "rich_value": "Another Third Feature"
      },
      {
        "id": 303873333,
        "plain_value": "PRJ1-3",
        "html_value": "<a data-drawer-url=\"/features/PRJ1-3\" href=\"/features/PRJ1-3\">PRJ1-3</a>",
        "rich_value": "PRJ1-3"
      },
      {
        "id": 303873333,
        "plain_value": "Designed",
        "html_value": "<span class=\"status-pill\" style=\"border-color: #ecdd8f; background-color: #f7f1d2; color: #5e5839;\">Designed</span>",
        "rich_value": {
          "id": 962984386,
          "name": "Designed",
          "color": "727272"
        }
      }
    ],
    [
      {
        "id": 131414752,
        "plain_value": "Project 1",
        "html_value": "<a data-drawer-url=\"/projects/PRJ1\" href=\"/projects/PRJ1\">Project 1</a>",
        "rich_value": "Project 1"
      },
      {
        "id": 278327321,
        "plain_value": "Release 1",
        "html_value": "<a data-drawer-url=\"/releases/PRJ1-R-1\" href=\"/releases/PRJ1-R-1\">Release 1</a>",
        "rich_value": "Release 1"
      },
      {
        "id": 622562724,
        "plain_value": "Another Feature",
        "html_value": "<a data-drawer-url=\"/features/PRJ1-2\" href=\"/features/PRJ1-2\">Another Feature</a>",
        "rich_value": "Another Feature"
      },
      {
        "id": 622562724,
        "plain_value": "PRJ1-2",
        "html_value": "<a data-drawer-url=\"/features/PRJ1-2\" href=\"/features/PRJ1-2\">PRJ1-2</a>",
        "rich_value": "PRJ1-2"
      },
      {
        "id": 622562724,
        "plain_value": "Designed",
        "html_value": "<span class=\"status-pill\" style=\"border-color: #ecdd8f; background-color: #f7f1d2; color: #5e5839;\">Designed</span>",
        "rich_value": {
          "id": 962984386,
          "name": "Designed",
          "color": "727272"
        }
      }
    ],
    [
      {
        "id": 131414752,
        "plain_value": "Project 1",
        "html_value": "<a data-drawer-url=\"/projects/PRJ1\" href=\"/projects/PRJ1\">Project 1</a>",
        "rich_value": "Project 1"
      },
      {
        "id": 278327321,
        "plain_value": "Release 1",
        "html_value": "<a data-drawer-url=\"/releases/PRJ1-R-1\" href=\"/releases/PRJ1-R-1\">Release 1</a>",
        "rich_value": "Release 1"
      },
      {
        "id": 1007868956,
        "plain_value": "Feature 1",
        "html_value": "<a data-drawer-url=\"/features/PRJ1-1\" href=\"/features/PRJ1-1\">Feature 1</a>",
        "rich_value": "Feature 1"
      },
      {
        "id": 1007868956,
        "plain_value": "PRJ1-1",
        "html_value": "<a data-drawer-url=\"/features/PRJ1-1\" href=\"/features/PRJ1-1\">PRJ1-1</a>",
        "rich_value": "PRJ1-1"
      },
      {
        "id": 1007868956,
        "plain_value": "New",
        "html_value": "<span class=\"status-pill\" style=\"border-color: #dce7c6; background-color: #f1f5e8; color: #585c4f;\">New</span>",
        "rich_value": {
          "id": 934242751,
          "name": "New",
          "color": "727272"
        }
      }
    ]
  ]
}

GET /api/v1/bookmarks/custom_pivots/:report_id

Pivot view

The pivot view is similar to the list view, but is more referential. There are six attributes in the JSON response: top_level_columns, columns, top_level_rows, rows, cells, field_definitions.

The columns attribute is an object where the keys are the unique id of column within the report. Rows and field_definitions have the same referential treamtment. The attributes are the id in order to ease lookup.

Since pivot reports are referential in nature and can be nested, there are top_level_rows and top_level_columns. This allows you to easily iterate throught the nested structure. Each column and row object contain a reference to their parent object (parent_ref). They also contain a set of references to their direct children (child_refs). They also, along with cells, contain a reference to their field definition (via field_definitions_refs).

The field definitions object is an object that acts as a map to look up the definitions of the fields used within the report. Similarly to the list view, these contain table, field, and title attributes.

The cells attribute is a set of nested arrays. The lowest level array is a list of the fields for a specific value. Each element in this set has a row_ref, column_ref, and field_definition_ref which can be used to lookup which column and row this value is representing. Just like the list view, there are multiple formats of the value:

  • id: The unique id of the object
  • plain_value: A string representation of the object
  • html_value: An HTML representation of the object. This is the same HTML that is used in Aha! to represent the value in our reports.
  • rich_value: If the object is a reference to another object (such as a tag), the, this will be the object representation.

There is no pagination with pivot report.

Parameters

Name Description
report_id The ID of the saved view in Aha! to report on.
view The type of API response you want. This should either be list or pivot. It defaults to list

GET /api/v1/bookmarks/custom_pivots/801750833?view=pivot

Example CURL command

curl -g "https://secure.aha.io/api/v1/bookmarks/custom_pivots/801750833?view=pivot" -X GET \
	-H "Authorization: Bearer 15b60d42d4bc417284a246ced6877b0bf13fb4aca415f7b55f7006bc3694a8ab" \
	-H "X-Aha-Account: account1" \
	-H "Content-Type: application/json" \
	-H "Accept: application/json"

Request

Authorization: Bearer 15b60d42d4bc417284a246ced6877b0bf13fb4aca415f7b55f7006bc3694a8ab
X-Aha-Account: account1
Content-Type: application/json
Accept: application/json

Response

Status: 200 OK
  Content-Type: application/json; charset=utf-8
{
  "top_level_columns": [
    1
  ],
  "columns": {
    "1": {
      "ref": 1,
      "parent_ref": null,
      "child_refs": [

      ],
      "id": 131414752,
      "plain_value": "Project 1",
      "html_value": "<a data-drawer-url=\"/projects/PRJ1\" href=\"/projects/PRJ1\">Project 1</a>",
      "rich_value": "Project 1",
      "field_definition_ref": 3
    }
  },
  "top_level_rows": [
    2
  ],
  "rows": {
    "2": {
      "ref": 2,
      "parent_ref": null,
      "child_refs": [

      ],
      "id": 278327321,
      "plain_value": "Release 1",
      "html_value": "<a data-drawer-url=\"/releases/PRJ1-R-1\" href=\"/releases/PRJ1-R-1\">Release 1</a>",
      "rich_value": "Release 1",
      "field_definition_ref": 4
    }
  },
  "cells": [
    [
      [
        [
          {
            "id": 1007868956,
            "plain_value": "PRJ1-1",
            "html_value": "<a data-drawer-url=\"/features/PRJ1-1\" href=\"/features/PRJ1-1\">PRJ1-1</a>",
            "rich_value": "PRJ1-1",
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 5
          },
          {
            "id": 1007868956,
            "plain_value": "Feature 1",
            "html_value": "<a data-drawer-url=\"/features/PRJ1-1\" href=\"/features/PRJ1-1\">Feature 1</a>",
            "rich_value": "Feature 1",
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 6
          },
          {
            "id": 1007868956,
            "plain_value": "New",
            "html_value": "<span class=\"status-pill\" style=\"border-color: #dce7c6; background-color: #f1f5e8; color: #585c4f;\">New</span>",
            "rich_value": {
              "id": 934242751,
              "name": "New",
              "color": "727272"
            },
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 7
          }
        ],
        [
          {
            "id": 622562724,
            "plain_value": "PRJ1-2",
            "html_value": "<a data-drawer-url=\"/features/PRJ1-2\" href=\"/features/PRJ1-2\">PRJ1-2</a>",
            "rich_value": "PRJ1-2",
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 5
          },
          {
            "id": 622562724,
            "plain_value": "Another Feature",
            "html_value": "<a data-drawer-url=\"/features/PRJ1-2\" href=\"/features/PRJ1-2\">Another Feature</a>",
            "rich_value": "Another Feature",
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 6
          },
          {
            "id": 622562724,
            "plain_value": "Designed",
            "html_value": "<span class=\"status-pill\" style=\"border-color: #ecdd8f; background-color: #f7f1d2; color: #5e5839;\">Designed</span>",
            "rich_value": {
              "id": 962984386,
              "name": "Designed",
              "color": "727272"
            },
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 7
          }
        ],
        [
          {
            "id": 303873333,
            "plain_value": "PRJ1-3",
            "html_value": "<a data-drawer-url=\"/features/PRJ1-3\" href=\"/features/PRJ1-3\">PRJ1-3</a>",
            "rich_value": "PRJ1-3",
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 5
          },
          {
            "id": 303873333,
            "plain_value": "Another Third Feature",
            "html_value": "<a data-drawer-url=\"/features/PRJ1-3\" href=\"/features/PRJ1-3\">Another Third Feature</a>",
            "rich_value": "Another Third Feature",
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 6
          },
          {
            "id": 303873333,
            "plain_value": "Designed",
            "html_value": "<span class=\"status-pill\" style=\"border-color: #ecdd8f; background-color: #f7f1d2; color: #5e5839;\">Designed</span>",
            "rich_value": {
              "id": 962984386,
              "name": "Designed",
              "color": "727272"
            },
            "row_ref": 2,
            "column_ref": 1,
            "field_definition_ref": 7
          }
        ]
      ]
    ]
  ],
  "field_definitions": {
    "3": {
      "ref": 3,
      "table": "projects",
      "field": "name",
      "title": "Product name"
    },
    "4": {
      "ref": 4,
      "table": "releases",
      "field": "name",
      "title": "Release name"
    },
    "5": {
      "ref": 5,
      "table": "features",
      "field": "reference_num",
      "title": "Feature reference #"
    },
    "6": {
      "ref": 6,
      "table": "features",
      "field": "name",
      "title": "Feature name"
    },
    "7": {
      "ref": 7,
      "table": "features",
      "field": "status",
      "title": "Feature status"
    }
  }
}

Overview

API Resources