Dynamic Subset not so dynamic

[Bucjeff]

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

[Wim Gielis]

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

[babytiger]

MDX statement below simplifies your original MDX provided:

Code: Select all

{ EXCEPT( 
{ DRILLDOWNMEMBER( {TM1SubsetBasis()}, {[bpmCostCenter].[TDUS02]} )}
{ [bpmCostCenter].[Unassigned], [bpmCostCenter].[CR_SGA], [bpmCostCenter].[CR1_161] }
	) }

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.

[Bucjeff]

Thanks for the advice guys. Really appreciate it.