Use of SQL Views

Hi Devs,

Can anyone explain how to use the SQL Views under Data Admin app. I am trying to learn from the online manuals but failing to put together for my use.

FYI, We are using DHIS2 2.29.

1 Like

Hi fellow Academy mate :wink:
We have a few SQL Views setup - first there are some SQL script, then the ‘Execute query’ tests if it is legit, then the ‘Show SQL view’ will run the script for you & allow it to be downloaded in various formats.
We have most of ours setup as SQL type = View & Cache strategy = Respect system setting.
I also have a local dump on the Postgresql install on my laptop to test the SQL scripts beforehand.
Does that help you?

1 Like

Hi @dmnscar, :wave::blush:

Glad to see you here. Hope you are doing well and good.

Thanks for sharing your experience. Generally, what you mentioned works. But my scenario is different.

My scenario is to develop SQL views for some kind of reports, that are not possible to do through Event Reports and share with users. Now my concern is sharing the SQL views to the users. I am seeing when I give the authority "See Data Administration and “SQL View Management” I am exposing all options under “Data Admin”. In addition to this, the user who executes the SQL, gets all data that comes out of the SQL instead of filtering for their respective org_unit.

I am not sure whether you experienced this problem. If so, how did you handle it?

Thanks for your time and have a good day!

1 Like

Hi @MSP,
We have not gotten that far with the SQL Views; they just execute a SQL script against the DB & produce a ResultSet that we can then refer to.
Maybe you need to create separate SQL scripts per OrgUnit & name it accordingly.
Have you a local copy of the DB that you can test this on, so you can finetune the script to produce the desired report/s?
Sometimes we had scripts that were not correct & never ended, & we had to manually stop the process on the server side to get DHIS2 back up & running. So now we test them locally beforehand.
Hope you are all good where you are too :wink:

1 Like