Forum Discussion

aspirationalbec's avatar
aspirationalbec
Frequent Visitor
2 years ago
Solved

Filling in hierarchy fields

Hello and thank you in advance for your help.   fields - [mch_code] and [obj_level].   Obj_levels: 1 - contract  2 - site 3 - group barracks 4 - barracks 5 - building  5 - infrastructure 6...
Need Help
  • aspirationalbec's avatar
    aspirationalbec
    2 years ago

    Managed to work it out using a bit of SQL at source.

     

    Posting here in case it helps anyone else:

     

     

    select
    eo.mch_code code,
    eo.operational_status code_operational_status,

    eo1.mch_code room,
    eo1.operational_status room_operational_status,

    eo2.mch_code building,
    eo2.operational_status building_operational_status,

    eo3.mch_code barracks,
    eo3.operational_status barracks_operational_status,

    eo4.mch_code grpbar,
    eo4.operational_status grpbar_operational_status,

    eo5.mch_code site,
    eo5.operational_status site_operational_status from equipment_object eo

    join equipment_object eo1 on eo.SUP_MCH_CODE = eo1.mch_code
    join equipment_object eo2 on eo1.SUP_MCH_CODE = eo2.mch_code
    join equipment_object eo3 on eo2.SUP_mch_code = eo3.MCH_CODE
    join equipment_object eo4 on eo3.SUP_mch_code = eo4.MCH_CODE
    join equipment_object eo5 on eo4.SUP_mch_code = eo5.MCH_CODE

mickey64's avatar
mickey64
Icon for Super User rankSuper User
2 years ago

Please try using a "matrix" instead of a "table".

Rows --> bs_07_room

Columns --> blank

Values --> bs_06_building, bs_05_intra, etc...

 

  • aspirationalbec's avatar
    aspirationalbec
    Frequent Visitor
    2 years ago
    Hi mickey64, thanks for the suggestion but using a matrix wouldn't solve the issue. I need to use the hierarchy in graphs and visuals all over the report. The table was just to check if the columns I had created had worked.