Get n Level First Parent's Rule Statement

[LUS]

I use TM1 10.1. I am testing to write a rule to copy first parents of n level elements' Monthly Wages to a summary cube without the need of creating an intermediate calculation cube. It is from our staff planning's employee Info cube.

Employee Info Capture.PNG (22.61 KiB) Viewed 2762 times

to our Ops Plan Cube

Op Plan Capture.PNG (16.91 KiB) Viewed 2762 times

If I hardcoded the first 0 level element (EIN - 023011) of "200010-010 Organic Prep" dept in the rule, below rule works

['Base Wages', 'Jan']=N:DB('Employee Info', ELPAR('Employee', 'EIN - 023011', 1), 'Budget','JanWage');

But I need to replace 'EIN - 023011' for all n level elements of "Employee" dim, below rule does not work (I got string expression error message)

['Base Wages', 'Jan']=N:DB('Employee Info', ELPAR('Employee', IF( EllEV('Employee', !Employee)=0, !Employee, STET), 1), 'Budget','JanWage');

If element level is not zero level, I do not want to have its first parents, bypass it. Please help, thank you

[declanr]

Bit confused with what you are doing here.

I am assuming that "200010-010 Organic Prep" is an n-level of a dimension that exists in your destination cube? If so you can just use something along the lines of:

Code: Select all

['Base Wages', 'Jan']=N:DB('Employee Info', !Department, 'Budget','JanWage');

[LUS]

Employee Dimension was built one level under "COMPANY" dim's hierarchy tree (BU is zero level of Company Dim), but for "Employee" dim, Employee Identification Number (EIN -XXXX) is n level element. My Ops Plan Summary cube removes "Employee" Dim, Add " Company" Dim, sum all employees' wages to each BU which is n level of company dim. For instance,

Total Employee
10 East Region (Company Dim)
200 Burlington (Company Dim)
200010(BU, n level Company Dim)
EIN- 023011 (Employee #1, Employee ID)
EIN-023114 (Employee #2, Employee ID)
200040 (Burlington Air Toxic Dept)
280 Denver
280010(Business Unit is Lab Location + Dept, e.g Burlington Organic Pre Dept)
EIN- XXXX (Employee #1)
EIN-XXXX (Employee #2)
20040 (Burlington Air Toxic Dept)

Employee Dim Capture.PNG (22.85 KiB) Viewed 2751 times

Company Dim Capture.PNG (13.73 KiB) Viewed 2751 times

My Source Cube ('Employee Info") has three dims: Employee, Ops Plan Version, mEmployeeInfo (contains JanWage...DecWage as one element)
My Destination Cube ("Ops Plan") has Five dims: Ops Plan Account, Company, Ops Plan Version, Month, Mmeasure (Amount as Element)

[declanr]

Yes so you can reference the consolidated element in your source dimension since it has the same name as the nodal element in your destination dim.

Code: Select all

['Base Wages', 'Jan']=N:DB('Employee Info', !Company, 'Budget','JanWage');

[Steve Rowe]

Bumped post hiding the post while we confirmed the data in the OP was test data.