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.
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)