Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Travis Crane
    Making REST calls via VBA for BI Publisher Reports
    Topic posted February 28, 2019 by Travis CraneBlue Ribbon: 750+ Points, tagged REST 
    181 Views, 4 Comments
    Title:
    Making REST calls via VBA for BI Publisher Reports
    Summary:
    I am trying to make REST calls via VBA for Reports but I do not know how to pass all of the parameters.
    Content:

    I have been able to use VBA for updating Project Team Member Roles (GET, POST, & DELETE).  This works very well in Excel.

    I would like to call Reports via VBA as well using the REST API.  I am able to do this in SOAPUI without any problems, but I don't know all of the code to call the Reports in VBA.  

    The payload below is what I am using in SOAPUI:

    <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:pub="http://xmlns.oracle.com/oxp/service/PublicReportService">
       <soap:Header/>
       <soap:Body>
          <pub:runReport>
             <pub:reportRequest>
    <pub:parameterNameValues>
                    </pub:parameterNameValues>
                <pub:reportAbsolutePath>/Custom/ERP-PSA/Acosta_INT_PPM_Burden_Rates_Report.xdo</pub:reportAbsolutePath>
                <pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>
             </pub:reportRequest>
          </pub:runReport>
       </soap:Body>
    </soap:Envelope>
     
    Below is what I have tried in VBA:
       sEnvelope = "<soap:Envelope xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" xmlns:pub=""http://xmlns.oracle.com/oxp/service/PublicReportService"">"
        sEnvelope = sEnvelope & "<soap:Header/>"
        sEnvelope = sEnvelope & "<soap:Body>"
        sEnvelope = sEnvelope & "<pub:runReport>"
        sEnvelope = sEnvelope & "<pub:reportRequest>"
        sEnvelope = sEnvelope & "<pub:reportAbsolutePath>/Custom/ERP-PSA/Acosta_INT_PPM_Burden_Rates_Report.xdo</pub:reportAbsolutePath>"
        sEnvelope = sEnvelope & "<pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>"
        sEnvelope = sEnvelope & "</pub:reportRequest>"
        sEnvelope = sEnvelope & "</pub:runReport>"
        sEnvelope = sEnvelope & "</soap:Body>"
        sEnvelope = sEnvelope & "</soap:Envelope>"
        Set objRequest = CreateObject("MSXML2.XMLHTTP")
        str_UsrUrl = ""
        str_UsrUrl = str_Domain & "/xmlpserver/services/rest/v1/reports/Custom%2FERP-PSA%2FAcosta_INT_PPM_Burden_Rates_Report"
    '    str_UsrUrl = str_Domain & "/xmlpserver/services/ExternalReportWSSService"
        blnAsync = False
        With objRequest
            .Open "POST", str_UsrUrl, blnAsync
            .SetRequestHeader "Content-Type", "application/json"
    '        .SetRequestHeader "Content -Disposition", "form-data; name=" ""ReportRequest"
            .SetRequestHeader "Authorization", "Basic " & credentials
            .send
            strResponse = .responseText
            MsgBox "Response: " & strResponse
     
    I've searched everywhere trying to piece this code together and I am not having much success.
    The Response that I am getting is:

    <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
    <html><head>
    <title>404 Not Found</title>
    </head><body>
    <h1>Not Found</h1>
    <p>The requested URL /xmlpserver/services/rest/v1/reports/Custom/ERP-PSA/Acosta_INT_PPM_Burden_Rates_Report was not found.</p>
    </body></html>

     
    Any help with this would be great.
     
    Thanks!
    Code Snippet:

    Comment

     

    • Travis Crane

      I am very thankful that I was able to finally figure this out...The Lord was merciful!

      Note that your report Layout Default Format needs to be Data (XML) and then the response will need to be decoded.  I have used https://www.base64decode.org/ to decode outside of the VBA for testing.

          sEnvelope = "<soap:Envelope xmlns:soap=""http://www.w3.org/2003/05/soap-envelope"" xmlns:pub=""http://xmlns.oracle.com/oxp/service/PublicReportService"">"
          sEnvelope = sEnvelope & "<soap:Header/>"
          sEnvelope = sEnvelope & "<soap:Body>"
          sEnvelope = sEnvelope & "<pub:runReport>"
          sEnvelope = sEnvelope & "<pub:reportRequest>"
          sEnvelope = sEnvelope & "<pub:parameterNameValues>"
          sEnvelope = sEnvelope & "</pub:parameterNameValues>"
          sEnvelope = sEnvelope & "<pub:reportAbsolutePath>/Custom/NameOf_Report.xdo</pub:reportAbsolutePath>"
          sEnvelope = sEnvelope & "<pub:sizeOfDataChunkDownload>-1</pub:sizeOfDataChunkDownload>"
          sEnvelope = sEnvelope & "</pub:reportRequest>"
          sEnvelope = sEnvelope & "</pub:runReport>"
          sEnvelope = sEnvelope & "</soap:Body>"
          sEnvelope = sEnvelope & "</soap:Envelope>"
          
          Set objRequest = CreateObject("MSXML2.XMLHTTP")
          With objRequest
              .Open "POST", "https://YourDomain.fs.us2.oraclecloud.com:443/xmlpserver/services/ExternalReportWSSService", False
              .SetRequestHeader "Content-Type", "application/soap+xml" 
              .SetRequestHeader "Authorization", "Basic " & credentials
              .SetRequestHeader "Content-Length", "1579"
              .send (sEnvelope)
       
              strResponse = .responseText
              MsgBox "Response: " & strResponse      
              Sheets("TeamMember").Cells(4, 13).Value = strResponse
       
      Hope this helps someone out there.
    • Darshit Chauhan

      Hi Travis,

      Thank you for your post.

      I am wondering if we can use excel vba to do bulk password reset using REST API. Do you have any insights on this if it's possible and how?

       

      Thanks,

      Darshit

      • Travis Crane

        I have not tested that but I know that you can add roles via excel vba so I would assume that if there is a REST API for password resets then it would work.

        • Darshit Chauhan

          Great. Do you have any link/page which I can refer to for creating vba code for role assignment? May be that would help me create similar code for password reset.

           

          Many thanks in advance!

          Regards,

          Darshit