dhis2-devs team mailing list archive

["Tran Chau (HISP Vietnam)" <tran.hispvietnam@xxxxxxxxx>]

Hi Shantala,

I took a look at your database and run it on 2.19 version.

It seems that you want to overwrite some data in aggregate side (dataSet
values) by removing events on event side and running the manual
aggregation, correct?

If so, DHIS 'aggregation' from event side does not work like that.
If there is no events, the aggregation process simply skips aggregation for
that.  It does not write over '0' values to aggregation side (dataSet
values).


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




On Tue, May 19, 2015 at 9:42 AM, Tran Chau (HISP Vietnam) <
tran.hispvietnam@xxxxxxxxx> wrote:

> HI Shantala,
>
> I will take a look at it couple days later. I will inform you if I find
> out the issue.
>
> ...........................................
> Best regards,
> Tran Chau
>
>
> On Mon, May 18, 2015 at 7:54 PM, Shantala Raman <sraman@xxxxxxxxxxxxxxxx>
> wrote:
>
>> Hi Tran,
>>
>> Here <https://www.dropbox.com/s/zywgr1sys7crx4k/dhis2_db.dump?dl=0> is
>> the link to the DB dump of DHIS in dropbox where we have setup the data
>> needed to replicate the defect. We have associated the *Cholera CTC*
>> program under the organization hierarchy, MSF->OCP->A new country->A new
>> project->New Hospital->Cholera->Origin 1. The program is of type *Single
>> event without registration*.
>>
>> Steps to reproduce the defect:
>>
>>    1. In event capture, register an event for the *Cholera CTC* program
>>    under the *Origin 1* org unit.
>>    2. Preview the manual aggregation results for *Geographic Origin*
>>    data set for *Origin 1* org unit for the week of the *Incident date*
>>    and save all values.
>>    3. In data entry, you will see that the value of the *Number of
>>    Patients (Cholera CTC) *data element for the *Geographic Origin* data
>>    set for the week of the *Incident date* is 1.
>>    4. In event capture, remove the event for the *Cholera CTC* program
>>    under the *Origin 1* org unit.
>>    5. Preview the manual aggregation results for *Geographic Origin*
>>    data set for *Origin 1* org unit for the week of the *Incident date*.
>>    This won't return any changes to be updated while the expected behavior is
>>    that preview shows that the value for the *Number of Patients
>>    (Cholera CTC)* data element will change from 1 to 0. Save all values.
>>    6. In data entry, you will see the value of the *Number of Patients
>>    (Cholera CTC)* data element remains as 1 for the *Geographic Origin*
>>    data set for the week while the expected behavior is that the value of the *Number
>>    of Patients (Cholera CTC)* data element is 0.
>>
>> Thanks,
>> Shantala
>>
>>
>> On Tue, May 12, 2015 at 3:05 PM, Shantala Raman <sraman@xxxxxxxxxxxxxxxx>
>> wrote:
>>
>>> Hi Tran,
>>>
>>> We are on 2.19 rev 19092. We are not using SQL queries for generating
>>> summary data values. We are using the aggregation query builder to setup
>>> the queries. Could you explain how the SQL query is linked to aggregation
>>> query builder?
>>>
>>> Thanks,
>>> Shantala
>>>
>>> On Mon, May 11, 2015 at 7:10 PM, Tran Chau (HISP Vietnam) <
>>> tran.hispvietnam@xxxxxxxxx> wrote:
>>>
>>>> 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
>>>>>>>>>
>>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>
>>>>
>>>
>>
>