Materialized views for events fro specific programs

Hello team,
I would like to know how to create a materialized view from event reports.
Or how do I query data for specific program data elements in SQL?


cc: @Gassim

Hi @abdimohammed

Thank you for your question! I’m wondering why materialized view vs the standard SQL View? I think you might find this helpful: Visualizations - DHIS2 Documentation

First, I’d start with getting the list of tables:

SELECT * FROM pg_tables
WHERE schemaname = 'public';

You can see the name of tables that are related to your query:

Then you will need to know which columns you want from those tables and create a SQL query.

Another way to go about this is to use the API. For example, domainInstance/api/programs/programUID?fields=name,programStages[programStageDataElements[programStage[name],dataElement[name,valueType]]]