Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only

  • 1.  REST API - Update a single cell value

    Posted 10/06/21 10:22 AM
    Hello,

    Do you know if there a simpler way to update a cell with REST API then the one of the guide?
    It seems a bit complicated to insert a single value, I was exepecting something more like a cellput in TI.


    Update a single cell value
    You can update a single cell value by using the PATCH operation.
    To update the value of the cell, determine the value of the ID property of the cellset that the cell belongs
    to, and determine whether it is a value that can be updated.
    POST /api/v1/Cubes('plan_Budgetplan')/Views('Budget input detailed')/
    tm1.Execute?$expand=Cells($select=Ordinal,FormattedValue,Consolidated)
    134 IBM Planning Analytics TM1 REST API
    The request in the previous example returns the cells in the cellset as shown below.
    {
    "@odata.context": "../../$metadata#Cellsets(Cells(Ordinal,FormattedValue,Consolidated))/
    $entity",
    "ID": "DWjRKwMCAIAFAAAg",
    "Cells": [
    {
    "Ordinal": 0,
    "FormattedValue": "938,285",
    "Consolidated": true
    },
    {
    "Ordinal": 1,
    "FormattedValue": "315,513",
    "Consolidated": false
    },
    {
    "Ordinal": 2,
    "FormattedValue": "311,041",
    "Consolidated": false
    },
    ...
    The Consolidated property identifies if the value is a result of an aggregation or other operation based
    on other cell values. If the property is true, you cannot change the value unless Updateable=true. If a
    consolidated value is updateable, you can update the value with a spreading command.
    If the property is set to Consolidated=false, use a PATCH operation to update the cell, specifying the
    ordinal and the new value.
    PATCH /api/v1/Cellsets('DWjRKwMCAIAFAAAg')/Cells
    [
    {
    "Ordinal": 1,
    "Value": 315999
    }
    ]
    To verify that the cell is updated properly, repeat the GET operation.


    Regards



    ------------------------------
    Joseph GRANDCHAMP
    ------------------------------

    #PlanningAnalyticswithWatson


  • 2.  RE: REST API - Update a single cell value

    Posted 10/07/21 01:40 AM
    There is a much easier way to update 1 value. The above method works well if you are presenting a grid to users and you want to patch back updates to groups of cells. 

    In this case you would use the tm1.update function

    POST  Cubes('sys_Config')/tm1.Update​

    PAYLOAD:
{ 
   "Value":"New Value",
   "Cells":[ 
      { 
         "Tuple@odata.bind":[ 
            "Dimensions('Dim Name')/Hierarchies('Hierarchy Name')/Elements('Element Name')",
            "Dimensions('Dim Name')/Hierarchies('Hierarchy Name')/Elements('Element Name')",
            "Dimensions('Dim Name')/Hierarchies('Hierarchy Name')/Elements('Element Name')"
         ]
      }
   ]​


    ------------------------------
    Ryan Clapp
    ------------------------------



  • 3.  RE: REST API - Update a single cell value

    Posted 10/07/21 08:11 AM
    This also works

    POST

    Cubes('plan_BudgetPlan')/tm1.UpdateCells

    PAYLOAD

    {
    "Updates": [
    {
    "Tuple@odata.bind": [
    "Dimensions('plan_department')/Hierarchies('plan_department')/Elements('105')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('41101')",
    "Dimensions('plan_exchange_rates')/Hierarchies('plan_exchange_rates')/Elements('local')",
    "Dimensions('plan_source')/Hierarchies('plan_source')/Elements('input')",
    "Dimensions('plan_version')/Hierarchies('plan_version')/Elements('FY 2004 Budget')",
    "Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Elements('10110')",
    "Dimensions('plan_time')/Hierarchies('plan_time')/Elements('Jan-2005')"
    ],
    "Value": "1"
    },
    {
    "Tuple@odata.bind": [
    "Dimensions('plan_department')/Hierarchies('plan_department')/Elements('105')",
    "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('41101')",
    "Dimensions('plan_exchange_rates')/Hierarchies('plan_exchange_rates')/Elements('local')",
    "Dimensions('plan_source')/Hierarchies('plan_source')/Elements('input')",
    "Dimensions('plan_version')/Hierarchies('plan_version')/Elements('FY 2004 Budget')",
    "Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Elements('10110')",
    "Dimensions('plan_time')/Hierarchies('plan_time')/Elements('Feb-2005')"
    ],
    "Value": "2"
    }
    ]
    }

    ------------------------------
    Stuart King
    IBM Planning Analytics Offering Manager
    ------------------------------

    Original Message


  • 4.  RE: REST API - Update a single cell value

    Posted 10/07/21 08:22 AM
    Thank you very much.
    It is working fine :)

    ------------------------------
    Joseph GRANDCHAMP
    ------------------------------

    Original Message


  • 5.  RE: REST API - Update a single cell value

    Posted 10/07/21 07:29 PM
    Edited by System Admin 01/20/23 04:40 PM
    You can use a POST with /tm1.Update and in the body of the call specify the cell tuples like so

    [
  {
    "Cells": [
      {
        "Tuple@odata.bind": [
          "Dimensions('dim1name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim2name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim3name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim4name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim5name')/Hierarchies('hiername')/Elements('elename')"
        ]
      }
    ]
    , "Value":"37"
  },
    "Cells": [
      {
        "Tuple@odata.bind": [
          "Dimensions('dim1name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim2name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim3name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim4name')/Hierarchies('hiername')/Elements('elename')"
          "Dimensions('dim5name')/Hierarchies('hiername')/Elements('elename')"
        ]
      }
    ]
    , "Value":"73"
  }
]​

    UPDATE: sorry didn't see this had already been recommended.
    ------------------------------
    Craig Sawers
    ------------------------------



Related Content