Subset Name vs Consolidation Name in MDX Dynamic Subset

[rtanenbaum]

I am creating a dynamic subset using MDX to list the Level 0 (leaf nodes) of a different subset.
However, it is confusing the source subset name with a similar consolidation name.

The subset I want to use is called "BillingsReports" with no spaces.
There is also a consolidation called "Billings Reports" with a space.

The MDX Query references "BillingsReports" subset with no space like this.

Code: Select all

TM1SORT(
        {TM1FILTERBYLEVEL( 
                 {TM1DRILLDOWNMEMBER( {[clients].[BillingsReports]} , ALL, RECURSIVE }
        , 0)}
, ASC)

However the list being shown is the "Billings Reports" consolidation.

I could fix it by changing the name of the subset from "BillingsReports" to "Billings_Reports", but I wondered if anyone has a clue about why this is happening.

IBM Cognos TM1 9.5

[tomok]

TM1 ignores spaces in object names so to TM1, they're both the same. You need to come up with a different name for your subset.

[rtanenbaum]

Thank you. That makes sense.

[lotsaram]

TM1 is a bit funny with subsets they are both lists of elements and pseudo-consolidations. MDX was developed for SSAS which has no concept of a subset in the TM1 sense and so TM1's treatment of subsets in MDX is also a bit unusual. {[dim].[subset]} returns a the member set as long as no element of the same name exists in which case it returns a set of just the specified member. As in everything TM1 the MDX is both case and space insensitive. Like Tomok said already, if you want to specify the subset then the name must be unique (in the TM1 sense) and not the same as an element in the dimension.

[rtanenbaum]

Thank you. This is useful information.