Hide dimension elements with mdx subset

[tosca1978]

Hi all,

I'm using 9.5.2 and excel 2007.

The end user of the model that I have built wants the ability to deactivate elements of a "Prospects" dimension so that they do not appear in any input sheets/reports etc. I have created a TI to put "IN-ACTIVE" in the "Status" attribute of the dimension.

I have then created a dynamic subset called "Prospects_Expand_All_Active" using the following code:

Code: Select all

{FILTER( {TM1DRILLDOWNMEMBER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Hierarchy] )}, 5)}, ALL, RECURSIVE )}, [Prospects].[Status] = "ACTIVE")}

It strips out those elements that do not have "ACTIVE" in the status attribute. This works fine.

The input sheet is an active form. The TM1RPTROW formula in this active form references the "Prospects_Expand_All_Active" subset described above, so only shows "Active" elements - all good.

The Issue:

The subset works fine (it only displays active elements) until you roll up/drill down. Once you do this it will display all elements under a consolidation regardless of the STATUS. For example, the active form (input sheet) displays all active elements in the subset. However, if the user rolls up a consolidation and then drills down again both the active and inactive elements will be seen beneath the consolidation.

So I assume that just like when in subset editor, once you modify the subset (like rolling up/drilling down) it stops being a subset created by an expression and starts being a static subset. What I need is a subset where the elements with "inactive" status remain hidden.

Any ideas would be much appreciated.

Many thanks

[standtrue]

There may be a more elegant way to handle your issue via MDX, but one possibility would be to have a TI process loop through the dimension and create an alternate hieararchy of "Active" prospects. Your Active Form would then use the top of this alternate hierarchy as the starting point and users would never be able to drill down to non-active elements.

This process could run each night or via an Action Button after the user has set prospects to inactive...

[tosca1978]

Standtrue, thanks for your suggestion. I haven't implemented it but it did get me thinking about an alternative hierarchy for In-active prospects...

I have created a new consolidated element in the same dimension called "In-Active". I then have a ti process with the following code in the Prologue tab:

Code: Select all

# The following code places "In-Active" against the Status attribute of the prospect that is to be de-activated:

AttrPutS('IN-ACTIVE', 'Prospect', pProspect_Deactivate, 'Status');

# The following code adds the prospect to  be de-activated to the consolidated element  "In-Active":

DimensionElementComponentAdd('Prospect','In-Active', pProspect_Deactivate, 1.0);

# The following code removes the prospect to be de-activated from the parent that it belongs to in the main hierrachy:

DimensionElementComponentDelete('Prospect', pParent_deactivate_prospect, pProspect_Deactivate);

All of this is in the Prologue tab as the Metadata tab is not accessible as there are no variables in the ti. The ti has 2 parameters passed to it from the front end(excel worksheet with action button to execute ti).

Upon execution the first 2 parts of the Prologue script work (change status and add prospect to In-active parent), but the 3rd part (delete from parent in main hierarchy) does not work. I get the following error message in the log file:

Error: Prologue procedure line (22): Unable to delete element "BD011028" from consolidation.

I was pretty sure that part 3 should have worked from the Prologue tab. However, I moved this part of the code to the Epilogue tab and the process completes with no errors. However...the prospect does not get deleted from the parent as the code in the epilogue tab instructs it to. The "in_active" prospect remains under it's original parent in the main hierarchy.

If anyone can tell me where I'm going wrong or what I could try instead it would be much appreciated.

Many thanks