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.
Problem downloading Excel report
-
Hey guys, we have been working on a fairly large excel report, right now the mango says it takes 3 mins to run, and what we've run into is that we can't download the completed report for some reason. When I click download the page just loads nothing, goes just white and that's it.
The generation of this report also seems to be pinning the CPU on my VM and just locking everything up.
Any ideas on what I could do to try and figure out just what it is that's taking so long? I mean it is quite some data I have to admit.
-
Something else on this topic guys, is there a way that I can preprocess some of the data before I dump it into an excel report? Here's the deal.
I have two stream of data which I'm basically using excel and multiplying together. One stream of data is 15 min electrical demand and the other is hourly cost of this electricity. I was using mango to give me hourly sums of the 15 min data and dumping both that and the hourly price into the excel report, only to have excel multiply the two numbers, hour by hour, together. What I suspect is that I've run up against having too many of these calculations. TBH this is very unnecessary. Ideally I would just do a bunch of this processing on the mango. Keep in mind that I have 60+ streams of the hourly electrical data as there are 60+ clients.
I could see doing something like creating points for the result of the multiplication so that the mango would multiply the numbers for me day to day but Im wondering if there is anything I can do on the fly before it goes into the report?
-
Hi psysak,
Any ideas on what I could do to try and figure out just what it is that's taking so long?
I know it can be worse if there are lot of cells defined in a way that your spreadsheet is very large when being processed. We mitigated the consequence of having lots of empty cells at the end of a named range, but in the past some users certainly hit shift+end when defining their named range and blanking out a couple million cells in their case made their report very slow. Perhaps there is still an existent issue with very large formula columns? Couldn't say.
Something else on this topic guys, is there a way that I can preprocess some of the data before I dump it into an excel report?
Yes! If you're up to date you should see the Excel Reports has a "Post processing script." It can fill in cells in the Excel Report either by location or by named range. This occurs after data is written in the normal Excel Reporting process, but before Formula cells are evaluated. The help has a list of available functions, or you can
print(ExcelReportUtility);in the script. For optimal efficiency, you would want to do the calls to theExcelReportUtility(only in context in Excel Reports post processing scripts) in the callback function to aPointValueQueryso that values are streamed from the database.
Also to the original question of downloading, I'm not sure what limit you're hitting but you can find the completed report on the filesystem at Mango/web/modules/excelReports/web/report-data/
-
@phildunlap Sorry for the delay, thank you for the response Phil.
-
Certainly! I'm surprised in my response I didn't speculate that running low on memory was the cause of the CPU spike, but maybe that was to do with it finishing in 3 minutes making me think there was just some serious reporting to be done and memory wasn't the real issue. Also probably should have asked if you checked the browser's developer console when downloading the Excel report.
-
@phildunlap hey Phil, I'll check the console to see what's going on but I think the whole thing is just hitting limits on what the poor mango can reasonably handle, and probably more so what mango running on this specifically resourced instance of AWS can do. What we are asking of it is a bit nuts, the number of simple multiplications is ludicrous, hence my question of pre-processing because tbh what I'm doing requires simple loops to perform some math and then dump the results.
To that point, question about pointValueQuery. Is there a way to return the timestamp of the resulting point value when I do something like rollupquery? I'd like to verify that I'm multiplying what I think I'm multiplying. Essentially I have my two streams of data, one is an hourly price and the other is 15min demand. I was using the excel report to do this, and now I'm playing around with a script, but basically I use START rollup on the hourly price and SUM on the 15min demand and end up with two streams of hourly data. At that point it's easy to multiply them together. The only thing I would really like to do now is visually inspect what the timestamps are. Why is that callback function called PointValueTime, is there a time in there somewhere? When I print the passed back value it's just a single pointvalue so I'm a little stumped on whether this is possible.
-
To that point, question about pointValueQuery. Is there a way to return the timestamp of the resulting point value when I do something like rollupquery?
That's actually an error in the PointValueQuery's toString method I think. Thanks for bringing it to our attention! The callback's class for the rollup query in the code is
ScriptPointValueRollupCallback. If the point is a Numeric point, the callback passes a Double, but Image, Alphanumeric, Multistate, and Binary points will get DataValue objects (except for integer rollups likeCOUNT).To the question of finding the timestamp, it's kinda a tricky thing. What is the timestamp to give for the "First" rollup? Period start, the time the value occurred (null if missing?), or period end? Does an average occur at the beginning, middle or end of a period?
If I were to go about getting those times, I could do it by computing the number of milliseconds in the rollup period, and then add that N+1 times to the start time for the Nth period end time. However, it's probably easier to use the
ALLrollup type, which will get the AnalogStatistics (numeric), StartsAndRuntimeList (multistate, binary), or ValueChangeCounter (alphanumeric, image) objects themselves passed to the callback. These objects all have methods forgetPeriodStartTime()andgetPeriodEndTime()and then you'd need to explicitly grab to the rollup type you're looking for from the appropriate method, like,getStartValue()for instance. There would not be any way to get the timestamp the real start value had, but you could compare it===with the first value and if they are not the same object then the start value precedes the period. -
thoughtful emoji
-
@phildunlap Phil, is there a resource I can refer to for documentation on the available methods? Jared showed me the API docs for the front end, I'd love to have something similar for backend.
Kind of on that same note, is there a way to setup something like MS Code for Mango dev and code in that? I'm always just using like a Scripting DS which is frustrating cause there's no auto completion or anything like that. I'm slowly getting myself proficient on this whole thing but progress is hampered cause I lack a view of what's available. I think that combined with being able to print out would be gold. -
Phil, is there a resource I can refer to for documentation on the available methods? Jared showed me the API docs for the front end, I'd love to have something similar for backend.
There are certainly many documents about the backend, but the depth of those certainly does leave some questions unanswered. I guess to that hole I try to be the best resource I can to expand that here on the forum for intrepid searchers, but the code is open source which is of course the definitive documentation. https://github.com/infiniteautomation/ma-core-public/blob/main/Core/src/com/serotonin/m2m2/rt/script/PointValueTimeStreamScriptUtility.java perhaps find via https://github.com/infiniteautomation/ma-core-public/search?q=PointValueQuery&unscoped_q=PointValueQuery
Definitely a lot easier if cloned into an IDE so that you can easily follow any particular class to its definition (which for some reason I think github willfully will never support). I use eclipse.
Kind of on that same note, is there a way to setup something like MS Code for Mango dev and code in that? I'm always just using like a Scripting DS which is frustrating cause there's no auto completion or anything like that.
Unfortunately I do not know a way to get code completion to our script edit boxes, but it would be awesome. I have a somewhat skewed perspective of how challenging this issue is since I've committed a lot of class names and package paths and whatnot to memory at this point. The editor is the "ace" editor, and it looks like custom autocompletion does have some amount of support, but I'm not familiar with what's truly possible: https://github.com/ajaxorg/ace/issues/110
I think that combined with being able to print out would be gold.
Not sure what you mean print out. Some things do have useful toString methods defined, but others will still give you the classname, which is still useful if you have the source code. Because it's Java, one could write a global scripts function that does the necessary reflection to print out all the method names, for instance (perhaps one is trying to call some of the classes / modules that are not open source!). But, at that point it's maybe easier to ask on the forum!
