Report limitations

[icoso]

I have some reports on my database that I specify certain criteria for searching, such a date range and an office location. The reports work if I limit the date range to less than a 2 week period.

For example, If I pull a full report for a date range 2/1/2021 thru 2/6/2021 the report prints fine. If I run this same report for each week in February separately, it works fine. If I pull this report for a 2 week period, 2/1/2021 thru 2/13/21, It doesn't display the report. If I run it for 2/3/2021 thru 2/13/2021 it prints fine. If I select wider date ranges, the report does not display. It opens the new window and I can see it processing, but when it comes time to display the report, I get nothing.

If I run the same report 2/3/2021 thru 2/13/2021 on my 2nd office it prints fine. If I run the same date range on both offices, it doesn't display. It seems as if there may be a 20 page limit to being able to display the reports. On this example, If I run this date range on each office separately, I get 18 pages for one office and 17 pages for the 2nd office. If I ran them together I should get about 35 pages.

If I run a report on both offices for 2/1/2021 thru 2/6/2021 I get 17 pages. If I run it for 2/1/2021 thru 2/7/2021 I get the same because the 2/7 is a Sunday and the business is not open on sundays. However, If I add one more day to the range 2/1/2021 thru 2/8/2021, I do not get the report.

Is there some kind of limitation as to the size or number of pages in the reporting? how can I check to see why this is failing. It doesn't make sense especially since I can reports that cross over these different date ranges without issues.

I am running a PHP procedure that creates a temporary table, and then does some manipulation of the data in that temporary table. Then the report runs on that table.

Any ideas why I can seem to get a report that is more than 20 pages?

[kev1n]

It doesn't make sense especially since I can reports that cross over these different date ranges without issues.

Do you mean that other reports work fine or do you see this happen with any report?

[icoso]

This is happening with all my reports on this database. When the report doesn't display, The temporary table gets created and never gets deleted so there is a temporary table __nu1606f0b97413ca__ and __nu1606f0b97413ca__nu_summary table left in the database. I can review the data and see that my procedure that runs and changes the SSN from the encrypted to the unencrypted works and runs all the way through the last record. This temporary table has 1322 records in it. SO it seems that after the procedure runs that and the report function is then supposed to display the report, is when the process fails.

If I change my date range to one less day, it works fine.

[kev1n]

I created a very basic report containing just one field. 28+ pages are output.
Your issues must be either related to the date range or the temporary table.

[icoso]

My temporary table is fine. There is nothing in the data that would be out of the ordinary. Creating a 1 field table on a report is not a very good test.

Explain why I can run this same report on a date range of 2/1/21 thru 2/12/21 with no problems (17 pages), but if I run it thru 2/13/21 it doesn't print.
OR If I run the report from 2/2/21 thru 2/13/21 it runs fine. Again 17 pages
OR If I run the report 2/3/21 thru 2/14/21 it runs fine. Again 17 pages
OR If I run the report 2/7/21 thru 2/19/21 it runs fine. Again 17 pages
OR If I run the report 2/18/21 thru 2/28/21 it runs fine. Again 17 pages
Or If run the report for ANY combination of dates it runs fine, but if the report seems to be more than 17 pages is doesn't display anything?

[kev1n]

Creating a 1 field table on a report is not a very good test.

Sure, this is the first thing to do: To see if a basic report with 20+ pages runs fine. If it doesn't, you don't need to add more complexity to replicate the issue.
If it does you, you can go a step further by adding a data criteria etc. At least that is my approach.

[icoso]

In my nudebug messages I see the SQL statements running, I see how many records are being created by the SQL and once it gets above 650 is when it starts having troubles displaying them.
I also see my UPDATE sql statement for EVERY record. I know that my procedure is working fine. The table is getting created and the report is running. It just I cant run a report for more than about 10 days.

TempTable=___nu1606f1c384cafd___ Number of rows=622 This displays the report and all totals
TempTable=___nu1606f1c476b32b___ Number of rows=665 This does not
TempTable=___nu1606f1d5822533___ Number of rows=731 This does not

[kev1n]

Have you tried running a report without temp table? Just to see if that would work fine.

[icoso]

OK so here is what I did. I made a copy of the THE temporary table that is generated by my procedure before the database could automatically delete it.

Code: Select all

$newtab = $tabid."new";
$s = "
CREATE TABLE IF NOT EXISTS $newtab SELECT * FROM $tabid
";

$newt = nuRunQuery($s);

nuDebug("SQL=".$s);

Then I renamed the table to ReportTest1 Then I duplicated the report that i use to run from my procedure and instead of connecting it to use the PHP Procedure, I selected this new table. The table had 640 rows in it. I then ran the report and got the same results (no report) I then randomly delete 5 rows out of the table from within myPHPAdmin. I went to different pages and randomly selected some rows and deleted them 5 at a time and then ran the report. At 635 rows, the report DID NOT display, At 630 rows, the report DID NOT display, At 625 rows, the report DID NOT display, At 620 rows, The report displayed fine with NO issues.

Any ideas????

[kev1n]

I'm not sure if this will help but I think it worth a try to increase the memory:

https://stackoverflow.com/questions/527 ... blank-page