Datapoint/Excel Report

[Felicia Luong]

Hi,

Please enlighten me how to resolve this issue I had encountered.

I had created a few data points for testing:

• Data point A using Data Source Type = Virtual
• Data point B using Data Source Type = Virtual
• Data point C using Data Source Type = Meta. I had included a formula using Data point A + Data point B

I had used excel reports. The data export had showed me the following data:

Timestamp Data point A Data Point B Data Point C
42748.76389
42748.76458 6.774346083 12.64840074
42748.76528 6.743774728 12.66065828 19.42274682

The sum of Data point A & Data point B in 2nd row is displaced in Data point C but in 3rd row.

I need to resolve:

• the result (data point c) to be displayed in 2nd row
• remove the first blank row

Appreciate you can let me know how to resolve this.

FYI, I had used Rollup function (First 1 Minutes) in Excel Reports.

I suspect that a different timestamp is used for data point C and that timestamp may be a minute after data point A & B.

Thanks.

Regards,
Felicia

[JoelHaggar]

This is just formatting in Excel. Format your date column something like: m/d/yy h:mm:ss and your number columns as numbers with the number of decimals that you want.

The trick to get your formatting to stay in the template is to put some data into the columns in the template file formatted the way you want. The data will be overwritten with the new data and the format should stay the same.

[phildunlap]

Hi Felicia,

Have you checked that the named ranges in the Excel template begin in the 2nd or 1st row?

[Felicia Luong]

Hi Phildunalp,

I had changed previously checked the named ranges and they are all correct.

Regards,
Felicia

[Felicia Luong]

Hi Joel Hagger,

I had already done so. I believe is the timestamp issue. To be more specific, I will illustrate with an example since my previously email is not clear.

Data point A (Virtual) Timestamp is 21:01:30
Data point B (Virtual) Timestamp is 21:01:30
Data point C (Meta) Timestamp is 21:02:00. This may be due to the fact that this is a formula using Data point A add Data point B and there is a delay. Therefore, the timestamp is 1 minute later than Data point A & B.

How can I make Data point C Timestamp to be not later than 21:01:59 so that the 3 data can be displayed as 1 row?

Thanks.

Regards,
Felicia

[phildunlap]

Hi Felicia,

Your Meta point is something like,

return a.value + b.value;

You can ensure it has the same timestamp as a point by changing that to:

TIMESTAMP = a.time;
return a.value + b.value;

[Felicia Luong]

Hi Phildunlap,

I had used the stated formula given but the issue still continues. Perhaps there is something missing in my script somewhere. Below is my script for meta source datapoint COHRCP:

Timestamp = COWSFS_FM_C01.value;
return COWSFS_FM_C01.value * 1;

Please check if my script is correct. If it is correct, is there anything else I need to check to make sure the script works?

Thanks.

[phildunlap]

Hi Felicia,

Hmm. That doesn't seem like you are using the script I gave, which addressed the problem put forth in your previous post. Here's more of what it should look like...

TIMESTAMP = COWSFS_FM_C01.time;
return COWSFS_FM_C01.value * 1; //Why?

[Felicia Luong]

Hi Phildunlap,

Just realized my mistake. I had amended according to your screen. The result still the same. The time is not the same as COWSFS_FM_C01. It is out by 20 seconds. Is this the norm? Possible to be the exact per COWSFS timestamp?

The result as follows:
COWSFS_FM_C01 16:57:56
COHRCP 16:57:36

Regards,
Felicia

[phildunlap]

Hi Felicia,

Please ensure TIMESTAMP is all capital letters and not declared with var. Perhaps you should post your script again?

What version of the Core and Meta modules are you using? Are you using Java 7 or 8? I have just retested this, and it works in both Java 7 and 8 in the latest versions. Here's the JSON for my test:

