Create a consolidation

Available since 2021 Update 2

tip

You can try out this workflow at REST API Playground.

Learn more about MicroStrategy REST API Playground here.

This workflow sample demonstrates how to create a new consolidation object through the Modeling service.

1. Create a changeset.
2. Create a new consolidation.
3. Commit a changeset.
4. Delete a changeset.

More Example: Create a consolidation with two elements.

You want to create a new consolidation with the expression "({Customer Age=20 AND Customer=Aaby:Alen} + {Customer City=Addison})" under the Public Objects folder in the MicroStrategy Tutorial project. The folder object ID is 98FE182C2A10427EACE0CD30B6768258. The project ID is 091B51154B22036C53CFBB9958B2E8FC.

info

Get the authorization token needed to execute the request with POST /api/auth/login.
Get the project ID from GET /api/projects.

tip

Changesets are used in this workflow. For information on how to create and use changesets, see Changesets.

Create a new consolidation

Use POST /api/model/consolidation

The type of elements inside the consolidation must be "calculation". An example for creating a new consolidation with two elements is described below.

Sample Request Header:

"accept": "application/json"
"X-MSTR-AuthToken": "pisu5dkkutqfblaamdomgr00ch"
"X-MSTR-MS-Changeset": "0E9F01172ECF4BA2BB510F7B9FB4F6E8"

Sample Request Body:

tip

For more details on setting format properties, please refer to Format samples. The format for consolidations is the same as derived elements.

{
  "information": {
    "destinationFolderId": "98FE182C2A10427EACE0CD30B6768258",
    "name": "Create Consolidation"
  },
  "elements": [
    {
      "name": "Example",
      "type": "calculation",
      "format": {
        "header": [],
        "values": []
      },
      "expression": {
        "tree": {
          "function": "plus",
          "children": [
            {
              "type": "elements_object",
              "elements": [
                {
                  "display": "20",
                  "elementId": "h20",
                  "attribute": {
                    "objectId": "6E069E4C11D3E4E41000E887EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer Age"
                  }
                },
                {
                  "display": "Aaby:Alen",
                  "elementId": "h7796",
                  "attribute": {
                    "objectId": "8D679D3C11D3E4981000E787EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer"
                  }
                }
              ]
            },
            {
              "type": "elements_object",
              "elements": [
                {
                  "display": "Addison",
                  "elementId": "h1",
                  "attribute": {
                    "objectId": "8D679D3A11D3E4981000E787EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer City"
                  }
                }
              ]
            }
          ],
          "type": "operator"
        }
      }
    }
  ],
  "subtotal": "disable"
}

Sample Curl:

curl -X POST "https://demo.microstrategy.com/MicroStrategyLibrary/api/model/consolidations" -H "accept: application/json" -H "X-MSTR-AuthToken: 9rlq0e1dgg3rp237hiddruqcd0" -H "X-MSTR-MS-Changeset: EE48CD8879C44A03B18660AE7AFC8F68" -H "Content-Type: application/json" -d "{\"information\":{\"destinationFolderId\":\"98FE182C2A10427EACE0CD30B6768258\",\"name\":\"Create Consolidation\"},\"elements\":[{\"id\":\"6A4A4FAF4A9D868F53754FB3417A3CC1\",\"name\":\"Example\",\"type\":\"calculation\",\"format\":{\"header\":[],\"values\":[]},\"expression\":{\"text\":\"20, Aaby:Alen + Addison\",\"tree\":{\"function\":\"plus\",\"children\":[{\"type\":\"elements_object\",\"elements\":[{\"display\":\"20\",\"elementId\":\"h20\",\"attribute\":{\"objectId\":\"6E069E4C11D3E4E41000E887EC6DE8A4\",\"subType\":\"attribute\",\"name\":\"Customer Age\"}},{\"display\":\"Aaby:Alen\",\"elementId\":\"h7796\",\"attribute\":{\"objectId\":\"8D679D3C11D3E4981000E787EC6DE8A4\",\"subType\":\"attribute\",\"name\":\"Customer\"}}]},{\"type\":\"elements_object\",\"elements\":[{\"display\":\"Addison\",\"elementId\":\"h1\",\"attribute\":{\"objectId\":\"8D679D3A11D3E4981000E787EC6DE8A4\",\"subType\":\"attribute\",\"name\":\"Customer City\"}}]}],\"type\":\"operator\"}}}],\"subtotal\":\"disable\"}"

