Consolidation value based on values of children

[Mark RMBC]

Hi all,

I have a cube that includes 2 dimensions, Cost Centre and Status.

The status has 2 elements, Status_Number and Status_Description.

There is a rule on the cube as follows:

Code: Select all

['Status_Number'] = N:
     if(ATTRN('F_CostCentre', !F_CostCentre, 'CC_DsbldFlagN') = 1, 4, stet);
['Status_Number']=C:9;
['Status_Description']=S:
	if(['Status_Number'] = 0, 'Not Started',
	if(['Status_Number'] = 1, 'Preparing',
	if(['Status_Number'] = 2, 'Staff Budget Submitted',
	if(['Status_Number'] = 3, 'MTFS Agreed',
	if(['Status_Number'] = 4, 'Disabled', 'No Status')))));

For consolidations the Status_Number is currently set so the Status_Description returns “No Status”.

I am looking for a way to set the consolidation based on the statuses of the children, so for example if all children are Staff Budget Submitted then consolidations are set to Staff Budget Submitted, if 4 out of 5 children are set to Staff Budget Submitted but 1 is set to Preparing then consolidations are set to Preparing etc etc.

While scratching my head as to how to achieve this I can’t think beyond the use of a TI to set the Status_Number for consolidations based on various logical conditions (which I envisage to be a bit of a minor nightmare). Can anyone suggest an easier approach or am I on the right lines with the TI approach.

Cheers, Mark

[ascheevel]

You're on 10.1.1 right? In 10.2.2 and later, a rule function ConsolidatedMin might easily accomplish what you're trying to do (link below). Without that, a TI might be the simplest to implement, especially if you're ok with the data not being realtime. The TI would have as a datasource the level 1 cost centers and status_number values. In the data tab you would loop through the children of each cost center to find the minimum value and write the description associated with that value in the consolidated element's status_description measure. You'd need to update your rule to stet status_description when ELLEV<>0; the numeric value for the consolidated cost center would no longer be relevant.


https://www.ibm.com/support/knowledgece ... edmin.html

[Mark RMBC]

thanks for the info ascheevel, useful to know.

Yes on 10.1.1.

Looks like the TI route for now

cheers, Mark

[Andy Key]

The ConsolidatedXXX functions have been available from as far back as at least 9.5.2.

They weren't added to the documentation until much later, but is anyone surprised by that?

[Mark RMBC]

Hi,

Have used ConsolidatedMin and got it to work but I have had to change the rule because it is ignoring zeroes if one of the children has another status number.

So if I have 2 cost centres and one is status number 0 (Not started) and the other is status number 4 (Disabled) the parent status is 4 and not 0.

I have got round this by making disabled 0 and the Not Started 1. Is it not possible to include zero values?

The rule looks as follows:

Code: Select all

['Status_Number'] = N:
     if(ATTRN('F_CostCentre', !F_CostCentre, 'CC_DsbldFlagN') = 1, 0, stet);

['Status_Number']=C: ConsolidatedMin(1,'', !F_CostCentre, 'Status_Number' );

['Status_Description']=S:

	if(['Status_Number'] = 1, 'Not Started',
	if(['Status_Number'] = 2, 'Preparing',
	if(['Status_Number'] = 3, 'Staff Budget Submitted',
	if(['Status_Number'] = 4, 'MTFS Agreed',
	if(['Status_Number'] = 0, 'Disabled', 'No Status')))));

cheers, Mark