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: [email protected] To: [email protected]; [email protected]; [email protected] 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:[email protected]] On Behalf Of Gomez Phiri Sent: Friday, September 06, 2013 11:03 AM To: [email protected]; [email protected] 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.
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
