Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
565 lines (469 sloc) 15.2 KB
601,100
602,"Bedrock.Cube.View.Create"
562,"NULL"
586,"Variables"
585,"Variables"
564,
565,"o107s_0x?UNE^@5agv_4z1@f3wY;abMh41uxkn]5`>P<WUliPngCwIdQ_dAqLYXCOYC@?ae21jRbc0BcD>BOPY2:kW1prPGzy>_;QSd00nvqRkRoc^7ENwQ4Nz9rCA3R3VrB[q2yBD>\<KhgLuWmdc8g\ctyh@:JOrm:DRxEKItueNXCuf?oyYc`D8^zBfZ]quZSuH_S"
559,1
928,0
593,
594,
595,
597,
598,
596,
800,
801,
566,0
567,","
588,"."
589,
568,""""
570,All
571,
569,0
592,0
599,1000
560,10
pCube
pView
pFilter
pSuppressZero
pSuppressConsol
pSuppressRules
pDimensionDelim
pElementStartDelim
pElementDelim
pDebug
561,10
2
2
2
1
1
1
2
2
2
1
590,10
pCube,""
pView,""
pFilter,""
pSuppressZero,1
pSuppressConsol,1
pSuppressRules,1
pDimensionDelim,"&"
pElementStartDelim,":"
pElementDelim,"+"
pDebug,0
637,10
pCube,Cube Name
pView,Name of the View
pFilter,Filter: Year: 2006 + 2007 & Scenario: Actual + Budget & Organization: North America Operations
pSuppressZero,Suppress Zero Data (Skip = 1)
pSuppressConsol,Suppress Consolidations (Skip = 1)
pSuppressRules,Suppress Rules (Skip = 1)
pDimensionDelim,Delimiter for start of Dimension/Element set
pElementStartDelim,Delimiter for start of element list
pElementDelim,Delimiter between elements
pDebug,Debug: 0
577,0
578,0
579,0
580,0
581,0
582,0
572,402
#****Begin: Generated Statements***
#****End: Generated Statements****
#####################################################################################
##~~Copyright bedrocktm1.org 2011 www.bedrocktm1.org/how-to-licence.php Ver 3.1.0~~##
#####################################################################################
# This process automatically creates a view that can be used for exporting, copying and zeroing out numbers.
# Note:
# - The pFilter parameter contains the dimenson and elements to be used for filtering.
# - The format of the pFilter parameter is as follows delimiters of :, & and +:
# Dim1: Elem1 + Elem2 & Dim2: Elem3 + Elem4
# - The dimension parameters do not need to be given in the index order of dimensions in the cube.
# - The dimension name is specified as the first member of the delimited string of elements.
# - If consols are skipped the N level children of any consolidated filter elements will be used
# - Spaces are ignored so use them to make your filter more readable
### Constants ###
cProcess = 'Bedrock.Cube.View.Create';
cTimeStamp = TimSt( Now, '\Y\m\d\h\i\s' );
sRandomInt = NumberToString( INT( RAND( ) * 1000 ));
cDebugFile = GetProcessErrorFileDirectory | cProcess | '.' | cTimeStamp | '.' | sRandomInt ;
sSubset = pView;
sDelimDim = TRIM(pDimensionDelim);
sElementStartDelim = TRIM(pElementStartDelim);
sDelimElem = TRIM(pElementDelim);
nErrors = 0;
### Initialise Debug ###
If( pDebug >= 1 );
# Set debug file name
sDebugFile = cDebugFile | 'Prolog.debug';
# Log start time
AsciiOutput( sDebugFile, 'Process Started: ' | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
# Log parameters
AsciiOutput( sDebugFile, 'Parameters: pCube: ' | pCube );
AsciiOutput( sDebugFile, ' pView: ' | pView );
AsciiOutput( sDebugFile, ' pFilter: ' | pFilter );
AsciiOutput( sDebugFile, ' pSuppressZero: ' | NumberToString( pSuppressZero) );
AsciiOutput( sDebugFile, ' pSuppressConsol: ' | NumberToString( pSuppressConsol) );
AsciiOutput( sDebugFile, ' pSuppressRules: ' | NumberToString( pSuppressRules) );
AsciiOutput( sDebugFile, ' pDimensionDelim: ' | pDimensionDelim );
AsciiOutput( sDebugFile, ' pElementStartDelim: ' | pElementStartDelim );
AsciiOutput( sDebugFile, ' pElementDelim: ' | pElementDelim );
EndIf;
# If specified cube does not exist then terminate process
If( CubeExists( pCube ) = 0 );
sMessage = 'Cube: ' | pCube | ' does not exist';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
nErrors = nErrors + 1;
ProcessQuit;
EndIf;
## Validate the View parameter
If( TRIM(pView) @= '' );
sMessage = 'A view name must be provided';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
nErrors = nErrors + 1;
ProcessQuit;
EndIf;
# Check the delimiters
If( sDelimDim @= '' );
sMessage = 'The dimension delimiter is blank';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ProcessQuit;
EndIf;
If( sElementStartDelim @= '' );
sMessage = 'The dimension name delimiter is blank';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ProcessQuit;
EndIf;
If( sDelimElem @= '' );
sMessage = 'The element delimiter is blank';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ProcessQuit;
EndIf;
If( sDelimDim @= sElementStartDelim % sDelimDim @= sDelimElem % sElementStartDelim @= sDelimElem );
sMessage = 'The delimiters cannot me the same';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ProcessQuit;
EndIf;
### Create View ###
If ( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Creating view ' | pView | ' on cube ' | pCube );
EndIf;
If( pDebug <= 1 );
If( ViewExists( pCube, pView ) = 1 );
# Clear all of the subsets that may be attached to the view
nCount = 1;
While( TabDim( pCube, nCount ) @<> '' );
sCubeDimName = TabDim( pCube, nCount );
# Subset is the same name as the view
If( SubsetExists( sCubeDimName, pView ) = 1 );
# Clear the elements
SubsetDeleteAllElements( sCubeDimName, pView );
# Add all elements
If( SubsetIsAllSet(sCubeDimName, pView, 1) <> 1 );
If( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Unable to add all elements on subset ' | pView | ' on dimension ' | sCubeDimName );
EndIf;
ProcessQuit;
EndIf;
ENDIF;
nCount = nCount + 1;
End;
Else;
ViewCreate( pCube, pView );
EndIf;
ViewExtractSkipCalcsSet( pCube, pView, pSuppressConsol );
ViewExtractSkipZeroesSet( pCube, pView, pSuppressZero );
ViewExtractSkipRuleValuesSet( pCube, pView, pSuppressRules );
EndIf;
### Split filter and create subsets ###
sFilter = TRIM( pFilter );
nChar = 1;
nCharCount = LONG( sFilter );
sWord = '';
sLastDelim = '';
nIndex = 1;
# Add a trailing element delimiter so that the last element is picked up
If( nCharCount > 0 );
sFilter = sFilter | sDelimElem;
nCharCount = nCharCount + LONG(sDelimElem);
EndIf;
WHILE (nChar <= nCharCount);
sChar = SUBST( sFilter, nChar, 1);
If( pDebug >= 1 );
sMessage = 'Next character is: ' | sChar | ' at ' | NumberToString(nChar);
AsciiOutput( sDebugFile, sMessage );
EndIf;
# Used for delimiters, required for multiple character dilimiters
sDelim = '';
nAddExtra = 0;
# Ignore spaces
IF (TRIM(sChar) @<> '' );
### Dimension Name ###
# If the delimiter is more than 1 character peek ahead the same amount
# Ignore the first character
sDelim = sChar;
nCount = LONG(sElementStartDelim) - 1;
If( nCount > 0 & nChar + nCount <= nCharCount );
# Add the extra characters
sDelim = sDelim | SUBST( sFilter, nChar + 1, nCount);
# Move to the end of the delimter
nAddExtra = nCount;
EndIf;
If( sDelim @= sElementStartDelim );
sChar = sDelim;
If( sLastDelim @<> '' & sLastDelim @<> sDelimDim );
sMessage = 'The name of a dimension must follow a dimension delimiter (' | sDelimDim | ')';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ProcessQuit;
EndIf;
sDimension = sWord;
If( pDebug >= 1 );
sMessage = 'Found dimension name: ' | sDimension | ' at ' | NumberToString(nChar);
AsciiOutput( sDebugFile, sMessage );
EndIf;
If( DimensionExists( sDimension ) = 0 );
# The dimension does not exist in the model. Cancel process
sMessage = 'Dimension: ' | sDimension | ' does not exist';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
nErrors = nErrors + 1;
ProcessQuit;
EndIf;
### Determine the dimension is a member of the cube ###
nCount = 1;
nDimensionIndex = 0;
While( TabDim( pCube, nCount ) @<> '' );
sCubeDimName = TabDim( pCube, nCount );
IF(
sDimension @= sCubeDimName);
nDimensionIndex = nCount;
ENDIF;
nCount = nCount + 1;
End;
If( nDimensionIndex = 0 );
# The dimension does not exist in the cube. Cancel process
sMessage = 'Dimension: ' | sDimension | ' is not a member of: '| pCube | 'cube.';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
nErrors = nErrors + 1;
ProcessQuit;
EndIf;
# Create the subset
If( SubsetExists( sDimension, sSubset ) = 1 );
If ( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Subset ' | sSubset | ' exists on dimension ' | sDimension | '. Deleting all elements' );
EndIf;
If( pDebug <= 1 );
SubsetDeleteAllElements( sDimension, sSubset );
EndIf;
Else;
If ( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Creating subset ' | sSubset | ' on dimension ' | sDimension );
EndIf;
If( pDebug <= 1 );
SubsetCreate( sDimension, sSubset );
EndIf;
EndIf;
# Attach to the view
If ( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Assigning subset ' | sSubset | ' to view ' | pView );
EndIf;
If( pDebug <= 1 );
ViewSubsetAssign(pCube, pView, sDimension, sSubset);
EndIf;
nIndex = 1;
sLastDelim = sChar;
# Clear the word
sWord = '';
Else;
# Reset extra chars
nAddExtra = 0;
### Check both both dim delimiter and element delimiter ###
nIsDelimiter = 0;
## Check dimension delimiter first
# If the delimiter is more than 1 character peek ahead the same amount
# Ignore the first character
sDelim = sChar;
nCount = LONG(sDelimDim) - 1;
If( nCount > 0 & nChar + nCount <= nCharCount );
# Add the extra characters
sDelim = sDelim | SUBST( sFilter, nChar + 1, nCount);
# Move to the end of the delimter
nAddExtra = nCount;
EndIf;
If( sDelim @= sDelimDim );
nIsDelimiter = 1;
sChar = sDelim;
Else;
# Reset extra chars
nAddExtra = 0;
## Check element delimiter
# If the delimiter is more than 1 character peek ahead the same amount
# Ignore the first character
sDelim = sChar;
nCount = LONG(sDelimElem) - 1;
If( nCount > 0 & nChar + nCount <= nCharCount );
# Add the extra characters
sDelim = sDelim | SUBST( sFilter, nChar + 1, nCount);
# Move to the end of the delimter
nAddExtra = nCount;
EndIf;
If( sDelim @= sDelimElem );
nIsDelimiter = 1;
sChar = sDelim;
Else;
# Reset extra chars
nAddExtra = 0;
EndIf;
EndIf;
If ( nIsDelimiter = 1 );
If( sLastDelim @= '' % sLastDelim @= sDelimDim );
sMessage = 'An element delimiter must follow a dimension name: ' | sChar | ' (' | NumberToString(nChar) | ')';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
ProcessQuit;
EndIf;
sElement = sWord;
If( pDebug >= 1 );
sMessage = 'Inserting element: ' | sElement | ' at ' | NumberToString(nChar);
AsciiOutput( sDebugFile, sMessage );
EndIf;
If( DIMIX( sDimension, sElement ) = 0 );
# The element does not exist in the dimension. Cancel process
sMessage = 'Element: ' | sElement | ' in dimension ' | sDimension | ' does not exist';
If( pDebug >= 1 );
AsciiOutput( sDebugFile, sMessage );
EndIf;
nErrors = nErrors + 1;
ProcessQuit;
EndIf;
If ( pSuppressConsol = 1 & DTYPE( sDimension, sElement) @= 'C' );
# Add all N level elements to the subset
# Loop through all elements and check if it is an ancestor
If ( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Element ' | sElement | ' is consolidated' );
EndIf;
nElCount = DIMSIZ ( sDimension );
n = 1;
WHILE ( n <= nElCount );
sEl = DIMNM( sDimension, n );
IF( DTYPE( sDimension, sEl) @<> 'C' & ELISANC( sDimension, sElement, sEl ) = 1 );
If ( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Inserting element ' | sEl | ' into subset ' | sSubset | ' on dimension ' | sDimension);
EndIf;
If( pDebug <= 1 );
SubsetElementInsert( sDimension, sSubset, sEl, nIndex );
EndIf;
EndIf;
n = n + 1;
END;
Else;
# Add the element to the subset
If ( pDebug >= 1 );
AsciiOutput( sDebugFile, 'Inserting element ' | sElement | ' into subset ' | sSubset | ' on dimension ' | sDimension);
EndIf;
If( pDebug <= 1 );
SubsetElementInsert( sDimension, sSubset, sElement, nIndex );
EndIf;
EndIf;
nIndex = nIndex + 1;
sLastDelim = sChar;
# Clear the word
sWord = '';
Else;
sWord = sWord | sChar;
EndIf;
EndIf;
EndIf;
nChar = nChar + nAddExtra + 1;
If( pDebug >= 1 );
sMessage = 'Current word is: ' | sWord | ' at ' | NumberToString(nChar - 1);
AsciiOutput( sDebugFile, sMessage );
EndIf;
END;
573,4
#****Begin: Generated Statements***
#****End: Generated Statements****
574,4
#****Begin: Generated Statements***
#****End: Generated Statements****
575,40
#****Begin: Generated Statements***
#****End: Generated Statements****
#####################################################################################
##~~Copyright bedrocktm1.org 2011 www.bedrocktm1.org/how-to-licence.php Ver 3.1.0~~##
#####################################################################################
### Initialise Debug ###
If( pDebug >= 1 );
# Set debug file name
sDebugFile = cDebugFile | 'Epilog.debug';
# Log errors
If( nErrors <> 0 );
AsciiOutput( sDebugFile, 'Errors Occurred' );
EndIf;
# Log finish time
AsciiOutput( sDebugFile, 'Process Finished: ' | TimSt( Now, '\d-\m-\Y \h:\i:\s' ) );
EndIf;
### If errors occurred terminate process with a major error status ###
If( nErrors <> 0 );
ProcessQuit;
EndIf;
### End Epilog ###
576,CubeAction=1511 DataAction=1503 CubeLogChanges=0
930,0
638,1
804,0
1217,1
900,
901,
902,
903,
906,
929,
907,
908,
904,0
905,0
909,0
911,
912,
913,
914,
915,
916,
917,0
918,1
919,0
920,50000
921,""
922,""
923,0
924,""
925,""
926,""
927,""