Adding new level to existing hierarchy


Hi Everyone,

The model has 7 levels of hierarchy for three years now. Would it be advisable to add another level between l6 and l7, taking into account the consequences?


  • @MounikaVakacherla If there is a valid business case then you should do it. How you go about doing it is the thing you need to assess. I can imagine if you try to do it now you would get all sorts of error such an invalid parent or something.

    For example, original hierarchy is

    • L6
      • L7

    Then you want to introduce

    • L6
      • L6.5
        • L7

    If you have a formula somewhere that uses PARENT(ITEM('L7')), it would prevent you from inserting L6.5 as the current line item using the PARENT formula would have a format of "L6". But attempting to insert L6.5 would require the format of the line item to be "L6.5". Depending on how complex your model is, trying to identify all the line items that would prevent you from inserting that level can be painful

    As an approach, you can perform the following steps:

    1. Export the module line items. This is so you can refer back to it later as the next steps will require you to delete then re-introduce formulas

    2. Identify all line items that has L6 as format. Temporarily update the formula against these line items to blank

    3. Insert the new list L6.5 in to the hierarchy. For example,

    • L6
      • L6.5
        • L7

    4. Create the system modules for L6.5

    5. Assess what changes would be required for the items identified in step 2. It can be one of the following:

    • Change format to L6.5 and re-instate original formula
    • Keep format to L6 and update original formula
    • Keep format to L6 and re-instate original formula
    • Keep format to L6 and re-instate original formula AND add a new line item of L6.5 format the apply necessary formula
    • There's probably other combinations that will fit in here that I'm not across but this should hopefully provide you with a staring point