Hi
I am fairly new to MDX, and am trying to write a query to get measures (columns) of the leaf level (rows) of an organization hierarchy.
My query looks something like this:
SELECT { " _
[Measures].[Measures].[sales],
[Measures].[Measures].[inventory],
[Measures].[Measures].[cash]
} ON COLUMNS,
{ "
TM1FILTERBYLEVEL( {TM1SUBSETALL( [Organizations].[Organizations])}, 0)}
ON ROWS
FROM [MyCube] WHERE ([VERSIONS].[V1])
For a small set of leaf, something likes the following. It works.
[Organizations].[Organizations].[Dept1^level1^level2^level3^level4].CHILDREN
But when I try to get all the leaf of the whole organization, using the following, it does not work.
TM1FILTERBYLEVEL( {TM1SUBSETALL( [Organizations].[Organizations])}, 0)
There is no error message. Only the header of the table is returned. In Excel, when I convert the leaf to a static set, it returns data. But there are a bit over 10k leaf, and there can be more or less over time (not practical to spell each one out and maintain the list). The ideal case would be dynamically to get all the leaf.
Is it the way I wrote my query, so it does not work? Any idea how to resolve it?
Thanks