TM1 Forum

Hi

I am trying to create a drill through process in TI which is recreating a cube view and setting its subsets, rows, etc

I am trying to set the Account dimension as the row dimension and also assign a subset to it. If the element that was drill from is a level 0 then I just want to insert that element on its own to the subset, however if the element was a consolidation then I'm trying to create an MDX subset that shows all the level 0 children of the original element

I can create the MDX, that's not an issue, but I'm trying to use the same subset in both scenarios, so first I destroy the view and recreate it, then I do the same with the subsets.

I get an error if I try to destroy the subset once its been created using MDX as it seems to think its still being used in a view - which it isn't as I recreated that, but I believe that issue was raised by steve_h "Subset locked by phantom view" so instead of destroying the subset I'm now trying SubsetdeleteAllElements, however the only way I can find to add MDX to a subset is using SubsetCreateByMDX and this gives me an errors saying that it can't register the subset as it already exists - which of course it does as I haven't destroyed it!

Is there another MDX function that I don't know about in TI. I'm using 9.1.4, I've sesen some discussions on here about RefreshMDX but I think that came in 9.5

I think as a work around I'm going to create the subset by looping through the children and adding them

Here is an exmple of my code, CP_Account is being passed in as a parameter

ViewRowDimensionSet(Cube, View, 'CP_Account',1);
IF(SubsetExists('CP_account', 'CP_Account Comments')=1);
# SubsetDestroy('CP_account', 'CP_Account Comments');
SubsetDeleteAllElements('CP_account', 'CP_Account Comments');
EndIf;
If (Ellev ('CP_Account', CP_Account) = 0);
SubsetCreate('CP_account', 'CP_Account Comments');
SubsetElementInsert('CP_account', 'CP_Account Comments', CP_Account, 1);
Else;
MDXRef =
'{TM1FILTERBYLEVEL( {TM1DRILLDOWNMEMBER( {TM1FILTERBYPATTERN( {TM1SUBSETALL( [CP_Account] )}, "'
| CP_Account | '" )}, ALL, RECURSIVE )}, 0)}';
SubsetCreatebyMDX ('CP_Account Comments', MDXRef);
EndIf;
ViewSubsetAssign(Cube, View, 'CP_account', 'CP_Account Comments');

Any help is much appreciated!
Regards
Sarah

Sarah

Your logic is sound and what you are doing should work.

My only advice is to try running the subset delete or view delete in a separate process or within epilog instead of prolog. I have found that this sometimes gets round the problem since it leaves stuff 'open' in each section of code and then sort of commits them when you move on. A bit vague I know but its all I can think of.

Regards

John