IMO the documentation on TM1ELLIST should be updated to reflect Excel's dynamic array behavior.
TM1ELLISTIn fact, in Excel (connected through PAfE) you can just enter in a cell like A1:
=TM1ELLIST("CXMD:}Clients",,,,,"Head( TM1SubsetAll( [}Clients] ), 10 )")
And the result will spill down in the cell and 9 cells below - unless you have less than 10 users in the model.
Change the servername as well.
No need for array-entering the formula with Ctrl-Shift-Enter to receive the curly braces left and right of the formula.
You now enter the world of dynamic arrays in Excel.
For instance, in B1 enter:
=SORT( A1#, , -1 )
to sort the list (however many rows, 10 or less) descending.
If we could now also have the dynamic array reference (hash symbol) work in TM1 formulas like DBRW, or SUBNM or any other, that would be the best !
For those using VBA to generate the formula, consider:
Sub create_ellist()
Dim vMDX As String
vMDX = "TM1FilterByLevel( TM1SubsetAll( [Year] ), 0 )"
'creating a TM1ELLIST function as a dynamic array
Range("A1").Formula2 = "=TM1ELLIST(""servername:Year"",,,,,""" & vMDX & """)"
Range("B1").Formula2 = "=""Y"" & A1#"
End Sub
------------------------------
Wim Gielis
------------------------------
#PlanningAnalyticswithWatson