Excel Reports point limit

[solarnathan]

I have an excel report that I started out with 30 points of data to test. I've been adding 30 points at a time as I connect more devices into mango. Currently I'm at 90 points and the reports are no longer updating all of the values. Some of the first points I added are now returning values of 0. Is there a limit of how many points can be included in an excel report or is something else happening?

[phildunlap]

Hi Solarnathan, welcome to the forum!

There is no software limit on the Excel Report's size. There is a limit in how much data you can get into an Excel Report before you start demanding resources from Mango and cause issues with prioritizing the report above the UI, but that's a lot and mostly determined by how much RAM exists for that sort of task.

I would wonder if you've checked your Mango/logs/ma.log file (might be in Mango/bin/logs/ma.log if you're on Windows) for errors when you're getting the zeros? I assume you have opened the same point that is producing the zero values on the data point details page and confirmed that there are point values in the timerange you're running on?

I think something else is happening.

[mlavelle42]

I'm over 500 points and no interesting problems. It's rather amazing. And I'm running on a Raspberry Pi.

[solarnathan]

I came in this morning and ran it again. Now the points have data again. But I was changing the time period to try and fix the other problem and now past and previous aren't making a difference. Either one I select both give me data ending at 12 the previous day vs current time. When I change the number of days, the data changes so I don't know what it going on. Thanks for the help.

[solarnathan]

I actually just looked again and now different data points are at 0. The logs show no errors for the time when I run a report.

[phildunlap]

@solarnathan

The previous and the past determines if it is quantized to the time period. So, at 11:26 AM the previous hour is 10 AM --> 11 AM, the past hour is 10:26 --> 11:26. A previous day would be 12AM 6/29 --> 11:59:59.999PM 6/29 and past day would be 11:26 yesterday --> 11:26 today

Can you post screenshots of the data point details page for a point you think should have values in the report, and a screenshot of the time period configuration for the report / point?

[solarnathan]

I understand the difference between the past and the previous. The problem is changing it does't change the time stamps. It is currently set to past, but giving me data like its set to previous. Not sure if this is related to the other problems I'm having.

Here is the setup for one data point in the excel report.

Data point in excel report showing 0 value.

Data point chart with time stamps matched to excel report. I have the rollup set to delta so should be getting a value of nearly 35,000.

[phildunlap]

Looking into this right now. Seems to be working fine for me but there's a little more to check out.

I'm noticing the new report isn't always added properly to the table of reports. It looks like the sort order isn't always obeyed. So, I would try swapping the sort order back and forth to ensure you're looking at the report you just triggered.

I am also wondering if you use the statistics tool on the data point details page if you get the delta you are expecting for the time period?

Edit: Also I wonder the versions of Core and Excel Reports you are using, but I am assuming you are on the latest of both.

[solarnathan]

I don't have delta listed under statistics. And I have the reports emailing to me, so as long as its sends the new report, that's the one I am looking at.
Core version 2.8.8
excelReports 1.1.2

[phildunlap]

Alright, after some investigation I've resolved the following:

It's caused by a bug that was fixed in the 3.x series but no fix was released in 2.8 yet. I will try to get that prioritized. It'll probably get released either on Monday or Wednesday next week. This is in reference to the delta being zero. I haven't looked too deeply into the timestamps prev/past yet, I shall do that next. So, another resolution would be upgrading to Mango 3.

The good news is that you could work around it if need be. Since you're using a rollup period that's the same as your report period, you can change the rollup to none, and then change your named range to myNamedRange.delta and the point assigned to myNamedRange will have its delta for the whole report recorded in the .delta cell.

Thanks for bringing this to our attention!

[solarnathan]

Thanks for finding that. I will wait for the update. Currently this is just in the testing phase and hasn't been implemented so I'll leave my named ranges as is. Thank you!

[solarnathan]

Hi,
Checking back in. I haven't seen the excel reports update yet. Any new ETA? Hoping to use the report in production soon.

[phildunlap]

Hi Nathan,

Sorry for the delay. It will get released at some point tomorrow. Some good news is Craig found a bug in the purge of Excel Reports, so that fix will be in there too.

[phildunlap]

This has been released.

[solarnathan]

I'm not sure if the update solved my problem or created a new one. I have the report set to past 30 days with a 30 day rollup. The email body lists the correct date, but the time stamps in the excel file are incorrect.

[solarnathan]

I also get a system exception when going into system settings that looks related.

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException: javax.servlet.ServletException: File "/modules/excelReports/web/settings.jspf" not found

[phildunlap]

Hi Nathan,

Oh my. You can resolve the issue on the system settings by renaming Mango/web/modules/excelReports/web/modules/excelReports/web/settings.txt to settings.jspf

I will put a version in the store that doesn't have this issue.

Edit: fixed in store

[solarnathan]

So I still have a problem that when the report runs, it isn't including the correct date range. I have it scheduled to run on the first of the month and the time period is 30 days with 30 day delta rollup. The email I received this morning showed the time as Time - 2017/07/03 00:00:00 EDT to 2017/08/01 00:00:00 EDT. But the excel file has timestamps of 6/23/17 12:00AM to 7/22/17 12:00AM. Any ideas on how to fix that?

[phildunlap]

Hi solarnathan,

I have been looking into it and I think there may be a problem with the rollup using days. Can you try a rollup of 720 hours instead of 30 days? We're working on it.

[solarnathan]

I changed the rollup to 720 hours, saved it, and then ran it. It locked up the mango for couple a minutes and didn't email the report. I went to the finished reports paged and clicked the download button. I get an error the file is not found. So I changed the time period to 720 hours. Now I get a report emailed with the correct time stamps, but incorrect data. One of the data fields is a timer that counts in second and it is only showing 57,600 seconds or 16 hours far away from the 720 hours it should be. I guess my next try will be to delete this report and try making one using myNamedRange.delta for all the fields.