Page 1 of 1
Create view consisting of C elements
Posted: Sat Dec 02, 2017 1:17 am
by jwk019
I need to create a view in TI (Prolog) that comprises some dimensions with C elements and some dimensions with N elements, to be used as a datasource. I presume this means I need to create some subsets that contain only the C elements.
Seems like this should be a simple thing, but I keep ending up with the N elements. Even if I hard code in the name of the roll-up as follows,
Code: Select all
ViewSubsetAssign( vCubeName, vSourceViewName, CCDim, 'All Cost Center' );
...I get back a message that 'All Cost Center not a member of Dimension...'
BTW the reason I want to do this is because I will pull the consolidated total number from a group of elements into a single element elsewhere.
The expert help I so often see here would be greatly appreciated!
--Jack
Please port in the correct forum
Re: Create view consisting of C elements
Posted: Mon Dec 04, 2017 11:47 am
by TomBr
Hi,
ViewSubsetAssign is expecting a Subset as the final argument and I think you are just putting in a Consolidated Element.
I suspect that the full message you are getting is something like:
Subset "All Cost Center" not found in dimension "Cost Center"
If so probably the easiest way to resolve is to create a Subset called All Cost Center with one element - All Cost Center
Hope that helps,
Tom
Re: Create view consisting of C elements
Posted: Tue Dec 05, 2017 7:34 am
by declanr
It’s also worth noting that by default TIs skip consolidated records as normally we don’t want to use them as a source.
On the occasions that you do want to use consolidations, you need to let the TI know by using code as below:
Code: Select all
ViewExtractSkipCalcsSet ( <MyCube>, <MyView>, 0 );
Re: Create view consisting of C elements
Posted: Tue Dec 05, 2017 6:30 pm
by jwk019
Thank you TomBr and declanr! Both of these were helpful to me.
Tom -- appreciate the quick response and solution.
declanr -- I appreciate the succinct summation of the default behavior and remedy. I have not been settled in my mind about this issue, so your statement is helpful
So as a follow-on: if i want to loop through a Subset1 that consists of a combination of N elements and some C elements, to create an exploded view subset (Subset2) that has all the N elements in Subset1, what is the technique? The element count of Subset1 is 8, but it comprises 20 N elements when the C elements are exlploded (so 20 will be the size of Subset2).
Code: Select all
iCtr = 1;
While( iCtr <= SubsetGetSize(vDimName, Subset1);
vElem = SubsetGetElementName(vDimName, Subset2 , iCtr);
If(DType(vDim, vElem) = 'N'):
SubsetElementInsert(vDimName, Subset2 , 1);
EndIf;
iCtr = iCtr + 1;
End ;
The issue as I see it right now is that the size of Subset1 is 8 elements, not 20, so I am thus not getting them all. So how do I pull out the N-level detail to form the Subset2?
Appreciate any feedback!
Re: Create view consisting of C elements
Posted: Tue Dec 05, 2017 7:31 pm
by tm123
Why don't you do this with an MDX:
You can try the code below
sMDX = '{TM1FILTERBYLEVEL({DESCENDANTS(TM1SUBSETTOSET([' | vDimName | '],"' | vSubset1 | '"))}, 0)}' ;
IF (SubsetExists ( vDimName , vSubset2 ) <> 0 );
SubsetDestroy ( vDimName , vSubset2 ) ;
ENDIF;
SubsetCreateByMdx ( vSubset2 , sMDX , vDimName ) ;
Then you can convert this to static subset if you want by removing one element and adding it back
nLastIndex = SUBSETGETSIZE ( vDimName , vSubset2 ) ;
IF ( nLastIndex > 0 );
sLastSubsetElement = SUBSETGETELEMENTNAME ( vDimName , vSubset2 , nLastIndex ) ;
SUBSETELEMENTDELETE ( vDimName , vSubset2 , nLastIndex ) ;
SUBSETELEMENTINSERT ( vDimName , vSubset2 , sLastSubsetElement , nLastIndex ) ;
ELSE ;
SubsetDeleteAllElements ( vDimName , vSubset2 ) ;
ENDIF ;