Hi Marco,
You need to POST or PATCH the native view in order to change a selected element on the title axis. This basically saves the view back to the TM1 server. I'm pretty sure here is no way to adjust the selected element on the title axis of a native view without persisting the native view back to TM1. Here is an example of writing a native view back to TM1 with selected elements on the title axis.
140051588200192 [49] DEBUG 2021-09-10 09:31:36.724 TM1.HttpRequest POST /api/v1/Cubes('plan_BudgetPlan')/Views
140051588200192 [49] DEBUG 2021-09-10 09:31:37.675 TM1.HttpRequestBody {
"@odata.type": "#ibm.tm1.api.v1.NativeView",
"Name": "View1",
"Rows": [
{
"Subset": {
"Hierarchy@odata.bind": "Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')",
"Elements@odata.bind": [
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('41101')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('42201')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Revenue')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Other Expenses')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('61060')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('65022')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('65035')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Adv & Marketing')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Operating Expense')",
"Dimensions('plan_chart_of_accounts')/Hierarchies('plan_chart_of_accounts')/Elements('Net Operating Income')"
]
}
}
],
"Columns": [
{
"Subset@odata.bind": "Dimensions('plan_time')/Hierarchies('plan_time')/Subsets('plan_time_2004_qtrs_and_month')"
}
],
"Titles": [
{
"Selected@odata.bind": "Dimensions('plan_department')/Hierarchies('plan_department')/Elements('105')",
"Subset@odata.bind": "Dimensions('plan_department')/Hierarchies('plan_department')/Subsets('All Departments')"
},
{
"Selected@odata.bind": "Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Elements('10110')",
"Subset@odata.bind": "Dimensions('plan_business_unit')/Hierarchies('plan_business_unit')/Subsets('All Business Units')"
},
{
"Selected@odata.bind": "Dimensions('plan_exchange_rates')/Hierarchies('plan_exchange_rates')/Elements('local')",
"Subset@odata.bind": "Dimensions('plan_exchange_rates')/Hierarchies('plan_exchange_rates')/Subsets('local exchange rate')"
},
{
"Selected@odata.bind": "Dimensions('plan_source')/Hierarchies('plan_source')/Elements('input')",
"Subset@odata.bind": "Dimensions('plan_source')/Hierarchies('plan_source')/Subsets('input')"
},
{
"Selected@odata.bind": "Dimensions('plan_version')/Hierarchies('plan_version')/Elements('FY 2004 Forecast')",
"Subset@odata.bind": "Dimensions('plan_version')/Hierarchies('plan_version')/Subsets('All Versions')"
}
],
"SuppressEmptyColumns": false,
"SuppressEmptyRows": false,
"FormatString": "0.#########"
}
Consider translating the native view to MDX in your client. You can execute MDX without the need for the view object in TM1.
------------------------------
Stuart King
IBM Planning Analytics Offering Manager
------------------------------
Original Message:
Sent: Thu September 09, 2021 03:29 PM
From: Marco Spyker
Subject: REST API (Native) View - manipulating title selection
REST API (Native) View - manipulating title selection
Hi everyone!
I am struggeling with the TM1 REST API, especially when I try to "set" a new title element, to retrieve data from a view.
"The whole story":
I have created a view, belonging to cube and I am successfull in getting the structure and the cells using a call like:
POST https://localhost:8881/api/v1/Cubes('Cube_1')/Views('View_1')/tm1.Execute?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells
But what I like to do now is, I want to change the selection of a title element, so that I get appropriate values depending on that selection.
Same way, like in the architect, where you can select new values in a title an click on re-calculate.
Is this possible using the REST API?
What I learned from the metadata: "tm1.Execute" is a post request, which accepts a payload, where parameters like "View", "Titles", "SuppressEmptyColumns", "SuppressEmptyRows" can be handed over.
<Action Name="Execute" IsBound="true">
<Annotation Term="Core.Description">
<String>Runs a view against the model.</String>
</Annotation>
[...]
<Parameter Name="Titles" Type="Collection(tm1.Element)">
<Annotation Term="Core.Description">
<String>The values of the titles in the title axis that you are slicing the view against.</String>
</Annotation>
<Annotation Term="Core.LongDescription">
<String>You must specify all titles, even though you may only want change one.</String>
</Annotation>
</Parameter>
[...]
<ReturnType Type="tm1.Cellset" Nullable="false"/>
</Action>
My assumption was, that I could use the "Titles" parameter, to do the selection change. Looking again into the description of the metadata it says, that "all titles must be specified, even though you may only want change one" and the parameter has the type "tm1.Element".
And here is the point, where I am stuck:
I was not able the get the right payload format, to handle over the titles. The two main results of playing around with the format is:
- CellValues are always the initial ones
- Error code 278 - "Could not find entity reference in payload."
I did my test with the sample cube "SData" using the "Region" view - but unfortunately I was not lucky, too.
Can someone give me a hint, what I am missing?
Here is one example payload trying to change actuals to budget:
{ "Titles": [
{
"@odata.etag": "W/\"2fd97ea3ed7fc6f6626cdec0e0ef5ec423a381e8\"",
"Name": "Budget",
"UniqueName": "[actvsbud].[actvsbud].[Budget]",
"Type": "Numeric",
"Level": 0,
"Index": 2,
"Attributes": {
"Caption": "Budget",
"实际值VS预算": "预算",
"Presactivo": "Presupuesto",
"bilattivo": "Bilancio",
"実数と予算": "予算",
"datenart": "Plan",
"réelebud": "Budget"
}
},{
"@odata.etag": "W/\"a6de0dd08535c6b1f043753050597d85c76136a7\"",
"Name": "S Series 1.8 L Sedan",
"UniqueName": "[model].[model].[S Series 1.8 L Sedan]",
"Type": "Numeric",
"Level": 0,
"Index": 13,
"Attributes": {
"Caption": "S Series 1.8 L Sedan",
"型号": "S 系列 1.8 L 轿车",
"modelo": "Sedán Serie S 1.8 L",
"modelli": "Serie S Berlina 1.8 L",
"モデル": "S シリーズ 1.8 L セダン",
"modell": "S Klasse 1.8 L Limousine",
"modèle": "Berline 1,8L Série S"
}
},{
"@odata.etag": "W/\"2f1348bc71c1dec2f273a77d4a8b48b664024d81\"",
"Name": "Sales",
"UniqueName": "[account1].[account1].[Sales]",
"Type": "Numeric",
"Level": 0,
"Index": 4,
"Attributes": {
"Caption": "Sales",
"帐目1": "销量",
"cuenta1": "Ventas",
"conto1": "Vendite",
"アカウント1": "売上",
"wertart1": "Umsatz",
"compte1": "Unités"
}
}
]}
Any help is welcome! :-)
Thank you very much in advance,
M. Spyker
------------------------------
Marco Spyker
------------------------------
#PlanningAnalyticswithWatson