← Back to team overview

dhis2-devs team mailing list archive

Re: Defect with the Aggregation Query feature?

 

Hi Shantala,

The first, please make sure if the snapshots are from 2.19 version, R
19075  ?  It looks like old version.


I couldn't create an example on dev server ( apps.dhis2.org/dev ) because
the internet is still slow here. I cannot login the dev server to create an
example.

I am sending the SQL statement which I used to generate the data values
from aggregate query formulas.

Can you change the IDs in the SQL statement below according to one of your
formula and see if it is generated "NULL" value records or not  ( these
NULL values will be displayed/saved as "0" values in Manual Aggregate )


 SELECT '55' as dataelementid, '16' as categoryoptioncomboid,
'16' as attributeoptioncomboid, ou.organisationunitid as sourceid,
'1234' as periodid,'aggregated_from_tracker' as storedby,
'201505' as periodIsoDate,'Agg DE 1' as dataelementname,
'(default)' as categoryoptioncomboname, ou.name as organisationunitname,
( select  count(psi.programinstanceid) as value FROM  programinstance as pi

INNER JOIN programstageinstance psi ON
pi.programinstanceid=psi.programinstanceid
 WHERE  EXISTS ( SELECT * FROM trackedentitydatavalue _pdv
inner join programstageinstance _psi ON
_pdv.programstageinstanceid=_psi.programstageinstanceid
JOIN programinstance _pi ON _pi.programinstanceid=_psi.programinstanceid
WHERE psi.programstageinstanceid=_pdv.programstageinstanceid
AND _pdv.dataelementid=53  AND _psi.organisationunitid in (50, 51, 52)
AND _pi.programid = 60 AND _psi.executionDate>='2015-05-01'
AND _psi.executionDate <= '2015-05-31'
AND _psi.programstageid = 63 AND _pdv.value   is not null  )
 and psi.organisationunitid=ou.organisationunitid

GROUP BY ou.organisationunitid ) from organisationunit ou where
ou.organisationunitid in ( 50, 51, 52 )


...............................
Best regards
Tran Chau

On Mon, May 11, 2015 at 8:02 PM, Tran Chau (HISP Vietnam) <
tran.hispvietnam@xxxxxxxxx> wrote:

