Hi All,
we are having error to run analytic reports, it lasts longer than usual.
when we try to run a resource table, we have the following error message.
Please Help.
ERROR MESSAGE
Link to error message: http://gyazo.com/d50d019cb8374ec38dcf020488c0167a
Thank you for prompt answer.
–
Charles Emmanuel Willer
12,Lilavois 50 Croix des Bouquets, HT
Tels: +509-3842-3854 / +509-3270-0655
Skype: emmanuelwiller
emmanuelwiller@gmail.com
emmanuelwiller@yahoo.com
Hi Emmanuel,
It is not possible to have views linked directly to the resource tables, as they are dropped and regenerated each time when the analytics runs.
Two options.
Encapsulate your view in a stored procedure.
Use the SQL view function of DHIS2.
Both approaches have been discussed previously on this list so be sure to check the archives.
Best regards,
Jason
On Thu, Apr 24, 2014 at 8:30 PM, Emmanuel Willer CHARLES emmanuelwiller@gmail.com wrote:
Hi All,
we are having error to run analytic reports, it lasts longer than usual.
when we try to run a resource table, we have the following error message.
Please Help.
ERROR MESSAGE
Link to error message: http://gyazo.com/d50d019cb8374ec38dcf020488c0167a
Thank you for prompt answer.
Charles Emmanuel Willer
12,Lilavois 50 Croix des Bouquets, HTTels: +509-3842-3854 / +509-3270-0655
Skype: emmanuelwiller
emmanuelwiller@gmail.com
emmanuelwiller@yahoo.com
–
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
Hi,
This is related to a bug with analytics and sql views that was initially fixed but seems to have crept in again. It will need to be fixed – A temporary workaround is to delete sql views from your database.
…
Regards,
Dapo Adejumo
+2348033683677
skype : dapojorge
From: Dhis2-users [mailto:dhis2-users-bounces+dapo_adejumo=yahoo.com@lists.launchpad.net] On Behalf Of Emmanuel Willer CHARLES
Sent: 24 April 2014 15:31
To: DHIS 2 developers; dhis2-users@lists.launchpad.net; krendel@futuresgroup.com; Lars Helge Øverland; Knut Staring
Subject: [Dhis2-users] runing Analytics errors.
Hi All,
we are having error to run analytic reports, it lasts longer than usual.
when we try to run a resource table, we have the following error message.
Please Help.
ERROR MESSAGE
Link to error message: http://gyazo.com/d50d019cb8374ec38dcf020488c0167a
Thank you for prompt answer.
–
Charles Emmanuel Willer
12,Lilavois 50 Croix des Bouquets, HT
Tels: +509-3842-3854 / +509-3270-0655
Skype: emmanuelwiller
emmanuelwiller@gmail.com
Hi all,
It is surely right.
deleting the view we created has solved the problem.
is that bug fixed on one of the newer versions, as we are using ver 2.13 Build revision:12917.
thanks for your help guys.
On Thu, Apr 24, 2014 at 8:30 AM, Emmanuel Willer CHARLES emmanuelwiller@gmail.com wrote:
Hi All,
we are having error to run analytic reports, it lasts longer than usual.
when we try to run a resource table, we have the following error message.
Please Help.
ERROR MESSAGE
Link to error message: http://gyazo.com/d50d019cb8374ec38dcf020488c0167a
Thank you for prompt answer.
Charles Emmanuel Willer
12,Lilavois 50 Croix des Bouquets, HTTels: +509-3842-3854 / +509-3270-0655
Skype: emmanuelwiller
emmanuelwiller@gmail.com
emmanuelwiller@yahoo.com
–
–
Charles Emmanuel Willer
12,Lilavois 50 Croix des Bouquets, HT
Tels: +509-3842-3854 / +509-3270-0655
Skype: emmanuelwiller
emmanuelwiller@gmail.com
emmanuelwiller@yahoo.com
Hi,
This is not a bug, dhis drops the resource tables and needs to know about views depending on them. You can simply create the views as dhis sql views in data admin and it will be handled properly.
regards,
Lars
On Thu, Apr 24, 2014 at 8:30 AM, Emmanuel Willer CHARLES emmanuelwiller@gmail.com wrote:
Hi All,
we are having error to run analytic reports, it lasts longer than usual.
when we try to run a resource table, we have the following error message.
Please Help.
ERROR MESSAGE
Link to error message: http://gyazo.com/d50d019cb8374ec38dcf020488c0167a
Thank you for prompt answer.
Charles Emmanuel Willer
12,Lilavois 50 Croix des Bouquets, HTTels: +509-3842-3854 / +509-3270-0655
Skype: emmanuelwiller
emmanuelwiller@gmail.com
emmanuelwiller@yahoo.com
–
–
Charles Emmanuel Willer
12,Lilavois 50 Croix des Bouquets, HT
Tels: +509-3842-3854 / +509-3270-0655
Skype: emmanuelwiller
emmanuelwiller@gmail.com
emmanuelwiller@yahoo.com
Hi,
Not quite sure if this is a bug so I am testing out on this forum before reporting.
We have configured a multiple event tracker database to register clients (pregnant women) through the web-api. There are two enrolments used as test data at the moment.
The first set of aggregation queries should be fairly straightforward to count the number of enrolments (aggregate data elements have been created, included in data sets and assigned to orgunits).
In the aggregate query builder, I have chosen "Number of Tracked Entities", selected the program and used the program tab to define the condition. For program properties, I chose "Program Enrollment" and in the first instance the condition specified was only "Program Enrollment". The manual aggregation returned "no values". To explore another option, I tried "Program Enrollment ='true' but still no results.
To try a workaround, I used the attributes tab and selected one of the mandatory attributes with a condition of "attribute_name is not null". Now I got some very interesting results: a value of 2 for every single month in the period specified! The value is correct but not for multiple periods; I had used April 2012 to April 2014. Will not rule out that this has something to do with our database setup. When you expand the aggregation period to reveal the values and organisation unit, click on the "i" symbol under "Operator", you get an exception. In the log, the error message is:
* ERROR 2014-04-28 10:02:56,281 Error while executing action (ExceptionInterceptor.java [http-apr-8080-exec-6])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT p.name FROM programinstance as pi INNER JOIN trackedentityinstance p on p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN organisationunit ou ON ou.organisationunitid=p.organisationunitid WHERE EXISTS ( SELECT * FROM trackedentityattributevalue _pav WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=8818 AND _pav.value is not null ) ]; nested exception is org.postgresql.util.PSQLException: ERROR: column p.name does not exist.
Moving away from Program Attributes, I tried using the "Visited selected program stage" condition. When The Aggregator Operator is "Number of Tracked Entity", which is the most appropriate in this case, I get the error above. However, when I change to number of visits, this works fine but the log shows information that there is a data type mismatch i.e. not integer or number. I changed the aggregate data element to text and the aggregation value I got was "$value" This one seems like a bug to me. actual aggregation
Please find some screenshots attached to help illustrate the issues I have highlighted.
Any help of input would be appreciated. I have given lots of detail here to cover a spectrum of issues, I don't mind providing more specific information into aspects that may help illuminate what the problems are.
Best regards,
Busoye
Version:2.15
Build revision:14960
Aggregation Returns Multiple Period.tiff (85.3 KB)
Aggregation Value.tiff (29.1 KB)
Enrolment Aggregation error with Number of Visits.tiff (496 KB)
Aggregated Value data type mismatch.tiff (62.1 KB)
Hi Busoye,
Thanks for reporting this in such detail.
Some comments in-line.
On 28 April 2014 11:58, busoye@hisp.org wrote:
Hi,
Not quite sure if this is a bug so I am testing out on this forum before reporting.
We have configured a multiple event tracker database to register clients (pregnant women) through the web-api. There are two enrolments used as test data at the moment.
The first set of aggregation queries should be fairly straightforward to count the number of enrolments (aggregate data elements have been created, included in data sets and assigned to orgunits).
In the aggregate query builder, I have chosen “Number of Tracked Entities”, selected the program and used the program tab to define the condition. For program properties, I chose “Program Enrollment” and in the first instance the condition specified was only “Program Enrollment”. The manual aggregation returned “no values”.
That’s actually the correct way to set up the aggregation query as far as I know. I tried the same on another database and got the expected results. As you say it could be something with the database. Maybe you could try the same approach on another database to see whether it works there, e.g. give it a try on the online demo. Was this database recently upgraded from version 2.14, or has this been used with 2.15 or 2.15 snapshot since you started on it? Something might have happened during the upgrade process from 2.14 to 2.15.
To explore another option, I tried "Program Enrollment =‘true’ but still no results.
To try a workaround, I used the attributes tab and selected one of the mandatory attributes with a condition of “attribute_name is not null”. Now I got some very interesting results: a value of 2 for every single month in the period specified! The value is correct but not for multiple periods; I had used April 2012 to April 2014. Will not rule out that this has something to do with our database setup. When you expand the aggregation period to reveal the values and organisation unit, click on the “i” symbol under “Operator”, you get an exception. In the log, the error message is:
So your aggregated data element is in a data set with a monthly period type?
I can see that your query (that failed) doesn’t have any filters on period. That might be the reason for getting the save value for all the periods.
So, that sounds like a bug.
- ERROR 2014-04-28 10:02:56,281 Error while executing action (ExceptionInterceptor.java [http-apr-8080-exec-6])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT p.name FROM programinstance as pi INNER JOIN trackedentityinstance p on p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN organisationunit ou ON ou.organisationunitid=p.organisationunitid WHERE EXISTS ( SELECT * FROM trackedentityattributevalue _pav WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=8818 AND _pav.value is not null ) ]; nested exception is org.postgresql.util.PSQLException: ERROR: column p.name does not exist.
I also got this. There is a bug with the query here, as you can see column p.name does not exist. If you want to explore the results now you can run the sql query above directly on your database and just replace “select p.name” with “select p.*” - that will give all the tracked entity instances (persons in your case) that meets the aggregation criteria.
So you can use that query if you want to debug further what is happening with your aggregation queries.
I’ll do some more testing on my side as well, and I hope that the devs can have a look at these bugs quickly.
Ola
Moving away from Program Attributes, I tried using the “Visited selected program stage” condition. When The Aggregator Operator is “Number of Tracked Entity”, which is the most appropriate in this case, I get the error above. However, when I change to number of visits, this works fine but the log shows information that there is a data type mismatch i.e. not integer or number. I changed the aggregate data element to text and the aggregation value I got was “$value” This one seems like a bug to me. actual aggregation
Please find some screenshots attached to help illustrate the issues I have highlighted.
Any help of input would be appreciated. I have given lots of detail here to cover a spectrum of issues, I don’t mind providing more specific information into aspects that may help illuminate what the problems are.
Best regards,
Busoye
Version:2.15
Build revision:14960
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Thanks Ola,
Really useful comments.
I tried the Enrolment Query on another database, it returned accurate values but I got the “column p.name does not exist” error when I tried to get more information on the aggregation. This database was configured with 2.15 from the beginning.
Thanks for the tip on using the query directly in the database. I remember seeing this error before on 2.13 and was resolved eventually. Hope the devs can do the same with this quickly.
Cheers.
Busoye
On 28 April 2014 11:58, busoye@hisp.org wrote:
Hi,
Not quite sure if this is a bug so I am testing out on this forum before reporting.
We have configured a multiple event tracker database to register clients (pregnant women) through the web-api. There are two enrolments used as test data at the moment.
The first set of aggregation queries should be fairly straightforward to count the number of enrolments (aggregate data elements have been created, included in data sets and assigned to orgunits).
In the aggregate query builder, I have chosen “Number of Tracked Entities”, selected the program and used the program tab to define the condition. For program properties, I chose “Program Enrollment” and in the first instance the condition specified was only “Program Enrollment”. The manual aggregation returned “no values”.
That’s actually the correct way to set up the aggregation query as far as I know. I tried the same on another database and got the expected results. As you say it could be something with the database. Maybe you could try the same approach on another database to see whether it works there, e.g. give it a try on the online demo. Was this database recently upgraded from version 2.14, or has this been used with 2.15 or 2.15 snapshot since you started on it? Something might have happened during the upgrade process from 2.14 to 2.15.
To explore another option, I tried "Program Enrollment =‘true’ but still no results.
To try a workaround, I used the attributes tab and selected one of the mandatory attributes with a condition of “attribute_name is not null”. Now I got some very interesting results: a value of 2 for every single month in the period specified! The value is correct but not for multiple periods; I had used April 2012 to April 2014. Will not rule out that this has something to do with our database setup. When you expand the aggregation period to reveal the values and organisation unit, click on the “i” symbol under “Operator”, you get an exception. In the log, the error message is:
So your aggregated data element is in a data set with a monthly period type?
I can see that your query (that failed) doesn’t have any filters on period. That might be the reason for getting the save value for all the periods.
So, that sounds like a bug.
- ERROR 2014-04-28 10:02:56,281 Error while executing action (ExceptionInterceptor.java [http-apr-8080-exec-6])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT p.name FROM programinstance as pi INNER JOIN trackedentityinstance p on p.trackedentityinstanceid=pi.trackedentityinstanceid INNER JOIN organisationunit ou ON ou.organisationunitid=p.organisationunitid WHERE EXISTS ( SELECT * FROM trackedentityattributevalue _pav WHERE _pav.trackedentityinstanceid=pi.trackedentityinstanceid AND _pav.trackedentityattributeid=8818 AND _pav.value is not null ) ]; nested exception is org.postgresql.util.PSQLException: ERROR: column p.name does not exist.
I also got this. There is a bug with the query here, as you can see column p.name does not exist. If you want to explore the results now you can run the sql query above directly on your database and just replace “select p.name” with “select p.*” - that will give all the tracked entity instances (persons in your case) that meets the aggregation criteria.
So you can use that query if you want to debug further what is happening with your aggregation queries.
I’ll do some more testing on my side as well, and I hope that the devs can have a look at these bugs quickly.
Ola
Moving away from Program Attributes, I tried using the “Visited selected program stage” condition. When The Aggregator Operator is “Number of Tracked Entity”, which is the most appropriate in this case, I get the error above. However, when I change to number of visits, this works fine but the log shows information that there is a data type mismatch i.e. not integer or number. I changed the aggregate data element to text and the aggregation value I got was “$value” This one seems like a bug to me. actual aggregation
Please find some screenshots attached to help illustrate the issues I have highlighted.
Any help of input would be appreciated. I have given lots of detail here to cover a spectrum of issues, I don’t mind providing more specific information into aspects that may help illuminate what the problems are.
Best regards,
Busoye
Version:2.15
Build revision:14960
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
Thanks Ola,
Really useful comments.
I tried the Enrolment Query on another database, it returned accurate
values but I got the “column p.name does not exist” error when I tried to
get more information on the aggregation. This database was configured with
2.15 from the beginning.
Yes, that is a bug in the code related to the query being created with "
p.name", so doesn't matter which database your on. Shouldn't effect your
aggregation results though as this is the query to list the detailed events
contributing to the aggregated number. Should be fixed soon yes.
Enrolment Query Exception.tiff (172 KB)
On 28 April 2014 22:37, Adebusoye Anifalaje <busoye@hisp.org> wrote:
Thanks for the tip on using the query directly in the database. I remember
seeing this error before on 2.13 and was resolved eventually. Hope the devs
can do the same with this quickly.Cheers.
Busoye
On 28 Apr 2014, at 13:31, Ola Hodne Titlestad <olati@ifi.uio.no> wrote:
Hi Busoye,
Thanks for reporting this in such detail.
Some comments in-line.
On 28 April 2014 11:58, <busoye@hisp.org> wrote:
Hi,
Not quite sure if this is a bug so I am testing out on this forum before
reporting.We have configured a multiple event tracker database to register clients
(pregnant women) through the web-api. There are two enrolments used as test
data at the moment.The first set of aggregation queries should be fairly straightforward to
count the number of enrolments (aggregate data elements have been created,
included in data sets and assigned to orgunits).In the aggregate query builder, I have chosen "Number of Tracked
Entities", selected the program and used the program tab to define the
condition. For program properties, I chose "Program Enrollment" and in the
first instance the condition specified was only "Program Enrollment". The
manual aggregation returned "no values".That's actually the correct way to set up the aggregation query as far as
I know. I tried the same on another database and got the expected results.
As you say it could be something with the database. Maybe you could try the
same approach on another database to see whether it works there, e.g. give
it a try on the online demo. Was this database recently upgraded from
version 2.14, or has this been used with 2.15 or 2.15 snapshot since you
started on it? Something might have happened during the upgrade process
from 2.14 to 2.15.To explore another option, I tried "Program Enrollment ='true' but still
no results.To try a workaround, I used the attributes tab and selected one of the
mandatory attributes with a condition of "attribute_name is not null". Now
I got some very interesting results: a value of 2 for every single month in
the period specified! The value is correct but not for multiple periods; I
had used April 2012 to April 2014. Will not rule out that this has
something to do with our database setup. When you expand the aggregation
period to reveal the values and organisation unit, click on the "i" symbol
under "Operator", you get an exception. In the log, the error message is:So your aggregated data element is in a data set with a monthly period
type?
I can see that your query (that failed) doesn't have any filters on
period. That might be the reason for getting the save value for all the
periods.
So, that sounds like a bug.* ERROR 2014-04-28 10:02:56,281 Error while executing action
(ExceptionInterceptor.java [http-apr-8080-exec-6])
org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad
SQL grammar [SELECT p.name FROM programinstance as pi INNER JOIN
trackedentityinstance p on p.trackedentityinstanceid=pi.trackedentityinstanceid
INNER JOIN organisationunit ou ON ou.organisationunitid=p.organisationunitid
WHERE EXISTS ( SELECT * FROM trackedentityattributevalue _pav WHERE
_pav.trackedentityinstanceid=pi.trackedentityinstanceid AND
_pav.trackedentityattributeid=8818 AND _pav.value is not null ) ];
nested exception is org.postgresql.util.PSQLException: ERROR: column
p.name does not exist.I also got this. There is a bug with the query here, as you can see column
p.name does not exist. If you want to explore the results now you can
run the sql query above directly on your database and just replace "select
p.name" with "select p.*" - that will give all the tracked entity
instances (persons in your case) that meets the aggregation criteria.So you can use that query if you want to debug further what is happening
with your aggregation queries.I'll do some more testing on my side as well, and I hope that the devs can
have a look at these bugs quickly.Ola
----------Moving away from Program Attributes, I tried using the "Visited selected
program stage" condition. When The Aggregator Operator is "Number of
Tracked Entity", which is the most appropriate in this case, I get the
error above. However, when I change to number of visits, this works fine
but the log shows information that there is a data type mismatch i.e. not
integer or number. I changed the aggregate data element to text and the
aggregation value I got was "$value" This one seems like a bug to me.
actual aggregationPlease find some screenshots attached to help illustrate the issues I
have highlighted.Any help of input would be appreciated. I have given lots of detail here
to cover a spectrum of issues, I don't mind providing more specific
information into aspects that may help illuminate what the problems are.Best regards,
Busoye
Version:2.15
Build revision:14960_______________________________________________
Mailing list: DHIS 2 developers in Launchpad
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : DHIS 2 developers in Launchpad
More help : ListHelp - Launchpad Help
Hi all,
We need to implement a system for collecting maternal and deceases cases and nutrition . We want to use the sms service of DHIS and i want to know for that what we need to have for this implementation. Is DHIS2 SMS service like frontlinesms (just need a laptop or a server if you want, a modem, a phone (and phone number) or is it diff�rent in DHIS2
Sorry for my poor english, William
Hi Quango,
The things you may need are:
An running DHIS2 server
An BulkSms or Clickatell (or any kind of HTTP support sms service) or Physical GSM modem (not recommended)
Proper configuration which you can find here: http://www.dhis2.org/doc/snapshot/en/user/html/ch24.html
Best regards,
Long
On Wed, Jun 4, 2014 at 4:21 PM, OUANGO William williamouango@yahoo.fr wrote:
Hi all,
We need to implement a system for collecting maternal and deceases cases and nutrition . We want to use the sms service of DHIS and i want to know for that what we need to have for this implementation. Is DHIS2 SMS service like frontlinesms (just need a laptop or a server if you want, a modem, a phone (and phone number) or is it différent in DHIS2
Sorry for my poor english, William
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
–
Hi William,
thanks for your interest. It sounds like DHIS can do what you are looking for. It would be nice with a bit more info about your projects and your goals in order to help you.
regards,
Lars
On Wed, Jun 4, 2014 at 11:28 AM, Long Ngo Thanh long.hispvietnam@gmail.com wrote:
Hi Quango,
The things you may need are:
- An running DHIS2 server
- An BulkSms or Clickatell (or any kind of HTTP support sms service) or Physical GSM modem (not recommended)
- Proper configuration which you can find here: http://www.dhis2.org/doc/snapshot/en/user/html/ch24.html
Best regards,
Long
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
On Wed, Jun 4, 2014 at 4:21 PM, OUANGO William williamouango@yahoo.fr wrote:
Hi all,
We need to implement a system for collecting maternal and deceases cases and nutrition . We want to use the sms service of DHIS and i want to know for that what we need to have for this implementation. Is DHIS2 SMS service like frontlinesms (just need a laptop or a server if you want, a modem, a phone (and phone number) or is it différent in DHIS2
Sorry for my poor english, William
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help : https://help.launchpad.net/ListHelp
–
Hi Lars,
It's a real pleasure for me to know that you are interested by our project and i'll give you more informations soon.
Thank's for taking time to help all of us
William
Thanks.
On Wed, Jun 18, 2014 at 11:24 AM, OUANGO William williamouango@yahoo.fr wrote:
Hi Lars,
It's a real pleasure for me to know that you are interested by our project and i'll give you more informations soon.Thank’s for taking time to help all of us
William
