Process to Stamp Parents Based off Child Data

[vh5150]

I have a simple two dimensional cube with Projects down the rows and a string dim called status across the columns. The project dim has two levels, Master Project and sub project (level 0). The Status dim is a single string measure element. The data held at the the intersection between sub project and status contains three types of status: Open, Completed and Pending.

If the children under any given Level 1 project have all of the same status I would like to stamp the level 1 parent with that same status. So essentially if ALL of the children are stamped "Completed" under any given Master Project (level 1) I would like to stamp that level 1 Master Project with "Completed" also. I do not want to stamp the level 1 if only some of the children are populated with a status.

I have created the following While condition but it doesn't work because it only bases the status off of the last child under any given level 1:


sCube = 'Project';
sDim = 'Project';
vSub = 'All';



nCount =1;
nMax = DIMSIZ( sDim );

WHILE( nCount <= nMax );
sELEM = DIMNM( sDim, nCount );

IF(ELLEV( sDim, sElem) = 0 & CELLGETS( sCube, sElem, 'Status' ) @= '');
CELLPUTS( 'EXCEPTION' , sCube, ELPAR( sDim, sElem,1), 'Status');

ELSEIF(ELLEV( sDim, sElem) = 0);
CELLPUTS( CELLGETS( sCube, sElem, 'Status'), sCube, ELPAR( sDim, sElem,1), 'Status');

ENDIF;

nCount = nCount +1;


END;


How can I have the code look at all of the children under any given level 1 to determine if all of the status are the same???

Thanks

[declanr]

First of all change from doing a loop on the dimension and instead use the dimension as the datasource; you don't have to but I usually would (the other option is to do nested loops - not difficult at all but I like to let in built functionality do the work where possible.)


Therefore the data tab would be (assuming V1 is the variable of the Project dimension):

Code: Select all

sCube = 'Project';
sDim = 'Project';
vSub = 'All';


If ( Ellev ( sDim, v1 ) > 0 );
       iCount =2;
       iMax = ElCompN( sDim, v1 );
       sFirstSt = CELLGETS( sCube, ElComp ( sDim, v1, 1), 'Status' );
       nFlag = 0;
       While ( iCount <= iMax );
             sChild= ElComp ( sDim, v1, iCount );
             sStatus = CellGetS ( sCube, sChild, 'Status' );
             If ( sFirstSt @<> sStatus );
                     nFlag = 1;
                     iCount = iMax + 1;
             EndIf;
             iCount = iCount + 1;
        End;
        If ( nFlag = 0 );
             CellPutS ( sFirstSt, sCube, v1, 'Status' );
        EndIf;
EndIf;

Note that this has by no means been tested so you may have some dodgy commas in there etc and I haven't included anything to zero the data before hand etc but it should point you in the right direction.

[vh5150]

Hi Declan,

First off, thank you so much for promptly stepping into this. Very gracious of you and much appreciated. The process ran successfully but it didn't stamp the level 1 intersections. In the attachment I would expect the Project # master project to be stamped with "Completed" since it says completed in all of the child intersections. Any ideas??? Project T and Project X I wouldn't expect a stamp since the child intersections arent all populated.

Thanks again,

[declanr]

Hi Declan,

First off, thank you so much for promptly stepping into this. Very gracious of you and much appreciated. The process ran successfully but it didn't stamp the level 1 intersections. In the attachment I would expect the Project # master project to be stamped with "Completed" since it says completed in all of the child intersections. Any ideas??? Project T and Project X I wouldn't expect a stamp since the child intersections arent all populated.

Thanks again,

There is a high possibility that there is just something stupid in the code I wrote but I'm on a train with terrible wifi at the moment so can't check it all too carefully. First things first, have you got skip consolidations unchecked in the datasource? If it has a tick you will need to remove that.

If that is already unchecked you can put asciioutputs into the loop and if statements to see what parts are getting triggered and what aren't.

[Duncan P]

You could do it in a rule.

You will need a numeric attribute on the projects dimension (called "IndexInParent" in my example) and another string measure (called 'status_if_same_as_sibling' in my example) and the following rule statements

Code: Select all

['status'] = S: IF ( 0 = ELLEV( 'project',!project ),STET, DB( 'same_string', ELCOMP('project',!project,1),'status_if_same_as_sibling' ));

['status_if_same_as_sibling'] = S: IF( ELCOMPN( 'project', ELPAR( 'project', !project, 1 ) ) = ATTRN('project',!project,'IndexInParent') % DB('same_string', !project, 'status') @= DB('same_string', ELCOMP('project', ELPAR('project',!project,1), 1 + ATTRN('project',!project,'indexinparent') ),'status_if_same_as_sibling' )
, DB('same_string', !project, 'status')
, '' );

What this does is to use the 'IndexInParent' attribute to identify the next sibling and compares statuses. If they are the same it carries that value up to the first sibling in each set. Otherwise it gives ''. Each parent project looks at the calculated value for its first child project.

[vh5150]

Hi Duncan,

I like the rule approach, however the second block of code doesn't work (see below) because status_if_same_as_sibling is not a valid element which makes sense because it's an attribute. Does this rule have to go against the element attributes cube? Am I missing something?

['status_if_same_as_sibling'] = S: IF( ELCOMPN('Project', ELPAR('Project', !Project, 1 ) ) =
ATTRN('Project',!Project,'IndexInParent') % DB('Project', !Project, 'System Status') @=
DB('Project', ELCOMP('Project', ELPAR('Project',!Project, 1),
1 + ATTRN('Project',!Project,'indexinparent') ),'status_if_same_as_sibling' ),
#DB('Project_Status', !Project, 'System Status'), '' );

Thanks Duncan

[Duncan P]

and another string measure (called 'status_if_same_as_sibling' in my example)

What I meant was that alongside "status" which is a string type element on the last dimension there should be another string type element called (in my example) "status_if_same_as_sibling". You would not need to show this to the users.

[EDIT] I am presuming that you are OK with populating the "IndexInParent" attribute with the appropriate values. I use a small TI to do it generally.