report preview when large number of rows are called

report preview when large number of rows are called

by Salam Sarieddine -
Number of replies: 4

Hello,

I'm using moodle 3.6.3 and configurable reports latest version as well.

We use SCORM based courses (each has a minimum of 5 modules) and with the help of this plugin i created a custom sql reports showing (name, course name, module name, progress percentage, time spent, last access date..) along with filters, however when trying to retrieve large number of rows for all users for example, the browser takes a lot of time and even crashes sometimes.

My question: is there that we can use the filter fields as a first step and once clicking apply depending on the fields of course a report would be previewed instead of showing all the values of the query and then filter? meaning choosing the filter criteria before showing the report table not vice versa or maybe choosing default values so that onload the report would show according to the default values.

If not, then can i detect that the rows or the report is too huge to be previewed so only leave the download report button and give a message like (report too big to be previewed please download to view) so i can download and check the values without previewing the report onscreen since its either taking a longgg time or crashing?

I know i can always divide the report into several reports but imagine 5000 users that means the number of rows would be a lot greater than 5000, dividing the report into 10 or 20 reports isn't practical at all.

Thanks in advance for your help.

Regards,

Average of ratings: -
In reply to Salam Sarieddine

Re: report preview when large number of rows are called

by Luis de Vasconcelos -
Show us the query...

Have you checked the execution plan for the query?
Average of ratings: -
In reply to Luis de Vasconcelos

Re: Re: report preview when large number of rows are called

by Salam Sarieddine -
Hello,

I attached the query in a txt file and the report is created in a site level, it works fine until we're talking about a high number of rows requested.. If it is possible to filter before previewing the datatable report it would decrease the load by just previewing the same values of the columns but according to the filters, or let's say by default the report will show according to predefined default values for the filters so that not all the rows are displayed or called initially.
Average of ratings: -
In reply to Salam Sarieddine

Re: Re: Re: report preview when large number of rows are called

by Luis de Vasconcelos -
You filter it using "AND u.id != %%USERID%%", so how many rows does it produce in the report?

Are you aware of the 'Report row limit' setting in the Configurable Reports settings? If your sql query returns more records than the number defined in that 'Report row limit' then your report will be TRUNCATED! (See https://tracker.moodle.org/browse/CONTRIB-7727)
Average of ratings: -
In reply to Luis de Vasconcelos

Re: Re: Re: Re: report preview when large number of rows are called

by Salam Sarieddine -
Yeah sure i'm aware of that, and since the report is mainly for teachers to track the users progress in the same group i used u.id != %%USERID%%.
That's why i'm asking if there is a way that we can choose the filters before previewing the report datatable or i'll have to create the same report for each course instead of being at site level.
Average of ratings: Useful (1)