Export Project List from Clarity PPM To Excel

Would anyone know how to get list of project from Clarity PPM to excel using the SOAP Interfaces (XOG PPM) and VBA (Excel) ?

I have been able to use the interface to log successfully but cannot go further...XML documentation is not clear enough for me.

Here is how I could Log and Get Session ID

Sub Soap_LOGIN()
' RKO
' add MS XML v6 reference : tool/Reference

 'Set and instantiate our working objects
    Dim Req As Object
    Dim sEnv As String
    Dim Resp As New MSXML2.DOMDocument60
    Set Req = CreateObject("MSXML2.XMLHTTP")
    Set Resp = CreateObject("MSXML2.DOMDocument.6.0")
    Req.Open "Post", "http://SERVER/niku/xog", False
    Req.setRequestHeader "Content-Type", "text/plain; charset=UTF-8"
    Req.setRequestHeader "soapaction", "http://www.niku.com/xog/Query/Login" ' per the documentation
    Req.setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"
    Req.setRequestHeader "Connection", "keep -alive"



 ' we create our SOAP envelope for submission to the Web Service
     sEnv = sEnv & "<?xml version=""1.0"" encoding=""utf-8""?>"
     sEnv = sEnv & "<soap:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">"
     sEnv = sEnv & " <soap:Body>"
     sEnv = sEnv & "  <Login xmlns=""http://www.niku.com/xog/Query"">"
     sEnv = sEnv & "   <TenantID>Clarity</TenantID>"
     sEnv = sEnv & "   <Username>XXXX</Username>"
     sEnv = sEnv & "   <Password>YYYY</Password>"
     sEnv = sEnv & "  </Login>"
     sEnv = sEnv & " </soap:Body>"
     sEnv = sEnv & "</soap:Envelope>"
     MsgBox sEnv


' Send SOAP Request
    Req.send (sEnv)

' Display results in MessageBox
    'MsgBox Req.responseText
    Resp.LoadXML Req.responseText
    MsgBox (Req.responseText)
    MsgBox Resp.Text '<--- This is the Session ID


  'clean up code
    Set Req = Nothing
    Set Resp = Nothing



End Sub

If you have managed to login and get a session ID then you are almost there. You can create a SOAP request to request the projects:

<soap:invoke endpoint="${XOGURL}/niku/xog" var="xogoutput">
    <soap:message>
        <soap-env:Envelope>
            <soap-env:Header>
                <xog:Auth>
                    <xog:SessionID>SESSIONID</xog:SessionID>
                </xog:Auth>
            </soap-env:Header>
            <soap-env:Body>
                <XOGREQUEST/>
            </soap-env:Body>
        </soap-env:Envelope>
    </soap:message>
</soap:invoke>

In the above replace SESSIONID with your session ID. Then replace with your XML read request for your projects. An example of this would be:

<NikuDataBus xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../xsd/nikuxog_read.xsd">
  <Header version="6.0.11" action="read" objectType="project" externalSource="NIKU">
  </Header>
  <Query>
      <Filter name="projectID" criteria="EQUALS">test</Filter>
  </Query>
</NikuDataBus>

Replace test with the ID of the project you want back. Depending on your Clarity configuration this could return a large amount of data, just for one project. In the Clarity XOG and Integration guide it will show how to extend the above filter to get more projects. Alternatively in any Clarity documentation download the XOG client to get the sample project_read.xml from the xml examples folder.

One you get your projects back you will need to process the XML data. That could be quite a complex task.

If you just want a list of projects a simpler mechanism may be to write the SQL within MS Excel (Menu/Data/From Otehr sources/SQL) and use an ODBC connection to connect direct to the Clarity database.

Other alternatives would be to write a report (if you use SQL Server, then you could use SSRS for this).

A different approach and best used for simple integrations might be to write a GEL script on the Clarity server to extract your project list and to copy the results into a CSV file on an FTP server.