dhis2-devs team mailing list archive

[Gomez Phiri <gomezphiri@xxxxxxxxxxx>]

Hi Wilson,
I have pgadmin access and I can run all the queries from there, I just wanted to create the view for some non technical users who cannot query the database for user activity directly through pgadmin.
Thanks
Gomez.

From: rwilson@xxxxxxx
To: gomezphiri@xxxxxxxxxxx; dhis2-users@xxxxxxxxxxxxxxxxxxx; dhis2-devs@xxxxxxxxxxxxxxxxxxx
Subject: RE: [Dhis2-users] SQL View Permissions
Date: Fri, 6 Sep 2013 10:28:10 +0000









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.