• Recent
    • Tags
    • Popular
    • Register
    • Login

    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 Mango 5 Documentation Website

    Datapoint/Excel Report

    How-To
    3
    11
    3.5k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • F
      Felicia Luong
      last edited by

      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

      1 Reply Last reply Reply Quote 0
      • JoelHaggarJ
        JoelHaggar
        last edited by

        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.

        F 1 Reply Last reply Reply Quote 0
        • phildunlapP
          phildunlap
          last edited by

          Hi Felicia,

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

          1 Reply Last reply Reply Quote 0
          • F
            Felicia Luong
            last edited by

            Hi Phildunalp,

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

            Regards,
            Felicia

            1 Reply Last reply Reply Quote 0
            • F
              Felicia Luong @JoelHaggar
              last edited by

              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

              1 Reply Last reply Reply Quote 0
              • phildunlapP
                phildunlap
                last edited by

                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;
                F 1 Reply Last reply Reply Quote 0
                • F
                  Felicia Luong @phildunlap
                  last edited by

                  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.

                  1 Reply Last reply Reply Quote 0
                  • phildunlapP
                    phildunlap
                    last edited by 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?
                    1 Reply Last reply Reply Quote 0
                    • F
                      Felicia Luong
                      last edited by

                      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

                      1 Reply Last reply Reply Quote 0
                      • phildunlapP
                        phildunlap
                        last edited by

                        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.

                        F 1 Reply Last reply Reply Quote 0
                        • F
                          Felicia Luong @phildunlap
                          last edited by

                          @phildunlap,

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

                          Regards,
                          Felicia

                          1 Reply Last reply Reply Quote 0
                          • First post
                            Last post