Changing the Excel report name

[srashid]

How can i change the name of the excel report file?

[srashid]

@srashid actually I shoud say, Can i get rid of the time stamp from the name of the file?

[phildunlap]

Hi srashid,

The timestamp is in the filename so that running the same report multiple times does not result it overwriting the file. Are you wishing that each run of the file would overwrite the existing report, and that it be named with the report name?

[srashid]

@phildunlap In the file name, it shows me date_time_name of the template. i wanted to get rid of the time stamp. and keep only the date and template name.

[phildunlap]

Ah okay, so it's a daily report I would guess. You would have to do this in a script and have an SQL point that updates table only in the context

var basePath = "/path/to/Mango";
var reportsDirectory = new java.io.File(basePath + "/web/modules/excelReports/web/report-data");
var reports = reportsDirectory.listFiles();
for(var k = 0; k < reports.length; k+=1) {
    var filenameInfo = /(.*?__20\d\d)[^.]+(\.xlsx?)/.exec(reports[k].getName());
    if(filenameInfo !== null) {
        var newFilename = filenameInfo[1] + filenameInfo[2];
        sqlPoint.set("(" + newFilename +","+ reports[k].getName()+")");
        var newFile = new java.io.File(basePath + "/web/modules/excelReports/web/report-data/" + newFilename);
        java.nio.file.Files.move(reports[k].toPath(), newFile.toPath());
    }
}

Here's the JSON for my scripting data source and my SQL data source.

{
   "dataSources":[
      {
         "xid":"DS_de7b7e71-f677-4020-ae30-3880c0c7402d",
         "name":"Rename Reports",
         "enabled":false,
         "type":"SCRIPTING",
         "alarmLevels":{
            "SCRIPT_ERROR":"URGENT",
            "DATA_TYPE_ERROR":"URGENT",
            "POLL_ABORTED":"URGENT",
            "LOG_ERROR":"URGENT"
         },
         "purgeType":"YEARS",
         "updateEvent":"CONTEXT_UPDATE",
         "context":[
            {
               "varName":"sqlPoint",
               "dataPointXid":"DP_f527c6d3-9b10-44aa-b72c-9c21aaf6b605",
               "updateContext":false
            }
         ],
         "logLevel":"NONE",
         "cronPattern":"0 5 0\/1 * * ?",
         "executionDelaySeconds":0,
         "historicalSetting":false,
         "logCount":5,
         "logSize":1.0,
         "script":"var basePath = \"\/path\/to\/Mango\";\nvar reportsDirectory = new java.io.File(basePath + \"\/web\/modules\/excelReports\/web\/report-data\");\nvar reports = reportsDirectory.listFiles();\nfor(var k = 0; k < reports.length; k+=1) {\n    var filenameInfo = \/(.*?__20\\d\\d)[^.]+(\\.xlsx?)\/.exec(reports[k].getName());\n    if(filenameInfo !== null) {\n        var newFilename = filenameInfo[1] + filenameInfo[2];\n        sqlPoint.set(\"(\" + newFilename +\",\"+ reports[k].getName()+\")\");\n        var newFile = new java.io.File(basePath + \"\/web\/modules\/excelReports\/web\/report-data\/\" + newFilename);\n        java.nio.file.Files.move(reports[k].toPath(), newFile.toPath());\n    }\n}",
         "scriptPermissions":{
            "customPermissions":"",
            "dataPointReadPermissions":"superadmin",
            "dataPointSetPermissions":"superadmin",
            "dataSourcePermissions":"superadmin"
         },
         "editPermission":"",
         "purgeOverride":false,
         "purgePeriod":1
      },
      {
         "xid":"DS_9fdb6029-cf67-4ed4-8372-1d6e7f304cc5",
         "name":"UpdateReportNames",
         "enabled":true,
         "type":"SQL",
         "alarmLevels":{
            "POLL_ABORTED":"URGENT",
            "STATEMENT_EXCEPTION":"URGENT",
            "DATA_SOURCE_EXCEPTION":"URGENT"
         },
         "purgeType":"YEARS",
         "updatePeriodType":"HOURS",
         "connectionUrl":"jdbc:h2:C:\\IA\\Mangoes\\enterprise-m2m2-core-3.5.0-2\\databases\\mah2",
         "driverClassname":"org.h2.Driver",
         "password":"",
         "rowBasedQuery":true,
         "selectStatement":"select '' from systemSettings",
         "updatePeriods":5,
         "username":"",
         "editPermission":"",
         "purgeOverride":false,
         "purgePeriod":1
      }
   ],
   "dataPoints":[
      {
         "xid":"DP_f527c6d3-9b10-44aa-b72c-9c21aaf6b605",
         "name":"UpdateReportName",
         "enabled":true,
         "loggingType":"ON_CHANGE",
         "intervalLoggingPeriodType":"MINUTES",
         "intervalLoggingType":"INSTANT",
         "purgeType":"YEARS",
         "pointLocator":{
            "dataType":"ALPHANUMERIC",
            "parameters":[
               "STRING",
               "STRING"
            ],
            "dateParameterFormat":"yyyy-MM-dd'T'HH:mm:ss",
            "fieldName":"",
            "tableModifier":true,
            "timeOverrideName":"",
            "updateStatement":"update excelReports set filename='?' where filename='?'"
         },
         "eventDetectors":[
         ],
         "plotType":"STEP",
         "rollup":"NONE",
         "unit":"",
         "templateXid":"Alphanumeric_Default",
         "simplifyType":"NONE",
         "chartColour":"",
         "chartRenderer":{
            "type":"TABLE",
            "limit":10
         },
         "dataSourceXid":"DS_9fdb6029-cf67-4ed4-8372-1d6e7f304cc5",
         "defaultCacheSize":1,
         "deviceName":"UpdateReportNames",
         "discardExtremeValues":false,
         "discardHighLimit":1.7976931348623157E308,
         "discardLowLimit":-1.7976931348623157E308,
         "intervalLoggingPeriod":15,
         "intervalLoggingSampleWindowSize":0,
         "overrideIntervalLoggingSamples":false,
         "preventSetExtremeValues":false,
         "purgeOverride":false,
         "purgePeriod":1,
         "readPermission":"",
         "setExtremeHighLimit":1.7976931348623157E308,
         "setExtremeLowLimit":-1.7976931348623157E308,
         "setPermission":"",
         "tags":{
         },
         "textRenderer":{
            "type":"PLAIN",
            "useUnitAsSuffix":true,
            "unit":"",
            "renderedUnit":"",
            "suffix":""
         },
         "tolerance":0.0
      }
   ]
}

I have it running at the fifth minute of each hour, assuming the report is probably generated on some hour. You could work the cron to happen after the report is run.