Report Generation using Automation

John_McCredie · 02-09-2022 03:41 PM

I'm hoping someone can shed some light on an issue I'm having with automated report generation.

I have an App that is used just to generate reports for another App.

On the 4th day of the month it calculates a bunch of scores for the previous month, this works perfectly. On the 5th day of the month these scores are then updated into another table which has a formula that calculates other scores. This also works perfectly.

On the 6th day of the month, an automation runs to build a monthly report for multiple customers that is very complex with lots of data using SELECT() from multiple tables and references, pie charts and bar graphs, using SNAPSHOT(LINKTOFILTEREDVIEW), plus a whole lot of other info. The reports are usually about 10 or more pages long.

The reports work, but some months they run, and other months they don't. The months when they don't run, there is no info in the Appsheet Monitoring to indicate an error or anything. They just don't run.

If I manually run them, sometimes they run, other times they don't. If I adjust the database manually so only one customer report at a time is generated, it usually runs successfully. (I'm using a TRUE/FALSE filter condition to specify which rows of the table to use - so it's easy to manually have just one customer selected).

I'm making the assumption that the report generation is timing out and failing. Is there any way to extend the time out? Or am I on the wrong track? My reports generate at odd times too, like 9:23am Australian EST, which I read was the best approach to take, rather than on the hour.

When I ran them separately today. monitoring shows the 1st customer report took 113 seconds to complete and 2nd Customer took 84 seconds to complete. So the total time was about 3 minutes and 17 seconds.

Any advice, suggestions or assistance would be appreciated.

Thanks, John.

WillowMobileSys

As I read through this, timeouts was my thought as well.

Not knowing your data and use cases, my general advice is to break up the complexity and processing. Rather than collecting it all to be done at report generation, distribute the work throughout the month as data is input. Build the data you need for the reports as if you want to look at it daily. Then when it's time to generate the reports, it's mostly a lift and shift operation. The side benefit of this approach is that you can create actual views to look at the data in near real time whenever desired or generate an adhoc report at any time.

John_McCredie

The data that I build at the end of the month is actually really simple. 4 fields for each customer with a 5th field for net promoter score. And that part is working fine.

The report then uses all the data collected during the month in the report. I could easily make the report less complex and make it 3 or 4 reports that generate at different times, but a customer wants just one monthly report.

The biggest issue with this problem is that I don't know how to troubleshoot and work out if it is a time out. Plus, the time it takes to make the reports individually doesn't seem that long when it actually works.

Thanks for the suggestions though. And of course I have a workaround of running each customer separately. Just be nice if the automation was automatic EVERY month 🙂

WillowMobileSys

@John_McCredie wrote:
On the 6th day of the month, an automation runs to build a monthly report for multiple customers that is very complex with lots of data using SELECT() from multiple tables and references

This is the part I am referring to break up. Depending on the size of the tables, SELECT's can become inefficient and get worse over time as the table grows. Even if you separate into multiple processes, each process could likely reach that point of slowness.

Since it appears you are not getting any error messages (I assume you have looked at the Monitor for the Bot?), I am not sure there is a good way to analyze the problem. For that analysis we would need some insight to what's happening within the servers. Our only hope for that is through Log entries. I guess a Feature Idea is needed to prompt AppSheet to add better logging around these issues. Until then, we have to rely on AppSheet Support to help figure out the problem.

Additional Options:

1) If your tables are housing data that is not being used for daily processing, you could explore purging or archiving the data into a different datasource. Reducing the size of the tables will help.

2) If you are using sheets, keeping the sheets trimmed will help but not much. Google sheets are created with 1000 rows by default. Many lo out table don't need that many. The extra rows do cause extra time spent in interacting with the sheets. On Google sheets there is now an Extension to Crop the sheets to the data size (instead of manually deleting the blank rows and blank columns)...and yes new rows will still be added normally.

3) As you mentioned you could produce the reports separately and then send a single email with all of the reports attached. I am not sure of a way to combine the reports into a single doc but that may be worth looking into as well.