Tm1py: Pass parameters to MDM query

[user1493]

Hi,

I'm successfull at exporting the data using the following MDX query. But I would like to send in parameters to this MDX query. Planning to pass the dimension element's name from some other UI and use it in this MDX query for exporting data from cube.

mdx = "SELECT {[Period].[M05]} on ROWS, "\ "{[Products].[Products]} on COLUMNS " \ "FROM [Forecast] " \ "WHERE ([Account].[Net Sales],[DataType].[ClientInput], "\ "[Scenario].[2018AC])"

I'm expecting something like:

acc_ele = 'Net Sales'
mdx = "SELECT {[Period].[M05]} on ROWS, "\ "{[Products].[Products]} on COLUMNS " \ "FROM [Forecast] " \ "WHERE ([Account].[acc_ele],[DataType].[ClientInput], "\ "[Scenario].[2018AC]) "
Here acc_ele would be a variable. Is this possible? If yes, please let me know the systax.

Also, https://github.com/cubewise-code/tm1py/issues/95 in this post, you mentioned two new functions. Can you please provide sample codes for them.

Appreciate your support!

[MariusWirtz]

Hi @vino1493,

MDX Queries do not have parameters in TM1. They are static.
You can reach a certain degree of flexibility, by referencing existing subsets or using the StrToMember function.
You could, for instance, write the parameter value into a cube (before executing the query) and then use the StrToMember function (in the query) to contruct a member from the parameter value in the cube.
https://www.tm1forum.com/viewtopic.php?t=2894

Regarding the two new functions, they are not actually included yet.
We will provide samples once this feature is done and merged into the master branch.

Cheers,

Marius

[pbuncik]

I think the easiest way how to address what you want to achieve is to use builtin Python format function str.format() on the MDX query string itself. Please note you will have to __escape__ } and { characters by doubling them to make sure format() will work properly. You may index format {} placeholders to make query more readable - see in below example placeholder {0} which refers to first argument of format function.

Example:

acc_ele = 'Net Sales'
mdx = """
  SELECT
    {{[Period].[M05]}} on ROWS,
    {{[Products].[Products]}} on COLUMNS
  FROM [Forecast] 
  WHERE 
    ([Account].[{0}],[DataType].[ClientInput],
    [Scenario].[2018AC])
""".format(acc_ele)

[user1493]

Hi pbuncik,

Perfect!! At first I missed to include the double escape { }
The code works correctly, thank you very much!

I hope this post would help many TM1 developers.