This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.
···
Dear DHIS2 Users,
I would like to run SQL to query indicators values through SQL View. On which tables should I query from if for the dataelements, the SQL queries are based on dataValues (dv) table?
Hi Tantley,
This is not really so straightforward with SQL as far as I know, as indicators are calculated “on-the-fly” from the “analytics_***” tables. They are never persisted in the database, so you would need a means to parse the syntax of the indicator, and make the calculation from that. I seem to recall that someone had done something a while back with materializing indicators, so you might want to search through the email archive of this list to see if you can find more information.
The easier way, by far however, would be the use the analytics API, and let DHIS2 do the calculation for you.
Regards,
Jason
···
On Sun, Feb 5, 2017 at 9:34 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:
Dear DHIS2 Users,
I would like to run SQL to query indicators values through SQL View. On which tables should I query from if for the dataelements, the SQL queries are based on dataValues (dv) table?
I am using DHIS2 2.24
Thank you for your support!
Kind Regards,
Tantely.
This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.
Thank you for this information. I will adapt the calculations from dataElements then. It is a bit hard to output from the analytics as we need the values of specific indicators of all the lowest level of orgUnits across all periods, so we need to split the queries into orgUnits / periods.
With Kind Regards,
Tantely.
This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.
Hi Tantley,
This is not really so straightforward with SQL as far as I know, as indicators are calculated “on-the-fly” from the “analytics_***” tables. They are never persisted in the database, so you would need a means to parse the syntax of the indicator, and make the calculation from that. I seem to recall that someone had done something a while back with materializing indicators, so you might want to search through the email archive of this list to see if you can find more information.
The easier way, by far however, would be the use the analytics API, and let DHIS2 do the calculation for you.
Regards,
Jason
On Sun, Feb 5, 2017 at 9:34 AM, Raminosoa Rabemanantsoa, Tantely traminosoa@mikolo.org wrote:
Dear DHIS2 Users,
I would like to run SQL to query indicators values through SQL View. On which tables should I query from if for the dataelements, the SQL queries are based on dataValues (dv) table?
I am using DHIS2 2.24
Thank you for your support!
Kind Regards,
Tantely.
This message and its attachments are confidential and solely for the intended recipients. If received in error, please delete them and notify the sender via reply e-mail immediately.