Dataset report not showing proper breakdown.

Hello All,

We are running on 2.17 Snapshot (not in sync with trunk , some intermediate revision ). And we are facing this issue while pulling Dataset reports.

We are not seeing the numbesr for the breakdown of the category options, but only for category option combos.

I’ve done an analytics export and things went fine.

Let me know if you have any idea why this might be happening.

Please find the screen shot:

Regards,

Kiran.

sorry. missed the screen shot last time:

image

···

On Tue, Sep 2, 2014 at 12:19 PM, Kiran Prakash kprakash@thoughtworks.com wrote:

Hello All,

We are running on 2.17 Snapshot (not in sync with trunk , some intermediate revision ). And we are facing this issue while pulling Dataset reports.

We are not seeing the numbesr for the breakdown of the category options, but only for category option combos.

I’ve done an analytics export and things went fine.

Let me know if you have any idea why this might be happening.

Please find the screen shot:

Regards,

Kiran.

Hi Kiran,

please check that the “Skip category total in reports” property is set to false for the relevant category combination.

regards,

Lars

···

On Tue, Sep 2, 2014 at 9:18 AM, Kiran Prakash kprakash@thoughtworks.com wrote:

sorry. missed the screen shot last time:

On Tue, Sep 2, 2014 at 12:19 PM, Kiran Prakash kprakash@thoughtworks.com wrote:

Hello All,

We are running on 2.17 Snapshot (not in sync with trunk , some intermediate revision ). And we are facing this issue while pulling Dataset reports.

We are not seeing the numbesr for the breakdown of the category options, but only for category option combos.

I’ve done an analytics export and things went fine.

Let me know if you have any idea why this might be happening.

Please find the screen shot:

Regards,

Kiran.

Hello Lars,

any insight into this? this is holding up one of our stories. really appreciate if you can have a look in

thanks

Kiran

···

On Mon, Sep 8, 2014 at 10:30 PM, Mahendra Kariya mahendra.kariya@thoughtworks.com wrote:

Hi Lars,

Even after the “Skip category total in reports” property is set to false for category combinations, the data we are looking for is not coming up. Is there something we are missing?

On Tue, Sep 2, 2014 at 12:34 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Kiran,

please check that the “Skip category total in reports” property is set to false for the relevant category combination.

regards,

Lars


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

On Tue, Sep 2, 2014 at 9:18 AM, Kiran Prakash kprakash@thoughtworks.com wrote:

sorry. missed the screen shot last time:

On Tue, Sep 2, 2014 at 12:19 PM, Kiran Prakash kprakash@thoughtworks.com wrote:

Hello All,

We are running on 2.17 Snapshot (not in sync with trunk , some intermediate revision ). And we are facing this issue while pulling Dataset reports.

We are not seeing the numbesr for the breakdown of the category options, but only for category option combos.

I’ve done an analytics export and things went fine.

Let me know if you have any idea why this might be happening.

Please find the screen shot:

Regards,

Kiran.

Hello,

I did a little debugging and figure that this might be because the analytics query is failing with following error.

