Aggregation Query Builder doesn't count

Hello Friends.

After migrate to 2.12 version, the aggregation query builder doesn't aggrega=
te the values in the data element. We test with new data elements, simple q=
ueries but doesn't work. Maybe the new version has other parameters to agg=
regate the values?.

Thank you very much.
=20

--=20
Regards,

Ing. Marko David Garcia M.
=C3=81rea de Sistemas integrados de Informaci=C3=B3n
Secretar=C3=ADa de Salud Departamental del Cauca

_______________________________________________ Mailing list: https://launch=
pad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe :=
https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/L=
istHelp

_______________________________________________ Mailing list: https://launch=
pad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe :=
https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/L=
istHelp

···

_______________________________________________
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

_______________________________________________ Mailing list: https://launch=
pad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe :=
https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/L=
istHelp=

--Apple-Mail-B8354B69-F15F-4DE0-B1D2-D8D4E94C67DD
Content-Type: text/html;
  charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto"><div style=3D"-webkit-text-size-adjust: aut=
o; ">Hi Helder,</div><div style=3D"-webkit-text-size-adjust: auto; "><br></d=

<div style=3D"-webkit-text-size-adjust: auto; ">This bug was fixed for on=

ly 1.12 version. For 2.13, we have a plan to improve it by using web-API. An=
d I am working on it.</div><div style=3D"-webkit-text-size-adjust: auto; "><=

</div><div><span style=3D"-webkit-text-size-adjust: auto;">I tested this b=

ug on demo server ( with revision&nbsp;</span><span style=3D"-webkit-text-si=
ze-adjust: auto; background-color: rgba(255, 255, 255, 0);">11292 &nbsp;in t=
his morning), it works fine. Can you help me to test it again ?</span></div>=
<div style=3D"-webkit-text-size-adjust: auto; "><font face=3D"LiberationSans=
Regular, arial, sans-serif"><span style=3D"line-height: 25px; -webkit-tap-hi=
ghlight-color: rgba(26, 26, 26, 0.292969); -webkit-composition-fill-color: r=
gba(175, 192, 227, 0.230469); -webkit-composition-frame-color: rgba(77, 128,=
180, 0.230469);"><br></span></font><br>Best regard,</div><div style=3D"-web=
kit-text-size-adjust: auto; "><br>On 30-08-2013, at 23:42, helder yesid cast=
rillon cobo &lt;<a href=3D"mailto:helder_947@hotmail.com">helder_947@hotmail=
.com</a>&gt; wrote:<br><br></div><div style=3D"-webkit-text-size-adjust: aut=
o; ">

<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style>
<div dir=3D"ltr">Hi,<br>I downloaded DHIS 2.13-SNAPSHOT with revision 11846 f=
rom <a href=3D"http://apps.dhis2.org/ci/job/dhis-web-2.12/" target=3D"_blank=
">http://apps.dhis2.org/ci/job/dhis-web-2.12/</a>&nbsp; to test, but&nbsp; i=
n this revision the error persist, also we tested in the demo of DHIS to dis=
card errors in ours Data, obtaining the same error.<br><br><br><br><br>Mg. H=
elder Yesid Castrillon.<br>

Sistemas de informaci=C3=B3n<br>Secretar=C3=ADa departamental de salud del c=
auca.<br><br><br><div><hr id=3D"stopSpelling">From: <a href=3D"mailto:helder=
_947@hotmail.com">helder_947@hotmail.com</a><br>To: <a href=3D"mailto:tran.h=
ispvietnam@gmail.com">tran.hispvietnam@gmail.com</a><br>Date: Fri, 30 Aug 20=
13 15:17:29 +0200<br>CC: <a href=3D"mailto:dhis2-users@lists.launchpad.net">=
dhis2-users@lists.launchpad.net</a><br>Subject: Re: [Dhis2-users] Aggregatio=
n Query Builder doesn't count<br><br>