> Hi Shantala,
>
> Are you sure if the snapshots are from 2.19 version, R 19075  ?
> It looks like old version.
>
> ...................
> Best regards,
> Tran Chau
> Hi Tran,
>
> We are still seeing issues with the aggregation. We followed the below
> steps -
>
>    - Created a data element with *Store zero data values* property set to
>    Yes
>    - Created an aggregation query for the data element
>    - Created 3 events for the program of type* single event without
>    registration* - 2 events under orgunit 1 (origin 1) and 1 event under
>    orgunit 2 (origin 2)
>    - Ran manual aggregation for both org units
>    - Deleted the event under org unit 2 (origin 2)
>    - Ran manual aggregation for both org units
>
> We observe that the data entry screen for org unit 2 (origin 2) still has
> the number of events as 1.
>
> Please find attached the screenshots for the various steps. Please let us
> know if you need any other information.
>
> Thanks,
> Shantala & Shashank
>
>
> On Fri, May 8, 2015 at 8:12 AM, Tran Chau (HISP Vietnam) <
> tran.hispvietnam@xxxxxxxxx> wrote:
>
>> HI Simata,
>>
>> Yes, this bug is fixed on 19075, trunk version.
>>
>> I created an example on dev server ( https://apps.dhis2.org/dev/ ) for
>> testing.
>>
>> I am sending snapshot about the test step by step on dev server here.
>>
>>
>> *Step 1 - Create a data element and an aggregate query builder formula*
>>
>> *Step 1.1* - Create a data element with* '**Store zero data values' *property as
>> '*Yes' ( see snapshot [1.1] ). And add it into a dataset.*
>>
>> *Step 1.2*  - Defined an aggregate query builder formula* ( see snapshot
>> [1.2] )*
>>
>>
>> *Step 2 - Generate data values*
>>
>> *Step 2.1* - Create a  new person and an event in April 2015. Fill a
>> data value for data element which I used in Step 1.2  ( see snapshot [
>> *2.1*] )
>>
>> *Step 2.2 *- Run Aggregate Manual ( see snapshot [*2.2*]  )
>>
>> *Step 2.3 *- Result after clicking "Preview" button ( see snapshot [*2.3*
>> ]  )
>>
>> *Step 2.4* - Data values imported in the data set  ( see snapshot [*2.4*]
>>  ,  [*2.5*] )
>>
>>
>> *Step 3 - Modified data value of event in Step 2.1 and see the result
>> after aggregate*
>>
>> *Step 3.1* - Remove the data which was filled in Step* 2.1* ( see
>> snapshot [*3.1*] )
>>
>> *Step 32 *- Run Aggregate Manual ( see snapshot [*3.2*]  )
>>
>> *Step 3.3 *- Result after clicking "*Preview*" button ( see snapshot [
>> *3.3*]  )
>>
>> *Step 3.4* - Data values imported in the data set  ( see snapshot [*3.4*]
>>  ,  [*3.5*] ).
>>
>> We can see the data value in April ( snapshot [*3.4*] ) is 0 now )
>>
>> .....................................................................
>>
>> If you got different result, send me the images and description like
>> these.
>>
>> We will take a look at it.
>>
>> .............................................................
>>
>> Best regards,
>> Tran Chau.
>> On May 7, 2015 1:03 PM, "Simanta Jyoti Handique" <
>> shandique@xxxxxxxxxxxxxxxx> wrote:
>>
>>> Hello,
>>>
>>> We were wondering if this bug has been fixed as part of rev #19075. We
>>> are on rev #19097 and are still facing this issue
>>>
>>> Regards,
>>> Simanta
>>>
>>>
>>> On Tue, Apr 28, 2015 at 5:51 PM, Tran Chau (HISP Vietnam) <
>>> tran.hispvietnam@xxxxxxxxx> wrote:
>>>
>>>> HI Simanta and others,
>>>>
>>>> The issue is in Manual Aggregate, the "0" values are not
>>>> displayed/saved.
>>>> I am working on it to display and save "0" values in Manual Aggregate
>>>> functionality.
>>>>
>>>>
>>>> Thank you for reporting this bug.
>>>>
>>>> ..........................................
>>>> Best regards,
>>>> Tran Chau
>>>>
>>>> On Mon, Apr 27, 2015 at 5:48 PM, Simanta Jyoti Handique <
>>>> shandique@xxxxxxxxxxxxxxxx> wrote:
>>>>
>>>>> Hi, thanks for the response. The data elements were already configured
>>>>> to store zero data values.
>>>>>
>>>>> This may be another symptom to the problem: If I run the aggregation
>>>>> manually and preview the data that will be saved, there are no results with
>>>>> 0 values
>>>>>
>>>>> Regards,
>>>>> Simanta
>>>>>
>>>>>
>>>>>
>>>>> On Mon, Apr 27, 2015 at 1:58 PM, Adebusoye Anifalaje <busoye@xxxxxxxx>
>>>>> wrote:
>>>>>
>>>>>> Sorry,
>>>>>>
>>>>>> Just re read your question. My mistake. To overwrite values with 0,
>>>>>> you have to choose to store zero data values when creating the
>>>>>> aggregate data element.
>>>>>>
>>>>>>
>>>>>> On Monday, April 27, 2015, Adebusoye Anifalaje <busoye@xxxxxxxx>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi Simanta,
>>>>>>>
>>>>>>> You should try changing the Operator to Number of tracked entities.
>>>>>>> Visits are technically for program stages and since you are not registering
>>>>>>> programs then you can't have visits.
>>>>>>>
>>>>>>> Try tracked entities and let's know whether it worked.
>>>>>>>
>>>>>>> Cheers.
>>>>>>>
>>>>>>> Busoye
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> On Monday, April 27, 2015, Simanta Jyoti Handique <
>>>>>>> shandique@xxxxxxxxxxxxxxxx> wrote:
>>>>>>>
>>>>>>>> Hi,
>>>>>>>>
>>>>>>>> We have set up an aggregation query with the following details:
>>>>>>>>
>>>>>>>> Name: Number of Patients (Measles Outbreak)
>>>>>>>> Operator: Number of Visits
>>>>>>>> Data set: Geographic Origin
>>>>>>>> Data Element: Number of Patients (Measles Outbreak) - Geographic
>>>>>>>> Origin (Number)
>>>>>>>> Condition: [Measles Outbreak.Measles Outbreak stage.Date of
>>>>>>>> admission] is not null
>>>>>>>>
>>>>>>>> "Measles Outbreak" is a Single Event without Registration type
>>>>>>>> program. "Date of admission" is a Data Element of type Date
>>>>>>>>
>>>>>>>> We register an event, run the aggregation query and the "Number of
>>>>>>>> Patients (Measles Outbreak) - Geographic Origin (Number)" data element is
>>>>>>>> correctly populated with the count of the number of patients. We delete the
>>>>>>>> event and re-run the query. The data element for the number of patients
>>>>>>>> does not get updated with the value 0.
>>>>>>>>
>>>>>>>> Is this a defect with the Aggregation Query feature or is there
>>>>>>>> something wrong with the formula we're using?
>>>>>>>>
>>>>>>>> Regards,
>>>>>>>> Simanta
>>>>>>>>
>>>>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> 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
>>>>>
>>>>>
>>>>
>>>
>

Follow ups

References