how to get date_created as a data definition [solved]

Development
1

Hi Everyone!

I’m trying to create a very simple report to show new patient registrations. I am trying to display the following as columns:

  • Patient ID
  • Patient Name
  • Patient Gender
  • Created Date

I have tried to create above report, but couldn’t get to show the date_created in my report.:persevere: I am tying to use a Row-Per-Patient Dataset.

row_per_patient.JPG1910×598 99.9 KB

cohort_report.JPG1896×893 137 KB

I couldn’t find any Data Definitions to add the date_created as a data definition. Can anyone tell me how can I get to show the date_created in my report? (I am stuck on this for 3 days now :persevere:)

@raff The screencasts aren’t available. https://wiki.openmrs.org/pages/viewpage.action?pageId=19300405#BuildingReports(StepByStepGuide)-ScreenCastsStep-By-Step

Thanks & Regards, Jude Niroshan

2

@judeniroshan, apologies, I missed this post.

I don’t believe there is an existing built-in data definition for that. We do have one for Encounters - EncounterAuditInfoDataDefinition - which we could easily add in for patients, but we have not yet done so. So I would recommend one of these two solutions:

  1. Create a ticket to add a PatientAuditInfoDataDefinition and corresponding PatientAuditInfoDataEvaluator to support this. Issue a pull request against the reporting module to add this in, following the model laid out in EncounterAuditInfoDataDefinition/Evaluator.

  2. Use a SqlPatientDataDefinition, configured with this SQL.:

select patient_id, date_created from patient where voided = 0 and patient_id in :patientIds

Mike

3

I’m so happy to see a response from the person who has published all the tutorials, videos and many things related to reporting module. @mseaton :smiley:

I have actually tried to use SqlPatientDataDefinition to show all the date_created values.

sql_def.PNG1281×349 14.8 KB
result.PNG703×410 10.3 KB

My ultimate goal is for a given date range, I need to show all the patient registration details along with the date_created. Basically it would be a row-per-patient report. Therefore, passing the patientId to the query might not need in my case. Instead I should pass the 2 date parameters for check the created date period.

Thanks, Jude Niroshan

4

@judeniroshan, hmm - interesting. This might be a bug. The issue is that the SqlPatientDataEvaluator, on this line, will return an empty result if the base cohort passed in is either null or empty.

In your case, since you will be using this in a row-per-patient dataset where you will always have a base cohort (since the first step will be to determine which patients were created during the week in a cohort query, and then the next step will be to pass this cohort into the data definitions to get the relevant data points), this may not actually be an issue in practice. But it may not be the behavior we want overall. @darius, any opinions?

Mike

1 Like
5

Woah, finally I made. Thank you so much for your help. I was running the preview of a data-definition without connecting it to a date-set definition where I have passed the relevant cohort. Now I see the report the way I want. Not quiet sure whether the data is correct or not. I was checking in the qa-reff app. I’ll check it tonight in my local machine and let you know. :slight_smile:

finally.PNG1892×572 46.9 KB

6

Hi @mseaton,

I am having the problem of passing the startDate and endDate parameters from the data-set definition to this particular data definition. As per the SqlPatientDataDefinition it seems like when I select the parameter, it only allows one parameter pass-in. Probably the patient ID. Parameter type if String, not any other.

date_created_def.JPG1393×441 47.7 KB

But in my usecase, I am trying to pass both start date and end date and retrieve the date_created records which falls within that given date period.

Any of your inputs are much appreciated. :slight_smile:

Regards, Jude Niroshan

7

@judeniroshan, the main problem here is that this data definition does not have an appropriate editor associated with it. That’s why the “sql” property is showing up as it is and there is no way to add parameters directly. For example, compare this UI with the one for a Sql Cohort Definition.

I went ahead and committed a small enhancement so that the appropriate SQL definition editor is used here. As long as I was in there, I also modified the preview functionality so that it should show results for Sql Data Definitions by running them against a random cohort of 50 patients.

Here is the ticket I worked against: https://issues.openmrs.org/browse/REPORT-814

Give things a shot running against master of reporting and let me know how things work out.

Mike

1 Like
8

Hi @mseaton, Really cool work! It works and I am now able to pass the parameters to my SqlPatientDataDefinition :blush: UI page is clean and the usual SQL writing page that we are accustomed with.

mike.JPG1902×514 76.8 KB

Now I’m implementing this report through Java code inside referenceApplication module.

Thanks a lot!

9

I’m new to openmrs… Even i want to build report same as your report containing date_created with patient name, can you help me with it… Tell me steps for it…

Thanks …