dhis2-devs team mailing list archive

["Wilson,Randy" <rwilson@xxxxxxx>]

I believe those table are blocked to prevent non-system administrators from viewing user data.

If you have Pgadmin access or have installed PHPpgadmin on the server, you can run the queries there.  SQL views in DHIS-2 are also problematic especially if you refer to any of the resource tables because the analytics and data mart processes try to drop them when running and then they fail.

We've created a function that creates a number of 'materialized views' that our users want to export for Excel pivot table analyses.  This can be scheduled to run daily or we can run it on demand.  If it is something you want to view regularly you might go that route. For example most people using pivot tables want to view data with all levels of the hierarchy in a single row (province, district, sector, facilityname, dataelementname, startdate, periodtype, categoryoptioncombo, value).  Jason Pickering, a frequent contributor to this list is the master of materialized views and helped us develop ours.

Unfortunately there does not appear to be a way currently to expose those materialized views through the api.  You can now create iReports with a JDBC datasource (i.e. not based on a DHIS-2 pivot table), so that might be a way to view the data from within DHIS-2.

Good luck!


From: Dhis2-users [mailto:dhis2-users-bounces+rwilson=msh.org@xxxxxxxxxxxxxxxxxxx] On Behalf Of Gomez Phiri
Sent: Friday, September 06, 2013 11:03 AM
To: dhis2-users@xxxxxxxxxxxxxxxxxxx; dhis2-devs@xxxxxxxxxxxxxxxxxxx
Subject: [Dhis2-users] SQL View Permissions

Hi all,

Is there a way I could get access to special tables in dhis2? I'm trying to create sql view with the statement "SELECT username,lastlogin FROM users order by lastlogin;" and when I try to save the sql view I'm getting the message "Not allowance to query in the special tables!" We want to monitor by name who frequently logs-in the system.

Gomez.