Hi Lars,
I am not really sure. I think I would have to see it to see if it
meets what is required here. Again, here it is very difficult to
decide what is compulsory and what is not. This will take some time to
sort out and would really need to be based on what is available in
terms of services at each facility, as different orgunits would really
be required to report all the time on certain data elements. Some
orgunits will not offer VCT, but for others, it might be compulsory.So
some units would need to submit data on certain elements all the time,
while others would not. I think this would eventually be the way to
go, but for now, we are really just concerned with trends of data
submissions. It is not expected at this point that all of our
facilities will be submitting all data elements all the time. But, if
there are significant drops or spikes in data submission ratios, then
this is something that should be investigated.
I have extended the SQL I sent a while back to a slightly more general
case, attached with this mail, to compare submission ratios over time.
It is approaching something procedural, and could probably be
implemented in Java fairly simply. I have attached the report as well.
I still have some concerns about the queries, but they are pretty
close to being corrent. Notice that several things in the SQL are
hardcoded to the Zambia case, and would need to be further
generalized, but at least it would give you an idea where we are
heading here.
Maybe this will give you a better idea of what is required here (or at
least as i have interpreted it 
)
Regards,
Jason
quarterly_data_submissions.rptdesign (104 KB)
datasubmissions_over_time.sql (6.44 KB)
···
2009/4/30 Lars Helge Øverland <larshelge@gmail.com>:
On Thu, Apr 23, 2009 at 9:46 AM, Jason Pickering > <jason.p.pickering@gmail.com> wrote:
Hi Lars,
Here are the Postgreql scripts that I have developed, that provide me
with what i am looking for.They are very basic at this point. Sorry
for the mess.
Zambia, and not so sure how easy it would be to generalize. I think
you have the Zambia DHIS2 database, so you could probably test against
this. ,The output that results is what I am looking for, namely a
summary of submitted elements for each dataset for each facility for a
specific time period, with the number of expected and submitted
dataelements, and a value to indicate if anything was submitted at
all.All that you need to do is to run the scripts (I think....have not
tested on a clean DB) and then execute withSELECT * FROM montlhy_facility_data_submissions(5434);
where 5434 would represent some periodid.
You should get back something like this..
facilityid;"facilityname";"datasetname";"datasetid";"expecteddataelements";"submitteddataelements";"returnratio";"submittedanything";"country";"province";"district"
3899;"Chibombo RHC";"Routine PHC Dataset
HIA2";3550;368;168;0.45652173913043478261;1;"Zambia MoH";"Central
Province";"Chibombo District"
3899;"Chibombo RHC";"Disease Reports HIA1 Page
1";3551;429;49;0.11421911421911421911;1;"Zambia MoH";"Central
Province";"Chibombo District"
3899;"Chibombo RHC";"Disease Reports HIA1 Page
2";3552;325;48;0.14769230769230769231;1;"Zambia MoH";"Central
Province";"Chibombo District"
3899;"Chibombo RHC";"HMIS HF Data Quality";3557;17;;;0;"Zambia
MoH";"Central Province";"Chibombo District"
....Hi Jason,
I'm very sorry, this mail went into oblivion.
I see what you want with this, a ratio of expected / submitted data elements
for a data set for a period / orgunit.As I said we have been thinking of developing a solution for objective data
completeness based on a requirement from SL. We have proposed a solution
where data elements are marked as compulsory, and if all compulsory data
elements are submitted the data set will be defined as complete. (No need to
click "complete" in any way!) Percentages / ratios can be derived from this.The difference is that this solution is a little more course-grained than
yours as you want a ratio for each data set, while the latter provides
ratios for the orgunit / period. Would this solution suffice, or do you need
the possibility to examine each data set in detail? If so, I guess we can
develop functionality that covers both of these requirements.Lars
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"dhis-users" group.
To post to this group, send email to dhis-users@googlegroups.com
To unsubscribe from this group, send email to
dhis-users+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.co.in/group/dhis-users?hl=en-GB-~----------~----~----~----~------~----~------~--~---