Updating MDX Subsets in TI

[shorsted]

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

[John Hammond]

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