TM1ELLIST with MDX

Post Reply
dharav9
Posts: 72
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

TM1ELLIST with MDX

Post by dharav9 »

Hi, All

I am good with establishing name range for TM1ELLIST function with subset.

Ref: https://www.ibm.com/support/knowledgece ... llist.html

I faced challenge while establishing name range for the TM1ELLIST function through MDX.

my syntax : ( I MUST BE DOING MISTAKE WITH EXCEL SYNTAX CONSTRUCTION)

FYI : MDX syntax works perfectly in the subset expression window

construction1:

=TM1ELLIST("NewWorld:ConversionMap_Obj",,,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) = Sheet1!$B$3 and [ConversionMap].([ConversionMap_Msr].[NewDivision]) =Sheet1!$B$5 )}",,)

Construction2:
=TM1ELLIST("NewWorld:ConversionMap_Obj","Default","","",0,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) = "30003" and [ConversionMap].([ConversionMap_Msr].[NewDivision]) = "300081")}","","")


Can anyone through the light on the issue?

Thank You

Dharav
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1ELLIST with MDX

Post by Wim Gielis »

First off, Sheet1!$B$3 needs to be taken I’m out of the literal string enclosed by double quotes.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
dharav9
Posts: 72
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: TM1ELLIST with MDX

Post by dharav9 »

@wim:

Removed Double Quotes:

Instead of reference to Sheet1!$b3$ , i put the value 30003

=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) =30003 and [ConversionMap].([ConversionMap_Msr].[NewDivision]) =300081)}",,)

I am still not able to get it right. Could you please help me to understand the concept to structure the formula?

Thank You

Dharav
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1ELLIST with MDX

Post by Wim Gielis »

Hello,

I haven’t used TM1ELLIST yet so I cannot help you in detail with this one.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
dharav9
Posts: 72
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

Re: TM1ELLIST with MDX

Post by dharav9 »

Hi, All & Wim

i figured it out.

Cause was the construction of the MDX for the worksheet.

While referencing the cell from worksheet to MDX statement, we need to utilize

"&CHAR(34)&$b$5&CHAR(34)&" instead of just referencing cell as $b$5.

=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) ="&CHAR(34)&$b$5&CHAR(34)&")}")

Although, following mdx statement is not working

=TM1ELLIST("NewWorld:ConversionMap_Obj","Centers",,,,"{FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL( [ConversionMap_Obj] )}, 0)},[ConversionMap].([ConversionMap_Msr].[NewDepartment]) ="&CHAR(34)&$b$5&CHAR(34)&" and [ConversionMap].([ConversionMap_Msr].[NewDivision]) ="&CHAR(34)&$b$8&CHAR(34)&")}")

do we need to use any character with "and" in the MDX for worksheet?

Thank You

Dharav
User avatar
macsir
MVP
Posts: 782
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1
Version: PAL 2.0.9
Excel Version: Office 365
Contact:

Re: TM1ELLIST with MDX

Post by macsir »

Can you use single quotes to represent string in MDX inside double quotes?
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/
Post Reply