<style><!--
.ExternalClass .ecxhmmessage P {
padding:0px;
}

.ExternalClass body.ecxhmmessage {
font-size:12pt;
font-family:Calibri;
}

--></style>
<div dir=3D"ltr">Ok, I tested with revisions 11245 and 11290&nbsp; but the e=
rror persist, today I downloaded Dhis the web site (<a href=3D"http://www.dh=
is2.org/downloads">http://www.dhis2.org/downloads</a>), however it is the sa=
me revision (11290)<br>How I download DHIS 2.12 with revision 11291?.<br><br=

Mg. Helder Yesid Castrillon.<br>

Sistemas de informaci=C3=B3n<br>Secretar=C3=ADa departamental de salud del c=
auca.<br><br><br><div><hr id=3D"ecxstopSpelling">From: <a href=3D"mailto:tra=
n.hispvietnam@gmail.com">tran.hispvietnam@gmail.com</a><br>Date: Fri, 30 Aug=
2013 10:48:17 +0700<br>Subject: Re: [Dhis2-users] Aggregation Query Builder=
doesn't count<br>To: <a href=3D"mailto:helder_947@hotmail.com">helder_947@h=
otmail.com</a><br>CC: <a href=3D"mailto:markodavidgm@gmail.com">markodavidgm=
@gmail.com</a>; <a href=3D"mailto:dhis2-users@lists.launchpad.net">dhis2-use=
rs@lists.launchpad.net</a><br><br><div dir=3D"ltr">Hi Helder,<div><br></div>=
<div>It's fixed for 2.12 with revision&nbsp;11291. Please help me to re-test=
it on your side. Thank you.</div></div><div class=3D"ecxgmail_extra"><br cl=
ear=3D"all"><div>Best regards,<br>------------------------------------------=
------<br>

Ch=C3=A2u Thu Tr=C3=A2n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbs=
p; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>HISP Viet Nam=
<br>Email: <a href=3D"mailto:tran.hispvietnam@gmail.com" target=3D"_blank">t=
ran.hispvietnam@gmail.com</a></div>
<br><br><div class=3D"ecxgmail_quote">On Fri, Aug 30, 2013 at 3:49 AM, helde=
r yesid castrillon cobo <span dir=3D"ltr">&lt;<a href=3D"mailto:helder_947@h=
otmail.com" target=3D"_blank">helder_947@hotmail.com</a>&gt;</span> wrote:<b=

<blockquote class=3D"ecxgmail_quote" style=3D"border-left:1px #ccc solid;p=

adding-left:1ex;">

<div><div dir=3D"ltr">It is not our case, all the formulas has apostroph (')=
in the values. We have identified that the bug is when we do aggregation qu=
ery using the patients attributes created by us.<br><br><br><br>Mg. Helder Y=
esid Castrillon.<br>

Sistemas de informaci=C3=B3n<br>Secretar=C3=ADa departamental de salud del c=
auca.<br><br><br><br><div><hr>From: <a href=3D"mailto:tran.hispvietnam@gmail=
.com" target=3D"_blank">tran.hispvietnam@gmail.com</a><br>Date: Wed, 28 Aug 2=
013 15:39:52 +0700<div class=3D"ecxim">

<br>Subject: Re: [Dhis2-users] Aggregation Query Builder doesn't count<br></=

To: <a href=3D"mailto:markodavidgm@gmail.com" target=3D"_blank">markodav=

idgm@gmail.com</a><br>CC: <a href=3D"mailto:helder_947@hotmail.com" target=3D=
"_blank">helder_947@hotmail.com</a>; <a href=3D"mailto:dhis2-users@lists.lau=
nchpad.net" target=3D"_blank">dhis2-users@lists.launchpad.net</a><div>

<div class=3D"h5"><br><br><div dir=3D"ltr">Hi Marko,<br><br>It works with fo=
rmula [CA:43567] =3D 'Gender'. For formulas like this [CA:43567] =3D Gender (=
Gender didn't put between apostroph ), formulas didn't work. So if you have a=
ny formula similar to the second case, please put value to compare between a=
postroph (') . This bug will be fixed for 2.13 version as plan.<br>

<br>Thank you, Marko.<br></div><div><br clear=3D"all"><div>Best regards,<br>=
------------------------------------------------<br>Ch=C3=A2u Thu Tr=C3=A2n&=
nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &=
nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <br>HISP Viet Nam<br>Email: <a href=3D"mai=
lto:tran.hispvietnam@gmail.com" target=3D"_blank">tran.hispvietnam@gmail.com=
</a></div>

<br><br><div>On Tue, Aug 27, 2013 at 9:15 PM, Marko David Garcia Mu=C3=B1oz <=
span dir=3D"ltr">&lt;<a href=3D"mailto:markodavidgm@gmail.com" target=3D"_bl=
ank">markodavidgm@gmail.com</a>&gt;</span> wrote:<br><blockquote style=3D"bo=
rder-left:1px #ccc solid;padding-left:1ex;">

<div style=3D"word-wrap:break-word;">Thank you Tran.<div><br></div><div>"Reg=
imen" is patient attribute created by Us, when we try to test the condition t=
he system show the warning window.</div><div>For Example if we use "Genero" =3D=
Gender, or if we use the dataelement parameter ('210 - Dengue') the query w=
orks fine.</div>

<div><br></div><div>Thank you very much.<br><div><br></div><div>&lt;Captura d=
e pantalla 2013-08-27 a la(s) 8.52.00.png&gt;<br><div>
<div style=3D"text-indent:0px;letter-spacing:normal;font-variant:normal;text=
-align:-webkit-auto;font-style:normal;font-weight:normal;line-height:normal;=
text-transform:none;font-size:medium;white-space:normal;font-family:Helvetic=
a;word-wrap:break-word;word-spacing:0px;">

<div style=3D"text-indent:0px;letter-spacing:normal;font-variant:normal;text=
-align:-webkit-auto;font-style:normal;font-weight:normal;line-height:normal;=
text-transform:none;font-size:medium;white-space:normal;font-family:Helvetic=
a;word-wrap:break-word;word-spacing:0px;">

Cordialmente,<br><br>Ing. Marko David Garcia Mu=C3=B1oz</div><div style=3D"t=
ext-indent:0px;letter-spacing:normal;font-variant:normal;text-align:-webkit-=
auto;font-style:normal;font-weight:normal;line-height:normal;text-transform:=
none;font-size:medium;white-space:normal;font-family:Helvetica;word-wrap:bre=
ak-word;word-spacing:0px;">

Secretar=C3=ADa Departamental de Salud del Cauca</div><div style=3D"text-ind=
ent:0px;letter-spacing:normal;font-variant:normal;text-align:-webkit-auto;fo=
nt-style:normal;font-weight:normal;line-height:normal;text-transform:none;fo=
nt-size:medium;white-space:normal;font-family:Helvetica;word-wrap:break-word=
;word-spacing:0px;">

=C3=81rea de Sistemas Integrados de Informaci=C3=B3n</div><div style=3D"text=
-indent:0px;letter-spacing:normal;font-variant:normal;text-align:-webkit-aut=
o;font-style:normal;font-weight:normal;line-height:normal;text-transform:non=
e;font-size:medium;white-space:normal;font-family:Helvetica;word-wrap:break-=
word;word-spacing:0px;">

Skype: marko_david<br><br><br></div></div>
</div>
<br><div><div>El 26/08/2013, a las 23:19, Tran Chau &lt;<a href=3D"mailto:tr=
an.hispvietnam@gmail.com" target=3D"_blank">tran.hispvietnam@gmail.com</a>&g=
t; escribi=C3=B3:</div><div><div><br><blockquote><div dir=3D"ltr">

Dear Marko and&nbsp;Helder,<br><div><br></div><div>Could you please send to m=
e the formula you created. I can try to test with this formula in my laptop.=
</div><div><br></div><div>Thank you.</div></div><div>

<br clear=3D"all"><div>Best regards,<br>------------------------------------=
------------<br>Ch=C3=A2u Thu Tr=C3=A2n&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &n=
bsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <=

HISP Viet Nam<br>Email: <a href=3D"mailto:tran.hispvietnam@gmail.com" tar=

get=3D"_blank">tran.hispvietnam@gmail.com</a></div>

<br><br><div>On Tue, Aug 27, 2013 at 5:47 AM, helder yesid castrillon cobo <=
span dir=3D"ltr">&lt;<a href=3D"mailto:helder_947@hotmail.com" target=3D"_bl=
ank">helder_947@hotmail.com</a>&gt;</span> wrote:<br><blockquote style=3D"bo=
rder-left:1px #ccc solid;padding-left:1ex;">

<div><div dir=3D"ltr">sorry!!<br><br>SEX =3D gender<br>In spanish genero, se=
xo.<br><br>ING. Helder Yesid Castrillon.<br><div><br><br><br></div><div><div=

<hr>From: <a href=3D"mailto:helder_947@hotmail.com" target=3D"_blank">helde=

r_947@hotmail.com</a><br>

To: <a href=3D"mailto:markodavidgm@gmail.com" target=3D"_blank">markodavidgm=
@gmail.com</a>; <a href=3D"mailto:dhis2-users@lists.launchpad.net" target=3D=
"_blank">dhis2-users@lists.launchpad.net</a><br>Date: Tue, 27 Aug 2013 00:43=
:56 +0200<br>

Subject: Re: [Dhis2-users] Aggregation Query Builder doesn't count</div><div=

<div><br><br>

<div dir=3D"ltr">Hi, We have reviewed in detail and we found that the proble=
m is presented with aggregation query that use the attribute of patient that=
we have created. <br>The postgres console print the next error.<br>&nbsp;<b=

<b>ERROR: syntax error at or near ")" at character 617<br>STATEMENT: SELECT '=
0' as dataelementid, '0' as categoryoptioncomboid, ou.organisationunitid as s=
ourceid, '0' as periodid,'DHIS-SYSTEM' as comment, '201308' as periodIsoDate=
,'dataelementname' as dataelementname, 'optioncomboid' as categoryoptioncomb=
oname, <a href=3D"http://ou.name/" target=3D"_blank">ou.name</a> as organisa=
tionunitname, count (psi.programinstanceid ) as value FROM programstageinsta=
nce as psi INNER JOIN organisationunit ou on ou.organisationunitid=3Dpsi.org=
anisationunitid WHERE EXISTS ( SELECT * FROM patientattributevalue _pav WHER=
E _pav.patientid =3D pi.patientid and _pav.patientattributeid=3D280 AND p.or=
ganisationunitid in (0) AND _pav.value )=3D'Especial' ) GROUP BY ou.organisa=
tionunitid, <a href=3D"http://ou.name/" target=3D"_blank">ou.name</a></b><br=

<br>the error is generated when we edit the aggregation query, but it is don=
't generated with aggregation query that use the attibutes of patient that i=
t is by default. (Sex).<br><br>Thanks, <br><br>ING. Helder Yesid Castrillon.=
<br>

Secretar=C3=ADa departamental de salud<br><br><br><div><hr>Date: Mon, 26 Aug=
2013 09:21:39 -0500<br>From: <a href=3D"mailto:markodavidgm@gmail.com" targ=
et=3D"_blank">markodavidgm@gmail.com</a><br>To: <a href=3D"mailto:dhis2-user=
s@lists.launchpad.net" target=3D"_blank">dhis2-users@lists.launchpad.net</a>=
<br>

Subject: [Dhis2-users] Aggregation Query Builder doesn't count<br><br><div d=
ir=3D"ltr">Hello Friends.<div><br></div><div>After migrate to 2.12 version, t=
he aggregation query builder doesn't aggregate the values in the data elemen=
t. &nbsp;We test with new data elements, simple queries but doesn't work. &n=
bsp; Maybe the new version has other parameters to aggregate the values?.</d=

<div><br></div><div>Thank you very much.</div><div>&nbsp;<br clear=3D"all"><=

<br></div>-- <br>Regards,<div><br><br><font face=3D"arial, helvetica, sa=

ns-serif"><b><font color=3D"#336666">Ing. Marko David Garcia M.<br></font></=

</font><div>

<font face=3D"arial, helvetica, sans-serif"><b><font color=3D"#336666">=C3=81=
rea de Sistemas integrados de Informaci=C3=B3n<br>Secretar=C3=ADa de Salud D=
epartamental del Cauca<br><font color=3D"#3333ff"><br></font></font></b></fo=

<br></div>

</div>
</div></div>
<br>_______________________________________________
Mailing list: <a href=3D"https://launchpad.net/~dhis2-users" target=3D"_blan=
k">https://launchpad.net/~dhis2-users</a>
Post to : <a href=3D"mailto:dhis2-users@lists.launchpad.net" target=3D"_=
blank">dhis2-users@lists.launchpad.net</a>
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-users" target=3D"_blan=
k">https://launchpad.net/~dhis2-users</a>
More help : <a href=3D"https://help.launchpad.net/ListHelp" target=3D"_bla=
nk">https://help.launchpad.net/ListHelp</a></div> =
      </div>
<br>_______________________________________________
Mailing list: <a href=3D"https://launchpad.net/~dhis2-users" target=3D"_blan=
k">https://launchpad.net/~dhis2-users</a>
Post to : <a href=3D"mailto:dhis2-users@lists.launchpad.net" target=3D"_=
blank">dhis2-users@lists.launchpad.net</a>
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-users" target=3D"_blan=
k">https://launchpad.net/~dhis2-users</a>
More help : <a href=3D"https://help.launchpad.net/ListHelp" target=3D"_bla=
nk">https://help.launchpad.net/ListHelp</a></div></div></div> =
           </div></div>
<br>_______________________________________________<br>
Mailing list: <a href=3D"https://launchpad.net/~dhis2-users" target=3D"_blan=
k">https://launchpad.net/~dhis2-users</a><br>
Post to &nbsp; &nbsp; : <a href=3D"mailto:dhis2-users@lists.launchpad.net" t=
arget=3D"_blank">dhis2-users@lists.launchpad.net</a><br>
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-users" target=3D"_blan=
k">https://launchpad.net/~dhis2-users</a><br>
More help &nbsp; : <a href=3D"https://help.launchpad.net/ListHelp" target=3D=
"_blank">https://help.launchpad.net/ListHelp</a><br>
<br></blockquote></div><br></div>
</blockquote></div></div></div><br></div></div></div></blockquote></div><br>=
</div></div></div></div> </div></d=

</blockquote></div><br></div></div> <=
/div>
<br>_______________________________________________
Mailing list: <a href=3D"https://launchpad.net/~dhis2-users">https://launchp=
ad.net/~dhis2-users</a>
Post to : <a href=3D"mailto:dhis2-users@lists.launchpad.net">dhis2-users=
@lists.launchpad.net</a>
Unsubscribe : <a href=3D"https://launchpad.net/~dhis2-users">https://launchp=
ad.net/~dhis2-users</a>
More help : <a href=3D"https://help.launchpad.net/ListHelp">https://help.l=
aunchpad.net/ListHelp</a></div> <=
/div>
</div></body></html>=

--Apple-Mail-B8354B69-F15F-4DE0-B1D2-D8D4E94C67DD--