Create view consisting of C elements

Post Reply
jwk019
Posts: 2
Joined: Fri Jul 29, 2016 10:20 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 365

Create view consisting of C elements

Post by jwk019 » Sat Dec 02, 2017 1:17 am

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

TomBr
Posts: 30
Joined: Tue Jun 03, 2008 6:56 pm

Re: Create view consisting of C elements

Post by TomBr » Mon Dec 04, 2017 11:47 am

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

declanr
MVP
Posts: 1545
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Create view consisting of C elements

Post by declanr » Tue Dec 05, 2017 7:34 am

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 );

jwk019
Posts: 2
Joined: Fri Jul 29, 2016 10:20 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 365

Re: Create view consisting of C elements

Post by jwk019 » Tue Dec 05, 2017 6:30 pm

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!

tm123
Posts: 59
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Create view consisting of C elements

Post by tm123 » Tue Dec 05, 2017 7:31 pm

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 ;

Post Reply