Excel Reports - function compatability

[BG]

Hello all,
I am using Mango 2.8 with Excel Reports module 1.1.4.
Does the excel reports module support the following functions:

FLOOR
SUMIFS
AVERAGEIFS

Also if those functions are included inside IF statements?

The functions above work fine in my desktop Office Excel 2010 and are supported in our Office 365 version.

But when I run the report in the excel reports module I get an error message in the Finished Reports page saying "Failure error evaluating cell "Calculations!G5" and sometimes the cell reference changes.

Cheers

Brian

[phildunlap]

Hi Brian,

We did some light testing and were able to use the FLOOR and SUMIFS statements inside IF statements without finding the issue, but we did see the same result using the AVERAGEIFS function. It's possible this would have been fixed in a newer version of apache POI (3.14 has been in the last several releases, the latest version is 3.17) but it is not drop-in compatible with 3.14 so alas it will take us updating the module to update the underlying POI jar. Here's the git issue about it: https://github.com/infiniteautomation/ma-core-public/issues/692

Edit; Also, i just took a second look at the version you're using. it's possible in your Mango/web/modules/excelReports/lib/ directory your Excel Reports module may be using an even older poi jar, which would explain why we didn't have issue with FLOOR, SUMIFS and nesting them inside IF statements, as we were testing the latest excel reports module.

Edit again: This page with a list of supported functions may be useful to leave here: https://poi.apache.org/spreadsheet/eval-devguide.html

[BG]

Hello Phil,
Thanks for checking. I have been redesigning spreadsheet to do the AVERAGEIFS and the COUNTIFS in long hand format. Through trial and error I found the same as yourself that the SUMIFS and FLOOR works fine.

As mentioned initially, this is for Mango 2.8 so hopefully will not be a problem when I finally get Mango 3 off the ground.

Cheers

Brian