TM1 Forum

Posted: **Thu Apr 27, 2023 2:04 pm**

In CA, a prompt can be utilized to pick a dimensional element and then use that selection in a crosstab with a data item expression like:

[tm1_cube].[Products].[Products]->?p_tree_selection?

We are in dire need of equivalent functionality but with specifying a subset to use rather than a dimension selection. We want to be able to specify which subsets to utilize in a job running a report. We can then maintain a single report but utilize for multiple needs. The subsets in question are not exchangeable with say the children of a dimensional selection as the subset elements have different element order and customized depth of certain elements.

As I don't believe tree or value prompts can be used to pick from subsets, I was thinking of perhaps using a text box prompt to specify a subset reference like:

[tm1_cube].[Products].[Products].[Product_Subset1]->?p_textbox_value?

However, I cannot find a way to get CA to typecast this into usable set.

Has anyone had any luck dynamically swapping in subset references?

Thanks,
-- John

Posted: **Fri Apr 28, 2023 8:58 pm**

Yes, using the REST API and MDX queries. Only downside is it's probably a steep learning curve for you if you've never used APIs before and interacted with TM1 in this way before. My opinion is using the REST API (or planning analytics workshop API) to interact with TM1 by sending and receiving JSON payloads is the best way to go in 2023.

e.g. The MDX statement is sent as a string (like SQL) via JSON payload, so you can adapt it to whatever you want it to be, and as long as it's valid MDX you'll get your cellset back from TM1.

Code: Select all

MDX: SELECT {TM1SubsetToSet([DIMENSION1].[HIERARCHY],"YOURSUBSET1","public")} ON 0, {[DIMENSION2].[HIERARCHY]."YOURSUBSET2","public"} ON 1 FROM [YOURCUBE])

Post to TM1 server http://yourserver:yourport/api/v1/ExecuteMDX?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells($select=Ordinal,Value)

JSON Payload {"MDX":"YOUR MDX STATEMENT IN PLAINTEXT"}

Read how to parse the return JSON payload representing the cellset: https://www.ibm.com/docs/en/planning-an ... in_cellset

Or use packages:

Python TM1 API interaction: https://pypi.org/project/TM1py/

.Net C# TM1 API interaction: https://www.nuget.org/packages/AndromedaTM1Sharp

Posted: **Thu May 04, 2023 6:35 pm**

Thanks for your reply William.

I am familiar with the REST API and executing MDX queries using Python and TM1py. However, I'm not sure how the API can be leveraged from within a Cognos Analytics prompt or report?

We have existing CA reports that make use of TM1 subsets. CA allows for report elements to be driven by prompts on dimensional selections. However, there appears to be no method of prompting from a list of TM1 subsets. Given this and in lieu a tree/list prompt, I was hoping to use a simple text box prompt whereby a subset reference (ex "[cube].[dim].[dim].[subset]") could be typed in as a prompt string variable. The problem I've having is getting CA to recognize (cast) that string as a dimensional subset reference so it can used in a crosstab.

Below is a 10 yo post from ykud with some verbiage on using CA macros to dynamically define MUN references that I'm planning on digging into next. My guess, however, is that definitions are limited dimension elements and not subsets.

https://www.ykud.com/blog/cognos/cognos ... g-on-tm1/

TM1 Forum

Reference PA Subset Dynamically in Cognos Analytics

Reference PA Subset Dynamically in Cognos Analytics

Re: Reference PA Subset Dynamically in Cognos Analytics

Re: Reference PA Subset Dynamically in Cognos Analytics