Error in pivot tables

Hi! We have imported some data and run the analytics but we are can not manage to see the data in the Pivot Table App, the data is available at the Data entry app. Whenever we try to build the tables it shows up a “No data to display” message.

The logs register this error every time we update the table in the Pivot table app:

nested exception is org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)

at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:438)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:146)

at sun.reflect.GeneratedMethodAccessor1112.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:108)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)

Any help will be so helpful! Thanks !!

Hi Marc,

Please go to the main menu > Reports > Analytics (left menu) and click Start export.

As soon as it completes you should get the data in the analysis apps.

···

On 30 January 2017 at 12:46, Marc Garnica marcgarnica13@gmail.com wrote:

Hi! We have imported some data and run the analytics but we are can not manage to see the data in the Pivot Table App, the data is available at the Data entry app. Whenever we try to build the tables it shows up a “No data to display” message.

The logs register this error every time we update the table in the Pivot table app:

nested exception is org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)

at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:438)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:146)

at sun.reflect.GeneratedMethodAccessor1112.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:108)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)

Any help will be so helpful! Thanks !!


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

Jan Henrik Øverland

Software developer, DHIS 2

University of Oslo

http://www.dhis2.org

Any error messages during the export process?

···

On 30 January 2017 at 12:56, Marc Garnica marcgarnica13@gmail.com wrote:

We did it twice, but it seems that the queries are crashing.

Thanks!

2017-01-30 12:51 GMT+01:00 Jan Henrik Øverland jan@dhis2.org:

Hi Marc,

Please go to the main menu > Reports > Analytics (left menu) and click Start export.

As soon as it completes you should get the data in the analysis apps.

On 30 January 2017 at 12:46, Marc Garnica marcgarnica13@gmail.com wrote:

Hi! We have imported some data and run the analytics but we are can not manage to see the data in the Pivot Table App, the data is available at the Data entry app. Whenever we try to build the tables it shows up a “No data to display” message.

The logs register this error every time we update the table in the Pivot table app:

nested exception is org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)

at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:438)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:146)

at sun.reflect.GeneratedMethodAccessor1112.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:108)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)

Any help will be so helpful! Thanks !!


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

Jan Henrik Øverland

Software developer, DHIS 2

University of Oslo

http://www.dhis2.org

Jan Henrik Øverland

Software developer, DHIS 2

University of Oslo

http://www.dhis2.org

Hi Marc,

never seen this one before. Did you generate analytics tables first? Need some more info. Can you share a dhis + server login privately?

regards,

Lars

···

On Mon, Jan 30, 2017 at 12:46 PM, Marc Garnica marcgarnica13@gmail.com wrote:

Hi! We have imported some data and run the analytics but we are can not manage to see the data in the Pivot Table App, the data is available at the Data entry app. Whenever we try to build the tables it shows up a “No data to display” message.

The logs register this error every time we update the table in the Pivot table app:

nested exception is org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)

at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:438)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:146)

at sun.reflect.GeneratedMethodAccessor1112.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:108)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)

Any help will be so helpful! Thanks !!


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

Lars Helge Øverland

Lead developer, DHIS 2

University of Oslo

Skype: larshelgeoverland

lars@dhis2.org

http://www.dhis2.org

Okay, could be a bug then. Please share what Lars asked for if possible.

···

On 30 January 2017 at 13:11, Marc Garnica marcgarnica13@gmail.com wrote:

Marc

The analytics export does not register any error in the logs. The data is monthly data added through a csv file, it was not the first time we run the analytics on the server and the other times it went well.

2017-01-30 13:00 GMT+01:00 Jan Henrik Øverland jan@dhis2.org:

Any error messages during the export process?

On 30 January 2017 at 12:56, Marc Garnica marcgarnica13@gmail.com wrote:

We did it twice, but it seems that the queries are crashing.

Thanks!


Jan Henrik Øverland

Software developer, DHIS 2

University of Oslo

http://www.dhis2.org

2017-01-30 12:51 GMT+01:00 Jan Henrik Øverland jan@dhis2.org:

Hi Marc,

Please go to the main menu > Reports > Analytics (left menu) and click Start export.

As soon as it completes you should get the data in the analysis apps.

On 30 January 2017 at 12:46, Marc Garnica marcgarnica13@gmail.com wrote:

Hi! We have imported some data and run the analytics but we are can not manage to see the data in the Pivot Table App, the data is available at the Data entry app. Whenever we try to build the tables it shows up a “No data to display” message.

The logs register this error every time we update the table in the Pivot table app:

nested exception is org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:415)

at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:470)

at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:511)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:438)

at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:146)

at sun.reflect.GeneratedMethodAccessor1112.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:498)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:108)

at java.util.concurrent.FutureTask.run(FutureTask.java:266)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)

at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: ERROR: column “analytics_2015.dx” must appear in the GROUP BY clause or be used in an aggregate function

Position: 8

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)

Any help will be so helpful! Thanks !!


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

Jan Henrik Øverland

Software developer, DHIS 2

University of Oslo

http://www.dhis2.org

Jan Henrik Øverland

Software developer, DHIS 2

University of Oslo

http://www.dhis2.org