I recently discovered that you need to increase the php.ini settings for large databases for the Export CSV feature of collections report. The following 2 setting both failed and had to be increased
max_execution_time (default is 90) increased to 300 (same as apache)
max_input_vars (default is 1000) increased to 5000. I tried it at 3000 and it still failed
There was only 2772 lines of data exported for one month, doesn’t like that much data to me. I suspect that the method used to create the export is poorly crafted.
The requirement for max_input_vars is not surprising considering there is a checkbox on each reported invoice line.
Do you have “Export Individual Invoices” checked? If yes, it’s going to be very slow because the query selects by encounter without pid, and there is no such index. That query should be fixed to include pid.
In general the export will not work well with a large volume of output to choose from. It’s primarily intended to pick out patients with collection issues, which hopefully is a small number.
Good call on the PID thing. Sadly since OpenEMR lacks decent reports on billing issues in general, most of the larger installs I have use the collection export to be a general dump of all things accounting. It becomes the primary source of data for management and billing department.
Can you guys give me an example where I can actually get some results using this report. We have a running moderate database and still this report doesnt work.
Chandani,
I think you need to be a lot more specific about what “doesn’t work” means. Before anyone can help you. We need to know what version of OPenEMR, what OS, what options you selected and what the output looked like (redacted), were there errors recording in the log? etc.
Thank you for your reply.
By ‘it doesnt work’ I meant that I dont see any output once I select the options from the Collections Report.
Its Openemr 4.2 version running on windows and we have around 17K rows.
Wondering how can I use this report with my kind of setup & data volume or ultimately we have a requirement here for changing the query or some other settings as you guys have discussed here.
My clients use this report heavily and have volumes much higher than that. No output at all is certainly not normal. Does your apache / php error log report any issues, like out of memory or timeout?
There is the possibility of browser issues here as well.
Recommend stating this as a full report stating browser version, and perhaps a screenshot of the criteria you select that fail, or simply that it fails with any critera selected. I assume you limit the date ranges?
Also, are you using OpenEMR as your full billing software? Doing fee sheets, sending batches, all that, or just using it to take direct payments?
I take it you mean that the report does not run at all, and not that just the export features are lacking.
I understand that the lack of billing reporting causes a lot of folks to use the collections report. We use it also: but it is drastically modified as a by “service program” financial tracker. We have 14 different billing reports we use (in addition to a heavily tweaked billing manager). I just wish I had a way to get them out there for other folks to use.
I did notice that the “Payments” report we have…that is derived from the collections report and is closer to still being the original collections report stopped functioning recently…like no output at all. I remember changing some piece of the main query and it would run…and much faster than ever before… Can’t recall what that was. Heh. That one has all the SQLLedger stuff ripped out of it too though.
Just did a diff of our collections report vs the vanilla version. 1088 lines are different out of 1482 in the original…and I have 350 fewer lines total. Not much help here. Looks like Terry Hill did some extensive work though. You might want to replace your version with the dev tip here: https://sourceforge.net/p/openemr/code/ci/master/tree/interface/reports/collections_report.php
Though as mentioned by ArtEaton, we did opt for genericname2,genericvalue2 variable as unused and they might be the cause.
And certainly we have modified some other variables in demographics too which have been taken care of in collectionsreports.php
Unable to get a working report yet.
Do the conversion to the field billing_note. You won’t need to run a query to copy data (psuedocode): update patient_data SET billing_note = genericval2 WHERE genericname2=“Billing Note” (end psuedocode) since you already snuffed those values.
Since this branch may never get to the code base, it will forever be an integration nightmare if you do it, but that is better than having stuff broken all the time.