PAW: subset MDX works in one cube view, not in another

Post Reply
User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

PAW: subset MDX works in one cube view, not in another

Post by PavoGa » Tue Jun 25, 2019 2:06 pm

Seems to be a context problem. However, I have been unable to figure out the problem. Here is the query:

Code: Select all

     FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Accounts Master].[Hier.001]), 0),
         [Account Mapping].([Version].[Active_02], 
            [sys.Websheet Control].[Input], 
            [Geography].[All Geography], 
            [HR Level].[All HR Levels],
            [Organization].[Function Support], 
            [lookup.Measure].[CalcFlag2]) > 0)
The Account Mapping cube has the following dimensionality:
  1. Version
  2. sys.Websheet Control
  3. Geography
  4. HR Level
  5. Organization
  6. Accounts Master
  7. lookup.Measure
So the query is accounting for all the dimensions in the cube Account Mapping. This query works in PAW if using the Accounts Master/Hier.001 subset editor from a view on Accounts Mapping. However, if opening that subset editor on another cube, it will not return a set. And yes, I'm making sure the hierarchy Hier.001 is selected in the subset editor. Even tried this:

Code: Select all

     FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Accounts Master].[Hier.001]), 0),
         [Account Mapping].([Version].[Active_02], 
            [sys.Websheet Control].[Input], 
            [Geography].[All Geography], 
            [HR Level].[All HR Levels],
            [Organization].[Function Support], 
            [Accounts Master].[Hier.001].currentmember,
            [lookup.Measure].[CalcFlag2]) > 0)
Being that it works from the cube referenced in the filter and not from other cubes indicates to me a context issue, but every dimension is accounted for in the query! A number of the dimension elements (ex: All Geography) are consolidations.

The real problem is the query is failing to return the desired members in a TI. We use MDX for virtually everything and this type of filtering is quite common. What strange evil magic is at work here?
Ty
Cleveland, TN

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: PAW: subset MDX works in one cube view, not in another

Post by PavoGa » Tue Jun 25, 2019 2:35 pm

Okay. This is, surprisingly, the first time I have attempted to build a subset on a hierarchy with the FILTER using cube values as the filter. All the others were using attributes, TIFWIW.

Have also tried saving the subset that is returning a subset and then using TM1SUBSETTOSET, but that fails to return anything as well.
Ty
Cleveland, TN

User avatar
PavoGa
Community Contributor
Posts: 381
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.05
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

Re: PAW: subset MDX works in one cube view, not in another

Post by PavoGa » Tue Jun 25, 2019 4:02 pm

Apparently, this filter method using the cube and the alternate hierarchy does not work if not within the context of the filter cube itself. As this was part of a larger query, just switched to using the base dimension and StrToMember function with a GENERATE to resolve the overall requirement.

I think this should be considered a bug, no?
Ty
Cleveland, TN

Drg
Posts: 137
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: PAW: subset MDX works in one cube view, not in another

Post by Drg » Thu Jul 04, 2019 5:50 am

Hi
Perhaps the problem is that the PAW does not know how to currentmeber?
As far as I remember for the operation of this filter, the [Accounts Master] element should be in the context of the view.

Post Reply