TM1 Forum

We have dynamic subsets in place which we use in Active Forms for reporting. However, we have found that at times the dynamic subset goes blank (not sure what the trigger for this is) and so we have to re-create them in order for them to work. Reading up on this I have found that it is better for the expression to include TM1SUBSETALL in the statement. I am now doing this and have been able to re-create all the necessary dynamic subsets but one.

The Dimension is called PJ_AMDBID. On recording the expression I first select ALL
{TM1SUBSETALL( [PJ_AMDBID] )}

I then filter by wildcard 'ALL PJ'. This is because we have a numer of level ones. I only want the elements that sit under the grouping called 'ALL PJ'.
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [PJ_AMDBID] )}, "ALL PJ")}

If I don't use the filter by wildcard and simply select 'ALL PJ' from the view then I lose the TM1SUBSETALL from the expression - this is why I have done a wildcard filter
{TM1SubsetBasis()}

I then Expand All Elements
{TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [PJ_AMDBID] )}, "ALL PJ")}, ALL, RECURSIVE )}

And then final changes that do not update the expression are:
I set the Use Aliases to ON and select an Alias we have called 'ID & Work Name' and I do View > Expand Above

Screen shots of above steps attahced.

What I see in the view looks great. So I save it as a dynamic subset. My issue is when I go back into it, it returns nothing. It goes all blank. My other dynamic subsets I've set up using the same logic are all fine the only thing different about this one is the fact that I switched on the Use Aliases.

Is there a way to incorporate the Alias selection into the expression code? Or is there a smarter way to create this dynamic subset.

Since you know the identity of the member there is no need for exact match wildcard search, you should just directly drill down from "All PJ" in MDX.
{TM1DRILLDOWNMEMBER( { [PJ_AMDBID].[ALL PJ] }, ALL, RECURSIVE )}

AND if you're using active forms then it is much better to do the MDX in the active form itself and not use the dynamic subset at all.

Thanks lotsaram.

Using the code you have suggested seems to resolve the issue I had with the alias which is great.

I will write them all using this logic and see how it goes and hopefully they don't randomly drop out which is what has been happening.

Thanks
Claire