Situation
Hi everyone!
I work in a nonprofit organization in Ecuador, we have a small medical center and we are using OpenEMR a year ago, but we have problems to get the reports I mean patient’s reports.
We are using openEMR 5.0 and use a cloud server on amazon.
Does anybody have a suggestion about where should I start looking for a solution?
Thank you guys!
Best!
Hi Carlos,
Can you describe the problem in more detail? Can you provide screenshots of your issue without publishing PHI?
There might be a setting where the user that is trying to generate reports doesn’t have access to them, but without more information it is hard to diagonse the issue.
As with any technical support question or issue, providing screenshots will be very helpful.
I hope I can help you.
Dan
@CarlosQ06 To expand on Dan’s reply, OpenEMR has several different kinds of reports, accessed from different places. For some reports, the users’ ACL permissions will prevent access; others will be restricted by their menu configuration settings.
What reports are you trying to get to?
What are the ACLs of the users trying to access them?
Thanks- Harley
Thank you so much for your responses.
@htuck @growlingflea
Going a little deeper into the issue.
I work for a small foundation in Ecuador, and we work with health I mean we provide health care to poor people through a Clinic that we have.
Since last year we start using OpenEMR to have a better way to save the patient information. As we are a small foundation, we do not have enough resources to pay for an EMR, so we choose OpenEMR and we implemented it last year.
The thing now is that we are trying to get a report of patients I mean we would like to see how many patients we have seen until now and know a little bit about them (like ages, where they are from, diagnosis, treatment if they chronic conditions and things like that) we haven’t had a report until now because we don’t have an IT department and we do not have the money to hire someone to build the report so we are trying to get it by ourselves.
With ACL you mean the Acces Control List? Do you think that is the reason why I cannot access a report option?
I will appreciate all the help that you can provide to us.
Hi @CarlosQ06
This forum can be thought of as your free OpenEMR IT support! Often it’s slow but the people who answer often are the very ones who coded the part of the application you ask about- can’t beat that!
In my case I’m not on the forum as often as many other contributors, but I have done user support for OpenEMR for about 6 years so do have help to offer. And growlingflea has been developing in the OpenEMR project for almost that long.
One resource I find that mysteriously very few people are aware of is the OpenEMR wiki documentation repository at:
https://www.open-emr.org/wiki/index.php/OpenEMR_5.0.0_Users_Guide
Admittedly it is a bit disorganized, but then it has been added to with no formal plan for about 15 years. But that page does give access to documentation of most of the main aspects of OpenEMR. The only problem is knowing what the name is of what you’re looking for!
Another thing you’ll notice is that many of the docs were written for earlier versions of OpenEMR. However, most of the interface elements have been retained in the newer versions, merely moved around on the screen, so the instructions still apply.
Briefly about the ACLs, if your account has an Administrator ACL you’ll be able to see all the reports, so if one of your staff can’t access them, you can tell it’s an ACL problem and change their role to something that can.
About the patient reports you’re looking for, this page in the wiki (link below) has a good summary of all the reports. The doc was written for an earlier version of the EMR, but as I say, the controls that are used to set the report parameters are fairly consistent. Because of that you should be able to use any report in the system, as described in this doc.
https://www.open-emr.org/wiki/index.php/Reporting_4.1
As to the specific reports you mentioned, if you access from the Main Menu: Reports / Clients / List that will have the basic patient data of the pts you have seen in a date range (see output in image below).
Then look at the info available at Reports / Visits. That entire submenu is rich with useful info.
Best- Harley
Can you provide a spread sheet that lists all the columns you are looking for? There is a chance the report you are looking for already exists and either Harley or I can walk you through the process of printing the report.
Hi guys!
I appreciate your help.
It looks like there are default reports, but probably I will have to create a new template because I cannot see one with the fields that we need.
I attached the spreadsheet with the fields that I am looking for in the report.
Best!
Carlos
patientsReport.xlsx (9.3 KB)
OK. It would take about 4 hours to create a report like this. When you say community, do you mean city or are you storing a community value in the database?
Also, do you want one row per patient?
When you say Diagnosis, do you want data to come from the Issues field or per encounter?
Hi Dan!
@growlingflea
With community I mean where the patients live. Let me explain it, we do some medical brigades, that last 5 days, and each day is in a different neighborhood (we call it a community) this is because we try to provide health care to the most needed people in each community. Sometimes we go to the poor neighborhoods right outside of the city and sometimes we go farther in the jungle with the entire team to help people with difficult access to healthcare. So for these brigades, we have to do a report for the Ministery of Health in Ecuador about how many patients we saw in each community so they try to do a follow-up. So the short answer (haha) is that this field should be the address of the patient.
And yes! We need one row per patient.
The diagnosis should be the diagnosis that the doctor gave to the patient in that consult, I mean per encounter.
Once again thank you so much for your help!
Carlos.
Hi Dan!
@growlingflea
Sorry, but i would like to ask if you lead me how to get this report?
Sorry, we will need it soon so I would like to learn how to do it.
I know a little about computer science, I am studing IT, so if you could just give an idea about where I can start I could try to do it by myselft.
Best!
And if you would like to know a little more about my organization here is the website and the facebook page.
https://www.hhecuador.org/
facebook page: /hhecuador
Hi @CarlosQ06 are you looking to print this report for all patients or do you need to do a search and only print the data for all patients meeting a certain criteria? E.g. postal code
Hi Rachel.
Thanks for answering!
Is a report for all patients in a specific date.
e.g getting a report of the patients who came to consult between march 1st to march 15th.
I am wondering if I just can get the database and do a sql script for getting the fields that I need.
Is it that possible?
Best!
So if I’m understanding correctly you want to find all patients who visited the clinic in a custom date range. Then print off the patient demographics table?
Hi @CarlosQ06
I think the SHORT short answer is this: none of the reports that come standard in OpenEMR will search for encounters by date AND postal code.
Now, I am not a developer, but I know OpenEMR’s reports are basically mySQL searches on the database. It is common for a dev to modify an existing report by adding terms to the search that the page performs and adjust the output display to accommodate the new results.
The reports that come closest to what you want might be (from main menu:) Reports/ Visits/ Encounters. Or perhaps Reports/ Visits/ Appt Enc. Then you could add a text area to enter zip code for the search and adjust the columns for the new display.
However, if you have no php dev, and you do know mySQL and you have access to your database via adminer, phpMyAdmin or other, it’s a fairly trivial thing to look for all encounters created on a date in a location.
The relevant fields are:
table: form_encounter
fields:
.date
.pid
table: patient_data
fields:
.pid
.postal_code
JOIN on the pid.
Those tables have plenty of other useful fields you might want to report on, too.
Good luck!
Hi guys!
Thank you for answering you are so much helpful.
@htuck
In the report, I will need also information about the diagnosis and the medicines prescribed. The doctors here are using the SOAP (Subjective, Objective, Analysis, Plan) section.
eg. In the report I will need demographic data but also the SOAP in a custom date range.
So in I will need to explore more tables.
Because I am not very good at MySQL I wouldn´t like to go directly to the database I would like to “practice” first. So…, is there a way that I can reach the OpenEMR database structure to practice? Do you know if the demo version allows you to do some SQL consults on the database?
Once again, thank you so much for all the help!
Best!
Hi Carlos,
I think you used to be able to use PHPMyAdmin(a tool that allows you to visualize the database and construct queries) on a production install of openEMR. But I believe that was removed and is only available on the development version because it’s less secure.
I’m not terribly familiar with the reports generator and if it might meet your needs. The SQL queries to build your report sound pretty straightforward. If date range is the only parameter, you should be able to put it in a basic PHP file that takes in the start and end date in the header. There’s a file in the code base called standard.json that builds the menu system you see at the top of the screen. So once you know the location of your new PHP file and its name, you can add it to the JSON file and access your report by selecting the custom report from the menu.
If you want to try out the developer version that gives you access to PHPAyAdmin there’s some instructional videos on the OpenEMR YouTube channel that might be helpful.
would be great to add the missing data to the Reports->Clients->Patient List Creation
am starting to add insurance companies in this PR
That’s a great idea. Maybe @spatel1313 can help with that. Maybe we can take some time to discuss a plan on the Saturday call.
@CarlosQ06 If you have your own instance of OpenEMR at hand- like, a local installation- you could install a database manager to play with. I understand that adminer ( https://www.adminer.org/ ) is a bit easier to install than phpMyAdmin- I’m a customer service guy, not a dev and I even did adminer myself once.
I’ve used both for basic database activities; adminer is as easy to use and has all the necessary capabilities as the other. It’s a great way to learn basic MySQL!
At the end. I got a backup of the DB and Import it on phpmyadmin and I got the report. Thanks a lot for all your help, this group is amazing!
Thanks a lot!
