Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website

  • Excel Reports – Is there a way to run the reports on a trigger rather than time/date?


  • You can't currently do this but we did add in the infrastructure to allow Modules to create their own event handlers so there is a plan to add in an event handler to trigger a report. We will probably add this in for Mango 3.0.


  • Thanks Joel


  • Hi Larry,

    Excel reports does have API endpoints to run a report, so it would be possible to write a script to invoke a report in the API through a process event handler.


  • Phil, Could you give an example?

    Thanks


  • For sure!

    Here's my python script (adapted from this thread: https://infiniteautomation.com/forum/topic/2658/any-generic-guide-to-rest-api-python/3):

    #!/usr/bin/python
    
    import requests
    
    s = requests.Session()
    s.headers.update({'Accept': 'application/json', 'Accept-Encoding': 'gzip,deflate,sdch', 'password': 'asdf'})
    
    #Show session headers
    #print s.headers, "\n\n"
    
    r = s.get('http://localhost/rest/v1/login/reportRunnerUser')
    #r = s.get('http://localhost:8080/rest/v1/login/admin');
    #Show headers sent
    #print r.request.headers, "\n\n"
    
    #Show Login response JSON
    #print r.text, "\n\n"
    
    #Remove password from headers
    del s.headers["password"]
    
    #you may need to set your X-Xsrf-Token header from the cookie, if so uncomment next line.
    #s.headers["X-Xsrf-Token"] = s.cookies["XSRF-TOKEN"]
    
    #Trigger the report
    r = s.get('http://localhost/rest/v1/excel-reports/run/triggeredReportXid')
    #print r.text, "\n\n"
    
    r = s.get('http://localhost/rest/v1/logout')
    

    So I...

    1. Created a user named 'reportRunnerUser' who has password 'asdf' and permissions 'reportRunner'
    2. Added 'reportRunner' to the edit permission field of the Excel Report, whose xid is 'triggeredReportXid'
    3. Created a process event handler on my point that had an active command of python /home/mango/testRunExcelReport.py

    Here's a much more complex script I wrote a while ago to run some set of reports on individual days for the past year or so (for inspirational purposes only):

    import requests 
    import datetime
    import json
    import time
    from StringIO import StringIO
    import re
    
    class TZ(datetime.tzinfo) :
    	def utcoffset(self, dt): return datetime.timedelta(hours=8)
    class UTC(datetime.tzinfo) :
    	def utcoffset(self, dt): return datetime.timedelta(hours=0)
    
    baseUrl = "http://10.10.10.49"
    excelReport = 'knownXid'
    adminPassword = 'admin'
    startDate = datetime.datetime(2017, 1, 17, tzinfo=TZ())
    endDate = datetime.datetime(2017, 1, 19, tzinfo=TZ())
    timestep = datetime.timedelta(days=1)
    
    s = requests.Session()
    headers = {'Accept': 'application/json', 
    	'Accept-Encoding': 'gzip,deflate,sdch', 
    	'password': adminPassword, 
    	"User-Agent": "Mozilla/5.0 (Windows NT 6.3; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/54.0.2840.71 Safari/537.36", 
    	"Host": "10.10.10.49", 
    	"Referer": "http://10.10.10.49/swagger",
    	"Accept-Language":"en-US,en;q=0.8",
    	"Pragma": "no-cache",
    	"Cache-Control": "no-cache",
    	"Connection": "keep-alive"}
    
    r = s.get(baseUrl + '/rest/v1/login/admin', headers=headers)
    #print "Response headers: ", r.headers, "\n\n"
    
    token = re.search("XSRF-TOKEN=([^; ]+)", r.headers["set-cookie"])
    headers['Cookie'] = re.sub("Path=/", "", r.headers["set-cookie"])
    headers["Cookie"] = re.sub(";,", ";", headers["Cookie"][:-1])
    headers["X-Xsrf-Token"] = token.group(1)
    headers["Content-Type"] = "application/json"
    del headers["password"]
    #print "\nPOST AUTH HEADERS: \n", headers, "\n\n"
    #get the list of templates
    r = s.get(baseUrl + '/rest/v1/excel-report-templates', headers=headers)
    #print "\n QUERY REPORTS GOT RESPONSE:\n", r.text, "\n\n"
    response = json.load(StringIO(r.text))
    excelReportObj = None
    for er in response["items"] :
    	if er["xid"] == excelReport :
    		excelReportObj = er 
    if excelReportObj is None :
    	print "Ah nuts, couldn't find the excel report specified."
    	exit() 
    
    #print json.dumps(excelReportObj)
    epoch = datetime.datetime(1970, 1, 1, tzinfo=UTC())
    def setReportForDate(dt) :
    	dt1 = int((dt-epoch).total_seconds()*1000)
    #	print "dt1: ", dt1
    	excelReportObj["timeSeries"][0]["startTimestamp"] = dt1
    	dt2 = int(((dt + timestep) - epoch).total_seconds()*1000)
    #	print "dt2: ", dt2
    	excelReportObj["timeSeries"][0]["finishTimestamp"] = dt2
    #	print "Report configured for start: %d  || and end: %d" % (excelReportObj["timeSeries"][0]["startTimestamp"],excelReportObj["timeSeries"][0]["finishTimestamp"])
    #	print json.dumps(excelReportObj)
    	return excelReportObj
    
    reportCount = 0
    while startDate < endDate :
    	r = s.put(baseUrl + '/rest/v1/excel-report-templates/%d'%excelReportObj["id"], data=json.dumps(setReportForDate(startDate)), headers=headers)
    #	print "\n", r.text, "\n\n"
    	startDate = startDate + timestep
    	s.get(baseUrl + '/rest/v1/excel-reports/run/%s'%excelReportObj["xid"], headers=headers)
    	reportCount += 1
    	time.sleep(1)
    
    print "Queued and ran %d reports consider running 'scp Mango/web/modules/excelReports/web/report-data/* C:\\ReportData\\" %(reportCount,)
    

  • Thanks so much Phil I'll give it a try.