How to get the count of events having value for a particular data element?

We are trying to sync event data to DHIS using an automated program and we want to ensure that the data is getting synced correctly. We would like to use some queries in the DHIS db to make sure that the counts of events synced are matching with the data we have. Currently, we are checking the programstageinstance table for this. One example query we are using to get the count of all events in HTS program stage is as follows :

select count(*) from programstageinstance pi inner join programstage ps on pi.programstageid = ps.programstageid inner join organisationunit o on pi.organisationunitid = o.organisationunitid where o.uid = 'KP8boYIVyI7' and = 'HTS' and pi.eventdatavalues -> 'RQXv5WqAQGX' is not null;

But this query is not returning the expected results. Is this the correct way to get the count of event data having value for the element with id RQXv5WqAQGX ? We were unable to find proper documentation for DHIS database model. Any help would be much appreciated.

Hi @vishnu,
Welcome to the community! :tada:
This sounds basically similar to when we are using the Event Reports app as over there we select the data element (after selecting the program and program stage) and then selecting the where and when dimensions. For example, taking the API request that’s being sent when using the app you can see
Program (IpHINAT79UW) + Organisation Unit (ou:DiszpKrYNg8) + period (pe:LAST_YEAR) + data element (A03MvHHogjR.bx6fsa0t90x) + optionally: Program Stage (A03MvHHogjR) & displayProperty (NAME)

I think these are the main things that must be included in the request or in the query but maybe it’s not necessary to mention the period when querying directly from the DB?

It’d help a bit more if you specify exactly what you’re looking for. Maybe the schema: Database Scheme of DHIS2 Tracker ?


Thank you very much @Gassim .

Database Scheme of DHIS2 Tracker

This is exactly what we were looking for.

Also, we will try out the API you provided and explore if we can use it instead of the sql queries :+1:

1 Like