Hi All,
i'm trying to connect to the Hornbill Reporting API via an R scipt but reresulting in error. Must appreciated if anyone can help this.
Based on the hornbill documentation on BI Reporting (https://wiki.hornbill.com/index.php/PowerBI_Reporting ) I’ve set up the following on a development server:
Power BI Desktop build 2.45.4704.722 64-bit (April 2017)
Microsoft R Open 3.3.3
Installed below packages in R
RCurl
XML
jsonlite
setup a script to run with below parameters
instanceName
instanceZone
apiKey
When I try to run the R Script in Power BI desktop I get the following error:
Unable to Connect
We Encountered an error while trying to connect
Details: "ADO.NET: R script error.
Loading required package: methods
Loading required package: bitops
* Trying 78.129.173.117...
* Connected to eurapi.hornbill.com (78.129.173.117) port 443 (#0)
* successfully set certificate verify locations:
* CAfile: D:/Program Files/Microsoft/R Open/R-3.4.0/library/RCurl/etc/ca-bundle.crt
CApath: none
* SSL certificate problem: self signed certificate in certificate chain
* Closing connection 0
Error in function (type, msg, asError = TRUE) :
SSL certificate problem: self signed certificate in certificate chain
Calls: invokeXmlmc ... getURL -> curlPerform -> .Call -> <Anonymous> -> fun
Execution halted
"
See below for the R script I’m running.
#Define Instance Details
instanceName = "XXXX
instanceZone = "XXX"
# Define API Key
apiKey = “XXXXX”
# Define Report details
reportID = "121"
reportComment = "A comment to add to the report run"
# Import dependencies
library('RCurl')
library('XML')
# Build XMLMC URL
arrUrl = c("https://",
instanceZone,
"api.hornbill.com/",
instanceName)
xmlmcURL = paste(arrUrl, collapse="")
# invokeXmlmc - take params, fire off XMLMC call
invokeXmlmc = function(url, key, service, xmethod, params)
{
# Build Methodcall
paramsrequest = paste(params , collapse="")
arrRequest = c( "<methodCall service=\"",
service,
"\" method=\"",
xmethod,
"\">",
paramsrequest,
"</methodCall>")
request = paste(arrRequest, collapse="")
# Build Invoke URL
invokeURL = paste(url, "/xmlmc/", service, "/?method=", xmethod, sep="")
# Build Headers
espKeyAuth = paste('ESP-APIKEY ', key, sep="")
requestHeaders = c('Content-Type'='text/xmlmc',
'Cache-control'='no-cache',
'Accept'='text/xml'
# ,'Authorization'=espKeyAuth
)
data = getURL( url = invokeURL,
postfields=request,
httpheader=requestHeaders,
verbose=TRUE)
return(data)
}
# suspendExec - wait for a given number of seconds
suspendExec = function(susSec)
{
p1 = proc.time()
Sys.sleep(susSec)
proc.time() - p1 # The cpu usage should be negligible
}
### Kick off report run, get job ID
# Build XMLMC Request
arrXmlmcParams = c( "<params>",
"<reportId>", reportID, "</reportId>",
"<comment>", reportComment, "</comment>",
"</params>")
reportRunResponse = invokeXmlmc(xmlmcURL, apiKey, "reporting", "reportRun", arrXmlmcParams)
xmltext = xmlTreeParse(reportRunResponse, asText = TRUE,useInternalNodes=T)
runID = unlist(xpathApply(xmltext,'//methodCallResult/params/runId',xmlValue))
reportSuccess = FALSE
if(runID > 0){
reportComplete = FALSE
while(reportComplete == FALSE){
# Wait a second...
suspendExec(1)
# Check status of report
arrXmlmcRequest = c( "<params>",
"<runId>", runID, "</runId>",
"</params>")
xml.request = paste(arrXmlmcRequest , collapse="")
reportRunStatus = invokeXmlmc(xmlmcURL, apiKey, "reporting", "reportRunGetStatus", xml.request)
xmlRunStatus = xmlTreeParse(reportRunStatus, asText = TRUE,useInternalNodes=T)
runStatus = unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/status',xmlValue))
if ( runStatus == "completed" ){
reportCSVLink = unlist(xpathApply(xmlRunStatus,'//methodCallResult/params/reportRun/csvLink',xmlValue))
reportSuccess = TRUE
reportComplete = TRUE
} else if ( runStatus == "failed" ){
reportSuccess = FALSE
reportComplete = TRUE
}
}
}
if(reportSuccess == TRUE) {
# Now go get CSV
# Build Invoke URL
invokeURL = paste(xmlmcURL, "/dav/reports/", reportID, "/", reportCSVLink, sep="")
# Build Headers
espKeyAuth = paste('ESP-APIKEY ', apiKey, sep="")
requestHeaders = c('Content-Type'='text/xmlmc',
'Authorization'=espKeyAuth)
# GET request for report CSV content
reportContent = getURL( url = invokeURL, httpheader=requestHeaders)
## CSV vector in to data frame object
dataframe <- read.csv(textConnection(reportContent))
}
