Staff Cube with Movement Analysis - Feeders & Consolidations

[RJ!]

Hi All,

I've been working on this Staff Cube thing on & off for some time and I'm almost there!!

Can anyone advise if this possible?

The logic is that the Direct Staff are always either 1 or 0 since this is the true count of actual staff in the business that is paid by our own Payroll department.
Full-Time Equivalents is where we add in all of the Overtime, Contractors, Consultants & Temporary Staff to provide the full picture of the level of resources that we have in the business.

What I need is for the Overtime to roll up properly to the FTE Consolidation & nothing to be displayed for the Direct Staff like so:



The Rule is:

Code: Select all

SKIPCHECK ;

['Overtime'] = N: 
	
	If (DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,'9905100000-D909005',!Period,'EOP','HeadCount')=0, STET,
	DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code - OT'),'9905100000-D909005',!Period,'EOP','HeadCount') ) ;

#----------------------------------------
		
FEEDERS;
[BalanceType: 'EOP'] => [ BalanceType: 'Overtime'] ;

Note: The Code "DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code - OT')" is just pulling the Cost Centre code from a Lookpup table as Overtime is paid in arrears and there are some instances where staff have transferred between Cost Centres between months, so this is making sure the Overtime is recorded in the correct Cost Centre in the report.

Note Note: Overtime is loaded to the GL Account '9905100000-D909005' which rolls up into the FTE Consolidation only.

Thanks,

RJ

[Neil Watson]

Hi RJ,

I'm not sure if I understand the issue properly but here's a guess.

You want FTE's to roll up in Overtime but Directs to have an OverTime value of 0.

If this is what you want then, the easiest way looks to be, either to
1. Check the alias of the Account Code Name using subst to see if the 1st 3 characters = 'FTE'
2. Check to see if the 1st 2 are 'DS'

Example:
Assume that the alias is in an attribute called 'Description'

['Overtime'] = N:
if (subst(attrs('Account', !Account, 'Description'),1,3 = 'FTE',
If (DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,'9905100000-D909005',!Period,'EOP','HeadCount')=0, STET,
DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code - OT'),'9905100000-D909005',!Period,'EOP','HeadCount') )
,0)
;

Hope this helps - Good Luck

[RJ!]

Hi Neil,

Thanks for your help, I ended up refining it to:

Code: Select all

['Overtime'] = N: 

	If (subst(attrs('Account', !Account, 'Desc+Code'),1,12) @= 'FTE-OVERTIME',
	If(DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,!ResponsibilityCentre,'9905100000-D909005',!Period,'EOP','HeadCount')=0,STET,
	DB('Biz_Staff',!Version,!SectionCode,!SectionA,!SectionB,!SectionC,!Staff,!Product,DB('Biz_Staff_Map',!Period,!Staff,'Local Org Code - OT'),'9905100000-D909005',!Period,'EOP','HeadCount') )
	,0) ;