Hi all,
I have a finance report that shows GL accounts and amounts where the user can select any level of the organization hierarchy. There is a drop down to select the level to show. This is a dynamic subset built in the subset editor with the following MDX
{ EXCEPT( { EXCEPT( { EXCEPT( { DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[bpmCostCenter].[TDUS02]} ) }, { [bpmCostCenter].[Unassigned] }) }, { [bpmCostCenter].[CR_SGA] }) }, { [bpmCostCenter].[CR1_161] }) }
Unfortunately we get a lot of organizational hierarchy items we don't need in a data feed - Long story.
It works fine in the subset editor and in Excel. On the Web form the dropdown still has items that I am excluding in the EXCEPTs above. Its not a particularly large data set probably less than 500 options tops.
We are on Web 10.2.2
Thanks in advance
Jeff
Dynamic Subset not so dynamic
-
- Posts: 13
- Joined: Tue Jul 28, 2015 4:47 pm
- OLAP Product: TM1
- Version: Planning Analytics 2
- Excel Version: Office 365
- Location: Florida USA
Dynamic Subset not so dynamic
Newb Jeff
10.2.2 FP 3
Excel 2010
10.2.2 FP 3
Excel 2010
-
- MVP
- Posts: 3128
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: Dynamic Subset not so dynamic
Hello Jeff,
Try to avoid the use of {TM1SubsetBasis()}.
Here are a whole lot of examples for MDX statements:
http://users.skynet.be/fa436118/wim/tm1 ... nts_EN.htm
Try to avoid the use of {TM1SubsetBasis()}.
Here are a whole lot of examples for MDX statements:
http://users.skynet.be/fa436118/wim/tm1 ... nts_EN.htm
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
-
- Posts: 78
- Joined: Wed Jul 31, 2013 4:32 am
- OLAP Product: Cognos TM1, EP, Analyst
- Version: 10.2.2
- Excel Version: 2013
- Location: Sydney AU
Re: Dynamic Subset not so dynamic
MDX statement below simplifies your original MDX provided:
As Wim suggested, avoid using '{TM1SubsetBasis()}', and provide definitive base to start with, such as
{TM1SubsetAll([bpmCostCentre])} => bringing through all elements in the dimension
or {Descendants([bpmCostCenter].[TDUS02])} => bringing through all children at all levels that roll up to TDUS02 (essentially anything that ELISANC to TDUS02).
For the exclusions, you can combine them into a subset, and perform 1 EXCEPT instead of multiple.
Code: Select all
{ EXCEPT(
{ DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[bpmCostCenter].[TDUS02]} )}
{ [bpmCostCenter].[Unassigned], [bpmCostCenter].[CR_SGA], [bpmCostCenter].[CR1_161] }
) }
{TM1SubsetAll([bpmCostCentre])} => bringing through all elements in the dimension
or {Descendants([bpmCostCenter].[TDUS02])} => bringing through all children at all levels that roll up to TDUS02 (essentially anything that ELISANC to TDUS02).
For the exclusions, you can combine them into a subset, and perform 1 EXCEPT instead of multiple.
MK
-
- Posts: 13
- Joined: Tue Jul 28, 2015 4:47 pm
- OLAP Product: TM1
- Version: Planning Analytics 2
- Excel Version: Office 365
- Location: Florida USA
Re: Dynamic Subset not so dynamic
Thanks for the advice guys. Really appreciate it.
Newb Jeff
10.2.2 FP 3
Excel 2010
10.2.2 FP 3
Excel 2010