Alternative Hierarches


I have a P&L 1 that has being data input into it using a list Organisation 1 as its hierarchy. I require the same data to be pulled into a similar P&L which is using a different list Organisation 2 as its hierarchy. Both Organization lists have the same Codes against each reporting entity, but they are grouped undrer different headings. i.e. P&L 1 is using the legal entity organistions grouped by Country, whilst P&L 2 is using the same legal entity organisations but grouped by business type. If I use list item subsets for the line items i lose the formatting and the ability to insert formulae. Basically I need to be able to input data in P&L 1 at a Branch level, which can then be added to at a Head Office leve for **** adjustmentsl, and can then be viewed at a Head Office level under the two Organizational groupings thast I mentioned earlier. Any Ideas?

Best Answer

  • alec_judd
    Answer ✓
    Hi Chris,

    If you already have the first module laid out, where you have the data being input into Organization1, you should be able to use another module and the FINDITEM function to view the data using Organization2. 

    1. Add a property to the Organization2 list and call it something like "Find Organization1" and make this formatted as the list Organization1
    2. In the formula bar next to format, insert FINDITEM('Organization1', CODE(ITEM('Organization2')))
    *Now if you go to the grid view of Organization2, you should see in that property that it is matching the items from Organization1
    3. Create a new module that has the same dimensions as the first module, but instead use Organization2 this time.
    4. Add a line item to this new module (I will call it Amounts)
    5. In the formula of this line item Amounts, enter Organization1 Module.Amounts[LOOKUP: Organization2.'Find Organization 1']
    The syntax for that is the First Module.Line Item[LOOKUP: Alternate Hierarchy.List 1 Property]

    This should allow you to view the data that's being input originally, now using Organization2.

    Let me know if you have any issues, or if I misunderstood at all!



  • Hi Alec - thanks for the quick response. I have tried what you have suggested byt appear to be having some problems with the formula entered against Amounts. I am getting the message that Organization 1.Amounts is not a recognised line item. In my original P&L i have the usual line items and in my P&L 2 i only have the one liine item i.e. Amounts. am I doing something wrong?? Also would you be able to assist on the associated problem I have? Namely I need the Branch to be able to input in the Original P&L, and for the H.O to be able to add FURTHER input to that P&L (which must not be viewed by the Branch) prior to then having the two views available to H.O in bith the original organisation and the alternative organisation.
  • Try substituting 'Amounts' for the name of the line item in the first module that the data sits on.
  • This video gives you an idea of how to use alt hierarchies, such as for reporting.