Can you relativise an Excel Report? Driven by WatchList Possibly?

[Wingnut2.0]

Hello,

If I have many like devices with consistent data point naming, is there a way to apply a single excel reporting template to them? Possibly the report could be driven by DataSource/DeviceName?

I am just thinking of the functionality of the Dynamic Watch List and how this could be applied to reporting.

Thank you.

[phildunlap]

Hi Wingnut,

It's a great suggestion but not very easy at the moment. Possible, but not easy.

Here's a scripting data source that's my off-the-cuff attempt to hack it....

var dynamicTemplateXid="XLT_12345";
var foundTemplate = this.foundTemplate;
if(!foundTemplate) {
    var templates = JSON.parse(JsonEmport.getConfiguration("excel-report-templates"))["excel-report-templates"];
    for(var k = 0; k < templates.length; k+=1)
        if(templates[k].xid === dynamicTemplateXid) {
            foundTemplate = this.foundTemplate = templates[k];
        }
}

//For simplicity, I am going to run the report on every watchlist
// somewhat carelessly. Presumably you would want to filter down to just certain
// watchlists. You could encode an array of XIDs here, or track an index in the
// list if you need to break up executing the reports to multiple executions of 
// the script in a data point on the script....

var watchLists = JSON.parse(JsonEmport.getConfiguration("watchLists")).watchLists; //You may also need to check "watchlists"
for(var k = 0; k < watchLists.length; k+=1) {
    var watchList = watchLists[k];
    if(watchList.dataPoints.length < 2)
        continue;
    //Note that this is presuming static watchlists, but we could use the hierarchy instead
    // or try to construct queries. You could do extra work to identify points based
    // on foundTemplate.timeSeries[0].points[k].namedRange
    foundTemplate.timeSeries[0].points[0].pointXid = watchList.dataPoints[0];
    foundTemplate.timeSeries[0].points[1].pointXid = watchList.dataPoints[1];
    foundTemplate.name = "Report - " + watchList.name;
    var importText = JSON.stringify({"excel-report-templates":[foundTemplate]});
    JsonEmport.doImport(importText);
    //print(importText); print("\n\n");
    var reportTemplateVO = com.infiniteautomation.mango.excelreports.dao.ExcelReportTemplateDao.instance.getByXid(dynamicTemplateXid);
    com.infiniteautomation.mango.excelreports.rt.ExcelReportWorkItem.queueReport(reportTemplateVO, null, null, "report-script");
    RuntimeManager.sleep(3000);
}

Tested and working

[Wingnut2.0]

Thank you, Phil. I will try this out shortly.

Your //notes make me think of possibly using json store to build the array. Maybe a simple page displaying watchlists or point hierarchies that the report could be applied to along with a checkbox. The array is then created by adding any checked device?

[phildunlap]

Yeah there are definitely a lot of ways it could work. If they XIDs are programmatic or your fingers have too much sensation left, you can just have a big constant object you loop over....

var configured = {"reportName": ["Xid-for-excelReports.timeSeries[0].points-position-0", "...-position-1"],
....
} //JSON.parse(someAlphanumericPointForStorage.value); //Or yeah, use the JSON store

for(var report in configured) {
  var points = configured[report];
  foundTeplate.name = report;
  for(var k = 0; k < points.length; k+=1)
    foundTemplate.timeSeries[0].points[k].pointXid = points[k];
  /* Do import, launch report, take a break, continue */
}