TM1 Forum

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

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

@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

Hello,

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

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

Can you use single quotes to represent string in MDX inside double quotes?