{
   "dataSources":[
      {
         "xid":"DS_0637742",
         "name":"vrt",
         "enabled":true,
         "type":"VIRTUAL",
         "alarmLevels":{
            "POLL_ABORTED":"URGENT"
         },
         "purgeType":"YEARS",
         "updatePeriodType":"SECONDS",
         "updatePeriods":5,
         "editPermission":"",
         "purgeOverride":false,
         "purgePeriod":1
      },
      {
         "xid":"DS_970722",
         "name":"timestamped",
         "enabled":true,
         "type":"META",
         "alarmLevels":{
            "SCRIPT_ERROR":"URGENT",
            "CONTEXT_POINT_DISABLED":"URGENT",
            "RESULT_TYPE_ERROR":"URGENT"
         },
         "purgeType":"YEARS",
         "editPermission":"",
         "purgeOverride":false,
         "purgePeriod":1
      }
   ],
   "dataPoints":[
      {
         "xid":"DP_093226",
         "name":"timestamped",
         "enabled":true,
         "loggingType":"ON_CHANGE",
         "intervalLoggingPeriodType":"MINUTES",
         "intervalLoggingType":"INSTANT",
         "purgeType":"YEARS",
         "pointLocator":{
            "dataType":"ALPHANUMERIC",
            "updateEvent":"CONTEXT_UPDATE",
            "context":[
               {
                  "updateContext":true,
                  "dataPointXid":"DP_867849",
                  "varName":"ts"
               }
            ],
            "logLevel":"NONE",
            "executionDelaySeconds":0,
            "script":"var parsedInfo = \/(.*),(\\d+)-(\\d+)-(\\d+)\/.exec(ts.value);\nif(parsedInfo.length < 4)\n  throw \"Where's my info?!?!\";\nvar dt = new Date(parsedInfo[4], parsedInfo[3], parsedInfo[2]);\nTIMESTAMP=dt.getTime();\nreturn parsedInfo[1];",
            "scriptPermissions":{
               "customPermissions":"",
               "dataPointReadPermissions":"superadmin",
               "dataPointSetPermissions":"superadmin",
               "dataSourcePermissions":"superadmin"
            },
            "settable":false,
            "updateCronPattern":""
         },
         "eventDetectors":[
         ],
         "plotType":"STEP",
         "unit":"",
         "templateXid":"Alphanumeric_Default",
         "chartColour":"red",
         "chartRenderer":{
            "type":"TABLE",
            "limit":10
         },
         "dataSourceXid":"DS_970722",
         "defaultCacheSize":1,
         "deviceName":"timestamped",
         "discardExtremeValues":false,
         "discardHighLimit":1.7976931348623157E308,
         "discardLowLimit":-1.7976931348623157E308,
         "intervalLoggingPeriod":15,
         "intervalLoggingSampleWindowSize":0,
         "overrideIntervalLoggingSamples":false,
         "purgeOverride":false,
         "purgePeriod":1,
         "readPermission":"",
         "setPermission":"",
         "textRenderer":{
            "type":"PLAIN",
            "useUnitAsSuffix":true,
            "unit":"",
            "renderedUnit":"",
            "suffix":""
         },
         "tolerance":0.0
      },
      {
         "xid":"DP_867849",
         "name":"timestamper",
         "enabled":true,
         "loggingType":"ON_CHANGE",
         "intervalLoggingPeriodType":"MINUTES",
         "intervalLoggingType":"INSTANT",
         "purgeType":"YEARS",
         "pointLocator":{
            "dataType":"ALPHANUMERIC",
            "changeType":{
               "type":"NO_CHANGE",
               "startValue":""
            },
            "settable":true
         },
         "eventDetectors":[
         ],
         "plotType":"STEP",
         "unit":"",
         "templateXid":"Alphanumeric_Default",
         "chartColour":"red",
         "chartRenderer":{
            "type":"TABLE",
            "limit":10
         },
         "dataSourceXid":"DS_0637742",
         "defaultCacheSize":1,
         "deviceName":"vrt",
         "discardExtremeValues":false,
         "discardHighLimit":1.7976931348623157E308,
         "discardLowLimit":-1.7976931348623157E308,
         "intervalLoggingPeriod":15,
         "intervalLoggingSampleWindowSize":0,
         "overrideIntervalLoggingSamples":false,
         "purgeOverride":false,
         "purgePeriod":1,
         "readPermission":"",
         "setPermission":"",
         "textRenderer":{
            "type":"PLAIN",
            "useUnitAsSuffix":true,
            "unit":"",
            "renderedUnit":"",
            "suffix":""
         },
         "tolerance":0.0
      }
   ]
}

Setting timestamper to a value like string,1-2-2016 gives the point timestamped the value string on the date specified.

[Felicia Luong]

@phildunlap,

Thanks, it works! It does make a difference between small capitals and big capitals.

Regards,
Felicia