Sample Response Body:

You can view the new consolidation's definition in the body of the response.

{
  "information": {
    "versionId": "9E355E1F98C74D47B7EA30C4160FD625",
    "primaryLocale": "en-US",
    "objectId": "BE0FAD624A1A48BC9EFCE80F23C2D6C3",
    "subType": "consolidation",
    "name": "Create Consolidation",
    "destinationFolderId": "98FE182C2A10427EACE0CD30B6768258"
  },
  "elements": [
    {
      "id": "9360AEF0C38341E1ADA498692C4257C8",
      "name": "Example",
      "type": "calculation",
      "format": {
        "header": [],
        "values": []
      },
      "expression": {
        "text": "20, Aaby:Alen + Addison",
        "tree": {
          "function": "plus",
          "children": [
            {
              "type": "elements_object",
              "elements": [
                {
                  "display": "20",
                  "elementId": "h20",
                  "attribute": {
                    "objectId": "6E069E4C11D3E4E41000E887EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer Age"
                  }
                },
                {
                  "display": "Aaby:Alen",
                  "elementId": "h7796",
                  "attribute": {
                    "objectId": "8D679D3C11D3E4981000E787EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer"
                  }
                }
              ]
            },
            {
              "type": "elements_object",
              "elements": [
                {
                  "display": "Addison",
                  "elementId": "h1",
                  "attribute": {
                    "objectId": "8D679D3A11D3E4981000E787EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer City"
                  }
                }
              ]
            }
          ],
          "type": "operator"
        }
      }
    }
  ],
  "subtotal": "disable"
}

Response Code: 201 (A new consolidation is created successfully in the changeset.)

Example: Create a consolidation with two elements

In the above example, the consolidation has only one element. However, it can have multiple elements, and an element can be referred by other elements.

In this example, you want to create a consolidation with two elements. The expression of element_a is ({Customer Age=20 AND Customer=Aaby:Alen} + {Customer City=Addison}) and the expression of element_b is element_a + 1.

Sample Response Body:

{
  "information": {
    "destinationFolderId": "98FE182C2A10427EACE0CD30B6768258",
    "name": "Create Consolidation"
  },
  "elements": [
    {
      "name": "element_a",
      "type": "calculation",
      "format": {
        "header": [],
        "values": []
      },
      "expression": {
        "tree": {
          "function": "plus",
          "children": [
            {
              "type": "elements_object",
              "elements": [
                {
                  "display": "20",
                  "elementId": "h20",
                  "attribute": {
                    "objectId": "6E069E4C11D3E4E41000E887EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer Age"
                  }
                },
                {
                  "display": "Aaby:Alen",
                  "elementId": "h7796",
                  "attribute": {
                    "objectId": "8D679D3C11D3E4981000E787EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer"
                  }
                }
              ]
            },
            {
              "type": "elements_object",
              "elements": [
                {
                  "display": "Addison",
                  "elementId": "h1",
                  "attribute": {
                    "objectId": "8D679D3A11D3E4981000E787EC6DE8A4",
                    "subType": "attribute",
                    "name": "Customer City"
                  }
                }
              ]
            }
          ],
          "type": "operator"
        }
      }
    },
    {
      "name": "element_b",
      "type": "calculation",
      "format": {
        "header": [],
        "values": []
      },
      "expression": {
        "tree": {
          "function": "plus",
          "children": [
            {
              "type": "object_reference",
              "target": {
                "name": "element_a"
              },
              "isIndependent": 0
            },
            {
              "type": "constant",
              "variant": {
                "type": "double",
                "value": "1.0"
              }
            }
          ],
          "type": "operator"
        }
      }
    }
  ],
  "subtotal": "disable"
}