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.

Hi gquiroz,

I know this thread is from 3.2, but I think it may still work okay in 2.8.8: https://forum.infiniteautomation.com/topic/3099/bacnet4j-3-2-and-bbmd-foreign-device-support

You could probably also send the WhoIS to just that device by changing the broadcast address to that device's address in your Local device settings on the system settings page. That wouldn't quite be the same as sending a foreign device registration explicitly, though.

Hi phildunlap, apparently MYSQL v8 came with a security update that prevents many third party applications from accessing the server.
Authentication plugin 'caching_sha2_password' cannot be loaded
I could not find a fix for this, other than just downgrading to an older version.
I will create a new post regarding the BACnet question: https://forum.infiniteautomation.com/topic/4042/adding-a-bacnet-router-as-a-foreign-device

@phildunlap Hi Phildunlop,

I haven't set any rollup. Rollup is none

Here's another way of doing it that will only fetch the values for the selected point. The buttons also highlight to show which point is selected.

<!-- set the initial data point XID for when the page loads --> <div ng-init="dataPointXid = 'temperature'"></div> <ma-get-point-value point-xid="{{dataPointXid}}" point="selectedPoint"></ma-get-point-value> <md-button ng-click="dataPointXid = 'temperature'" ng-class="{'md-raised': dataPointXid === 'temperature'}">Data point 1</md-button> <md-button ng-click="dataPointXid = 'voltage'" ng-class="{'md-raised': dataPointXid === 'voltage'}">Data point 2</md-button> <ma-point-values point="selectedPoint" values="pointValues" from="dateBar.from" to="dateBar.to" rollup="{{dateBar.rollupType}}" rollup-interval="{{dateBar.rollupIntervals}} {{dateBar.rollupIntervalPeriod}}"> </ma-point-values> <ma-serial-chart style="height: 300px; width: 100%" series-1-values="pointValues" series-1-point="selectedPoint"> </ma-serial-chart>

0_1548952726907_cca593a7-6577-4b09-b4b7-70066a9d8e9a-image.png

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.

@psysak said in Calculating values on the fly:

Is the
{xid: 'cost'}

an implied search term for xid which contains "cost"?

No. This is object literal syntax - https://developer.mozilla.org/en-US/docs/Web/JavaScript/Guide/Grammar_and_types#Object_literals

By using points="[power, price, {name: 'Cost', xid: 'cost', deviceName: 'Cost'}]" I am creating an array containing the two points stored in the scope as power and price, and creating a third object in the array with its xid property set to the string cost.

@psysak said in Chrome on a MacBook:

I just had this issue pop up in page editor as well. However, I've also realized that everything works perfectly fine in FireFox so if I have any issues I just open that up.

If it is happening on the dashboard designer I would also expect it to affect the page editor too. They use the exact same ace editor component. Chrome should work 100% perfectly, the fact that you are still experiencing issues with this indicates there is something amiss with your installation. Please contact me via private message / chat and I'll get you to screen share and we can get to the bottom of it.

You also commented that you were experiencing this issue which was patched long ago.

@psysak said in Dynamic Excel reports?:

The thing with the excel report is formatting, as trivial as it may sound I don't want my clients just downloading raw csv files. I need our logo, proper formatting with bolding, some equations to run etc.

Ah, why didn't you say so sooner.
Seeing as you dont need them to 'access' a raw file per se. why not just generate a pdf file to encapsulate it instead?? Do you anticipate that they will need to use the data for their own graphing/charting in excel?? If not, this is also a viable solution. Works everywhere, can generate the layout and whatnot that you need in html and make it open in a new page for them to download or just download outright. There are numerous javascript libraries available. Doing it this way will also enable you to append chart images on the page as well rather than just numbers using amcharts's export api.

Let me know your thoughts.

EDIT: I have come across this library if you would still rather use excel. Whether or not it will do what you want I'm not certain.

https://github.com/SheetJS/js-xlsx however at first glance I'm not seeing anything resembling image support....

You could also just stick something on your page to reset the date range if they try -

<ma-calc input="dateBar.from" on-change="startOfMonth = ('now' | maMoment:'startOf':'month'); $value > startOfMonth && (dateBar.from = startOfMonth.toDate())"></ma-calc> <ma-calc input="dateBar.to" on-change="startOfMonth = ('now' | maMoment:'startOf':'month'); $value > startOfMonth && (dateBar.to = startOfMonth.toDate())"></ma-calc>

Ohh yes, this is where I got confused! I haven't seen that options on the Alarm level combo. All clarified now!
I found in the forum that I can export data from any table to another external DB, so I can keep the events shorter in H2. I will make an effort to keep the structure with H2 DB because the management through Mango seems simplier.
Thanks a lot for all the info.

I forgot to inquire if you have set memory settings for this machine? Perhaps you can try allocating ~70% of the memory explicitly to Java via moving an appropriate memory script from Mango/bin/ext-available into Mango/bin/ext-enabled, modifying it to your system's resources, and stopping, then starting (not restarting) Mango to have it come into effect.

I would unzip the backup and check the SQL file inside for DATA4. That's what I did in the other thread when he sent in his backup and I didn't find any references. I restored it and didn't encounter the issue. I would advise that you restore the backup into a clean database to check it out (move Mango/databases when Mango is off so that it will start on a new database) and perhaps keep a JSON configuration export and output of SELECT id, xid FROM dataPoints; on hand, in case you need to restore via JSON rather than the database backup.

This will be a handy post for those who are running cloud hosted solutions as there isn't a mangoES settings section for custom installs.

Hi Ian,

Quite strange. You could try launching it at the command line. Sometimes, especially in older versions, there were some errors that would make it to stderr and avoid the log file.

@phildunlap said in How do Excel reports handle no datapoints:

g the value

Thanks guys, my curiosity has been satisfied.

Yes but only one ES is publishing to this server with one connection so why is it showing 4 connections from the one server? If I stop and restart it it goes back to one connection and then over the next hour it grows to multiple connections.

0_1547752530368_fc09d4de-94de-442d-9bbd-3123f474c387-image.png

Whenever a publisher or data source is saved, it is stopped and then started. You must save your changes for them to be in effect.

You are correct!

Only IP networks use BBMDs

A local device can currently only register as a foreign device with one BBMD. You could try creating more LocalDevices and experimenting with using other port numbers. This has come up a few times fairly recently and we're likely to do some work making it easier in the future. See this thread: https://forum.infiniteautomation.com/topic/3798/support-multiple-bbmd-routers-networks-over-1-nic

@jleblancmango It sounds like the contents of your main div are overflowing or something like that. Post your markup / screenshot.

Has anything changed with Persistent TCP being bi-directional now in 3.5??

Yes. As Fox mentioned there is a setting on the publisher that permits it to accept sets. If,

the "Allow data source to set values back" is enabled on the publisher the original published point is settable it is set as settable in the point created on the receiver and the user has set permission to the point on the receiver

then you can set to the received point. It will behave as though it were set on the publishing device, such that the value is only recorded as it is echoed back in the normal course of publishing.