Clarity PPM

Expand all | Collapse all

How to set up OBS in Excel to XOG

Jump to Best Answer
  • 1.  How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:07 AM
    Does anyone have an example of how they set up the obs in excel format?  I am needing help designing the OBS in Excel. After looking at the XOG write (obs_write.xml). I am thinking that the structure should include the  columns of:name, type, level (depth), unit (the departments-lowest level).  We are not concerned with the rights, security at this time, just the structure of the obs. We only have a depth of 5, which is the lowest level.I have XOG'd out the OBS and have also looked at this structure.  Thanks ahead of time. :-)    


  • 2.  Re: How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:07 AM
      Do you want to show OBS hierarchy in excel? or you want a OBS format(columns) in excel that can be exported as OBS structure to Clarity?  Message Edited by sivasairam on 08-06-2009 12:44 PM [left] Message Edited by sivasairam on 08-06-2009 12:46 PM [left]


  • 3.  Re: How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:07 AM
    What I normally do is and Excel sheet with unit name and ID for every level.Then I fill it adding extra rows so that for each parent unit there is a row where all the child level cell as blank.Copy the with formulas to next sheet.Manually delete multiple instances of the parents leaving only the first (with no   children)Select All Copy and paste to another sheet with short name like X. to keep the formulas shorter in the next step.Go to the   next sheet and put formulas which put tags in front and after the values from sheet XSelect All Copy and paste to next sheet which adds the XML header and footer and formulas to add more tags if after a child the parent is more than one level up.Copy to notepad(2). Open in IE Copy back to notepad(2)to remove the vertical tabs Excel graciously inserts at the beginning of each line and invalidates the XML.Visually check the structure in IE.   When OK XOG in. If only small errors do manual move. If large errors delete OBS and go as far back as needed in the process to fix the errors and repeat.When done if this is for a department or location attach to the entity.  Martti K.    


  • 4.  Re: How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:07 AM
    sivasairam  - would that matter, is one better or more manageable  than the other?  We have one already made in excel. I just need to understand how i need to format it. Would this be a good start to the process Martti?   I don't understand the copy of formulas?       Message Edited by furrelkt on 08-06-2009 03:37 PM [left]


  • 5.  Re: How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:07 AM
    Looks OK. If you want to use it for XOG you need name and ID for each unit at each level.Looking back more closely you do not need to remove the extra parents.  You can do it with the formulas.  E.g.  If the data in cells I12 and J12 on sheet X   is   016223 and   R and D
    and the tags are in cells K1 L1 M1 O1 and P1 of the current sheet

    applying the following formula

    =IF(X!I12 "";"                                                   "&K$1&X!I12&M$1&TRIM(X!J12)&P$1;IF(X!G12 "";"                                 "&K$1&X!G12&M$1&TRIM(X!H12)&O$1;IF(X!E12 "";"                     "&K$1&X!E12&M$1&TRIM(X!F12)&O$1;IF(X!C12 "";"               "&K$1&X!C12&M$1&TRIM(X!D12)&O$1; "         "&K$1&X!A12&M$1&TRIM(X!B12)&O$1))))

    gives



    for that leaf child

    If after a leaf child you go up one level to next parent nothing extra is needed
    but if you go up one more level you add an extra

    for each level you skip    Martti K.


  • 6.  RE: Re: How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:08 AM
    Attached is a sample obs excel sheet with 5 levels. the Cell BB has the macro code that you execute to create the xml file.


  • 7.  RE: Re: How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:08 AM
    I do not see your attachment.


  • 8.  RE: Re: How to set up OBS in Excel to XOG

    Posted 10-12-2010 07:08 AM
    I've attached the file.


  • 9.  RE: Re: How to set up OBS in Excel to XOG
    Best Answer

    Posted 10-12-2010 07:08 AM
    I now have MapForce and it works great! I would suggest this to everyone. set up how you want your obs to be (just like the above xls) and map it you will use the .xsd and the xls. easy easy easy.. :-)

    Keri


  • 10.  RE: Re: How to set up OBS in Excel to XOG

    Posted 09-26-2011 12:51 PM

    chrisfol wrote:

    I've attached the file.
    I do not see the attachment. Could you provide. Thanks.