StatementCallback; bad SQL grammar [select “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”, sum(value) as value from analytics_2014 where “de” in (‘a0ea1b1d113’) and “weekly” in (‘2014W2’) and “uidlevel3” in (‘a42405657c4’) and “a99ecb2c5c3” in (‘abacfb5842a’, ‘a5dd89a503f’) and ( (uidlevel1 = ‘a2cf79e8f13’ and approvallevel <= 999) ) group by “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column “a99ecb2c5c3” does not exist
Position: 34
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456)
at org.springframework.jdbc.core.JdbcTemplate.queryForRowSet(JdbcTemplate.java:501)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getKeyValueMap(JdbcAnalyticsManager.java:366)
at org.hisp.dhis.analytics.data.JdbcAnalyticsManager.getAggregatedDataValues(JdbcAnalyticsManager.java:140)
at sun.reflect.GeneratedMethodAccessor876.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:95)
at java.util.concurrent.FutureTask.run(FutureTask.java:262)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.postgresql.util.PSQLException: ERROR: column “a99ecb2c5c3” does not exist
Position: 34
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:403)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:283)
at com.mchange.v2.c3p0.impl.NewProxyStatement.executeQuery(NewProxyStatement.java:35)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
… 13 more

  • INFO 2014-09-11 12:05:20,742 Query failed, likely because the requested analytics table does not exist (JdbcAnalyticsManager.java [SimpleAsyncTaskExecutor-921])
    org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [select “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”, sum(value) as value from analytics_2014 where “de” in (‘aee83d48c4c’) and “weekly” in (‘2014W2’) and “uidlevel3” in (‘a42405657c4’) and “a99ecb2c5c3” in (‘abacfb5842a’, ‘a5dd89a503f’) and ( (uidlevel1 = ‘a2cf79e8f13’ and approvallevel <= 999) ) group by “de”,“weekly”,“uidlevel3”,“a99ecb2c5c3”]; nested exception is org.postgresql.util.PSQLException: ERROR: column “a99ecb2c5c3” does not exist
    Position: 34

I have checked the database, and the table analytics_2014 exists.

I think this is because the generate sql seems wrong.

“uidlevel3” in (‘a42405657c4’) and “a99ecb2c5c3”

should this be “uidlevel3” in (‘a42405657c4’, “a99ecb2c5c3”)? or something else?

Are you guys aware of this issue and if it’s fixed in trunk, what revision?

We’re very close to release, so cannot afford to merge with trunk. Any help pointing out the fix/ revision number will be of great help

Thanks in advance

Kiran.

···

On Wed, Sep 10, 2014 at 1:23 PM, Mahendra Kariya mahendra.kariya@thoughtworks.com wrote:

Hi Lars/Morten,

It would be really helpful if you guys could point us to where we can look for if something is going wrong.

On Tue, Sep 9, 2014 at 10:59 AM, Kiran Prakash kprakash@thoughtworks.com wrote:

Hello Lars,

any insight into this? this is holding up one of our stories. really appreciate if you can have a look in

thanks

Kiran

On Mon, Sep 8, 2014 at 10:30 PM, Mahendra Kariya mahendra.kariya@thoughtworks.com wrote:

Hi Lars,

Even after the “Skip category total in reports” property is set to false for category combinations, the data we are looking for is not coming up. Is there something we are missing?

On Tue, Sep 2, 2014 at 12:34 PM, Lars Helge Øverland larshelge@gmail.com wrote:

Hi Kiran,

please check that the “Skip category total in reports” property is set to false for the relevant category combination.

regards,

Lars


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

On Tue, Sep 2, 2014 at 9:18 AM, Kiran Prakash kprakash@thoughtworks.com wrote:

sorry. missed the screen shot last time:

On Tue, Sep 2, 2014 at 12:19 PM, Kiran Prakash kprakash@thoughtworks.com wrote:

Hello All,

We are running on 2.17 Snapshot (not in sync with trunk , some intermediate revision ). And we are facing this issue while pulling Dataset reports.

We are not seeing the numbesr for the breakdown of the category options, but only for category option combos.

I’ve done an analytics export and things went fine.

Let me know if you have any idea why this might be happening.

Please find the screen shot:

Regards,

Kiran.

Hi there,

the problem here is that column a99ecb2c5c3 does not exist in the analytics
table. This corresponds to the "Type2" category, which is correctly set to
type disaggregation and use as data dimension. In fact, none of the
categories become columns/dimensions in analytics.

When I run your database with trunk it woks fine, see screenshot.

The error must exist somewhere in the tw branch. The interesting method is
this:

JdbcAnalyticsTableManager.getDimensionColumns (around line 227).

Can you please debug the call to

Collection<DataElementCategory> disaggregationCategories =
            categoryService.getDisaggregationDataDimensionCategories();

and check which categories, if any, it returns? If you can make this call
return all categories of type disaggregation and with data dimension to
true then the problem should be gone.

regards,

Lars

image