Forum Stats

  • 3,826,625 Users
  • 2,260,682 Discussions
  • 7,897,041 Comments

Discussions

Help for rule design to replace VLOOKUP

Kiran_
Kiran_ Member Posts: 81 Red Ribbon
edited Jun 7, 2019 11:31AM in Planning and Budgeting

Hello everyone,

I am currently implementing EPBCS at a professional services firm to replace their existing Excel heavy solution. Given that I am new to EPBCS, I would like some help to design rules that are similar to VLOOKUPS in Excel. Here is an example:

FTE % (Driver input by users) * Average Office Level Hours (this would be the data I would need to retrieve via VLOOKUP) to get Standard Hours of an employee

How should I go about this?


Thanks in advance,
Kiran

Tagged:
Cord234

Answers

  • Pete N
    Pete N Member Posts: 77 Blue Ribbon
    edited Jun 4, 2019 6:40PM

    Ah - I actually just posted a response in your other thread.

    Basically - Load the data for Average_Office_Level_Hours to a generic member (ie: No_CostCentre, No_Entity, No_Service_Speciality, No_client, No_Employee etc). If you have got differences between them (ie different countries have different rates) don't use the "No_" member and instead load directly to the member.

    Then use a cross dimensional operator "->" in the formula to bridge the dimensions. Essbase natively knows 'where' it is in the outline as it's calculating, so everything is a relative reference.

    Net_Hours = FTE_% * Average_Office_Level_Hours->No_CostCentre->No_Entity->No_Service_Speciality->No_client->No_Employee;

    That should get you going. Note: There is a myriad of 'other' things that you'll need to think about in order for this to be always work and be successful (block creation, calculation order etc). If this is a single change that you're trying to do for reporting on a build already complete, no worries, but if this the start of a 'bigger' change I'd recommend reaching out to some external help.

    Cord234Kiran_
  • Kiran_
    Kiran_ Member Posts: 81 Red Ribbon
    edited Jun 6, 2019 12:43PM

    @Pete N, Thanks for responding. If I understood correctly, the fact that Essbase knows where it currently is removes the first part of a Vlookup, so I would only need to specify where to search using cross dimensional operators. However, I think there is a missing piece between the fact that the Average Office Hours (Account member) are stored at the office level (level 1 members of the dimension Grouping) while budgeting is done at the Employee Level and Level 0 of dimension Grouping.

    If it helps, my custom dimensions are the following: Service Specialty (Renamed Services dimension from Financials), Client, Grouping and Employee (My Grouping dimensions is the organization hierarchy composed of Region > Sub Region > Office > Teams & all employees belong to 1 team which I've translated in the application via an Attribute Dimension tide to the Employee *note that Employee is currently a seperate dimension).

    Here is a clearer picture of what I would like to achieve:

    • My Average Office Hours (Account) are stored on No Client (Client) -> OFS_No Employee (Employee) -> OFS_No Entity (Entity) -> No Service Specialty (Service Specialty) -> OFS_Direct Input (Plan Element) -> Working (Version) -> OEP_Plan (Scenario)
      • These average office hours vary from office to office so I can't necessarily have a single global assumption for everyone
    • While my target Standard Hours (Account) is entered for specific Level 0 members of all dimensions except Client and Entity which are still at the NO_Members

    So I would need something along the lines of Normal Hours = FTE_% * Average_Office_Level_Hours->No_Entity->No_Service_Speciality->No_client->No_Employee->Parent of current Grouping member

    Any idea how I could get the parent of the current member? I entered the following in the member formula for Standard Hours but don't get any value in the Standard Hours Account after I've entered my FTE for the employee and entered the Average Office Hours for the Parent of the Grouping member I have selected in my form POV: "FTE %" * @parentval("Grouping","Average Office Hours")

    Am I using @Parentval incorectly?

    Much appreciated,

    Kiran

  • Pete N
    Pete N Member Posts: 77 Blue Ribbon
    edited Jun 6, 2019 5:26PM

    You're actually pretty close!

    Normal Hours = FTE_% * Average_Office_Level_Hours->No_Entity->No_Service_Speciality->No_client->No_Employee

    "FTE %" * @parentval("Grouping","Average_Office_Level_Hours"->"No_Entity"->"No_Service_Speciality"->"No_client"->"No_Employee")

    Where the documentation refers to a member name or member combination - you are allowed to use a cross dim to define that intersection.

    Optional. Any valid single member name or member combination, or a function that returns a single member or member combination.

    @Parentval specific requires a 'single' member to be returned (because otherwise which one would it use??) but it's only going to return the value of the parent. 1 important thing here. I don't know if I would put data against parent members in grouping - because you'll need to be careful to not aggregate level 0 data up and clear it. There are ways around that - but they can get tricky if 'some' data needs to be aggregated and others left alone.

    See https://docs.oracle.com/cd/E57185_01/ESBTR/set_aggmissg.html

  • Kiran_
    Kiran_ Member Posts: 81 Red Ribbon
    edited Jun 7, 2019 11:31AM

    That is indeed an issue when it comes to aggregation. Would there be a way for me to leverage attribute dimensions and have the average office hours entered directly against the employee?

    For example, could I create a data form where users can select the correct office from the attribute dimension set at the page level to have a list of employees filtered accordingly in the rows and entered the average office hours directly?