[Solved] How do Prepared Reports "Actually" work?!?

Ok, I have never needed to use Prepared Report feature until now. So I went to these instructions to see how to do it:

https://docs.erpnext.com/docs/v13/user/manual/en/setting-up/articles/prepared-report

In the instructions it says to use the Role Permissions for Page and Report to make the option available for any report in the list. So I did that for the “Sales Invoice Trends” report.

Why would I do that? (you may ask)

Well I have a commercial customer that has called in and requested information on every item they have purchased this year and what they have spent for the year.

It turns out the “Sales Invoice Trends” report allows you to group the report by “Customer” in the third, normally empty, search criteria box at the top of the screen. This however, attempts to search all items across all customers in order to sort the data in the report. This causes the dreaded timeout errors.

That makes this the logical candidate for the Prepared Report function. However, after going through the Roles Permission for Page and Report to make sure this is enabled for this report, when I actually run the report with the correct sorting criteria, it still times out and does NOT give me the option to “Generate New Report”

There is also no indicator that it would be running the report in background.

(Yes, I did use the “reload” function to make sure the new report settings were in place)

Am I doing this right?

Did I miss something?

Are these instructions still valid?

Does anyone here have experience with this function that can explain it any better?

BKM :nerd_face:

Your post got me curious, so I did some digging.

In the SQL table 'tabReport', there are actually -2- fields that talk about Prepared Reports.

SELECT
   `name`,
    prepared_report,
    disable_prepared_report
FROM tabReport
WHERE disable_prepared_report = 1
   OR prepared_report = 1

In all of ERPNext v13, it appears there is only one report marked as ‘prepared_report’.

Stock Balance.

And sure enough, when you run that report? You get the “Generate New Report” button.

(by the way, after clicking that, I -never- have seen a message indicating completion. I always have to manually click the refresh button. every single time. See also: Scheduler for prepared report not working - #7 by Mohammed_Redha)

How does one mark an existing report as “Prepared Report”? You cannot. Frappe has made that field entirely non-editable.

image

Why is it this way? Is it deprecated? Is the documentation wrong, or the software?

:man_shrugging:

Recently I created a new script report, without any special setting, I can run the report and get result immediately, i.e it is non prepared report, but after running the report several times, it automatically switched to prepared report! further checking the backend code, see below, it turned out that when the run time of the report crossed the predefined 30 seconds, it will auto set the report as prepared, at the same time, user can force the system to keep the long running report as non prepared report by checking the disable prepared report.

3 Likes

That explains a lot! :100:

Thank you for sharing. @szufisher !

This is interesting…

But if this is pervasive among the reports, then why would my “Sales Invoice Trends” report never automatically switch to being a ‘prepared report’? When I run it with the added Customer filter, it gets the timeout errors.

Still also cannot figure out why when I set the Role Permissions for Page and Reports to make the report a prepared report, it does NOT work. I could not get the report to ever run in background. It always fails with the timeout errors.

Anyone else have an idea how I get this to work?

BKM :nerd_face:

Okay… I figured it out. At least the part about how to get any report to become a “Prepared Report”

It didn’t matter how hard I tried, I could not get the instructions in the official documentation to work.

Until I read this post:

.

When you go to that post and start scrolling across to read the first line of the error, you come to:

'user' : 'Administrator'

It was one of those forehead slapping kind of moments. The official instructions do not make any mention of it, but in order to get the setting to actually work you must be logged in as Administrator before you go to the “Role Permissions for Page and Report” to set the flag for Prepared Reports.

If you go to “Role Permissions for Page and Report” as a regular user, and then set the flag for Prepared Report the setting will appear to be done, but it doesn’t actually work. It only works if you do it as the Administrator.

Once set by the Administrator user it becomes a Prepared Report type for all other users.

It would have been nice to have this little detail 2 days ago :roll_eyes:

At least I can present the answer here for anyone else that stumbles on this task.

The pat that was so infuriating was the “Official Instructions” that I linked to in the first post, included a GIF example of how to set the flag. Even in the official GIF explaining how to do it, the logged in user is NOT the Administrator. Yet, anything other than the Administrator user doesn’t work. :exploding_head:

BKM