Joyce

same here

Hi, I am happy to see that we an now schedule report, and possibly date filter with pre-defined settings. eg. 'last Month' etc. However, if i set my 'DateResolved' filter to be between Start of last month and end of last month, I do not get any value. If I put specific date value. ie: 2017-11-30 23:59:59' and '2018-01-01 00:00:00' I get the values. What Am I doing wrong? Thanks. Regards, JOyce

thanks @ArmandoDM

Hi, I am trying to generate a report for all problem tickets, with a list of corresponding linked incidents/Service request. Is there a table with information for 'Linked request'? Thanks. Regards, Joyce

Thanks for the feedback @Victor

Yes so did we, for about 10 mns. seems to be working now

Hi @Victor I now cannot download any of the reports, I am getting the following error when try to open a csv report: Error --- : Array ( => https://eurapi.hornbill.com/vinci/dav/reports/32/Service desk % Incidents resolved within SLT Monthly_1779.csv [content_type] => text/html [http_code] => 404 [header_size] => 468 [request_size] => 242 [filetime] => -1 [ssl_verify_result] => 0 [redirect_count] => 0 [total_time] => 0.024697 [namelookup_time] => 0.004324 [connect_time] => 0.005227 [pretransfer_time] => 0.019288 [size_upload] => 0 [size_download] => 0 [speed_download] => 0 [speed_upload] => 0 [download_content_length] => -1 [upload_content_length] => 0 [starttransfer_time] => 0.024613 [redirect_time] => 0 [certinfo] => Array ( ) [primary_ip] => 172.16.1.22 [primary_port] => 443 [local_ip] => 172.16.1.34 [local_port] => 45216 [redirect_url] => ) 0

I think I found out what the problem is, the script is looking for HTML file to download to Power BI, but now on hornbill we only create PDF and CSV Is there a way to get the HTML format report again?

Thanks @TrevorKillick It now take less time to run, but more than one report run is somehow happening , with the R script to connect to Power BI, and still doesn't download the update on Power BI.

The above file is smaller than the below, but it is taking too long to run:

Something has change on Hornbill, because the same report which took 1 -3 second to run last month, is taking 30 sec to run now!!, I am real struggling to get them to refresh on Power BI because of this. @Steve G

Hi Steve, Changing of the time above, doesn't seems to work with existing report. I keep getting, 'edit permission' and when I click run, it just keeps on loading. I have even reduce the size of my report. but it still taking too long and end up timing out. Does the example you provided above, in seconds or minutes? What value do I put for this example? is it '80' for 80 seconds?

Hi, I have been using Counter list items, for some of our teams reports. So when someone leaves, i have to remove them from the list , and add a new person. Now when deleting one counter item, it seems to delete all other counter items below it: Before: After Can this be rectify to only delete the required Counter list? Also is there a way to sort them by name alphabetical order? Thanks. Regards, Joyce

While I have got Steve G attention here, can I use the above script to connect from Excel PowerPivot? which data source query from below should I use?

Great thanks both @Victor and @Steve G the work around is working great.

Hi, Try to connect to a Report from Power BI R Script as follows: #PowerBIDataSource_Report.R #Define Instance Details instanceName = "vinci" instanceZone = "eur" # Define API Key apiKey = "<removed for security purposes>" # Define Report details reportID = "71" 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 ,ssl.verifypeer = FALSE) 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,ssl.verifypeer = FALSE) ## CSV vector in to data frame object dataframe <- read.csv(textConnection(reportContent)) } The connection takes too long, then timeout. My report is less than 1MB so not very big, and it seems to run, but struggle to download. Help please. Thanks. Regards, Joyce

Thanks Bob. I did the above, just getting the count, then average it outside on a piece of paper. I will try your sql query above and see if it works. Thanks for your assistance.

My query clause is just selecting request type (h_requesttype='Service Request'). I have also try adding r_Reopencount !=0, but |I am still getting 0 average. The r_reopencount, is counting number of times a ticket has been reopen ( so one ticket can be reopen more than once). The average I am after is of number of Service request re-openned monthly

@Bob Dickinson

Hi I am trying to create a measure for the following: Average number of tickets reopened per month Average Number of tickets resolved per month I have set up An average measure, as below, but I am getting 0 values: What am I doing wrong? Thanks

Its working now, thanks

Error message: 'chart type not supported'' Before starting to try to load, then it is' Loading charts please wait'

Hi, We are getting an error when try to view dashboards or Widget charts? Keep loading 'loading charts please wait' Can someone look into this ASAP please? Thanks. Joyce

Yes, it is working again. Thanks @TrevorKillick