Rewriting API to Return Unique Values of Data Element and Aggregated Values

Support - Assistance technique
1

Hello Community,
I am trying to obtain unique values of data element and their aggregated values from the current API. However, the API only returns three columns: data element, period, and value. This makes it impossible to get the desired output. Is it possible to program the API to return only two columns: data element and the sum of the data element value? My API is included below:

/api/29/analytics.json?dimension=pe:202201;202202;202203&dimension=dx:bjMEngqi5QX;mNSVBN0Bzdn;aNV9H1BXtfl;DVMvev2RCx9&filter=ou:SPDPD3qSTNL&displayProperty=NAME&outputIdScheme=NAME

Regards

Samnang

2

Hi @Samnang_Sao

You might want to experiment with this page first: Get a Analytics Raw Data Json | DHIS2

In that page you will be able to view all the parameters that can be used with the API endpoint. On the right side of the page there is a tool that you can use to format the API request and insert the values, and after inserting the values in the tool, you will see the API request formatted.

I was able to get the API to return the Data Element and Period in rows and then depending on where it is used one could extract only the rows and deconstruct it to retrieve only the period and value. Here’s the request I tried to play with: https://play.dhis2.org/2.40dev/api/40/analytics/rawData.json?dimension=dx:fbfJHSPpUQD&dimension=pe:LAST_12_MONTHS&filter=ou%3AUSER_ORGUNIT&displayProperty=shortName&includeNumDen=false&skipMeta=true&skipData=false

I hope this helps! (:

3

Dear Gassim,

Thank you for your quick response. I research the link you provide but it does not seem to provide me the right answer as the API still included Dates. Could you tell me
1 - how to provide Start Date and end date to fetch data in the API and
2 - how to exclude the dates (dates can be used to filter data only) from result set and how to aggregate the value for each data element. For instance,

fbfJHSPpUQD",“202208”,“2022-08-01 00:00:00.0”,“2022-08-31 00:00:00.0”,“6.0”,
fbfJHSPpUQD",“202304”,“2023-04-01 00:00:00.0”,“2023-04-30 00:00:00.0”,“41.0”
fbfJHSPpUQD",“202208”,“2022-08-01 00:00:00.0”,“2022-05-21 00:00:00.0”,“6.0”, fbfJHSPpUQD",“202208”,“2022-08-01 00:00:00.0”,“2022-06-11 00:00:00.0”,“6.0”

to
fbfJHSPpUQD", “59.0” (59 = 6+41+6+6).

Best regards

Sao

4

Dear @Samnang_Sao
Yes, you are correct in that the analytics API does not have any functionality to return distict values. The only way currently that I can think of would be with an SQL view which you could use to filter for distinct values. The other possibility would be to use a client (R/Python) to download the data via the analytics API and then determine the unique values.

Regards,
Jason

2 Likes