dhis2-devs team mailing list archive

[Ola Hodne Titlestad <olati@xxxxxxxxxx>]

Hi Busoye,

Thanks for reporting this in such detail.

Some comments in-line.

On 28 April 2014 11:58, <busoye@xxxxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>