Unable to load data from dataset XYZ Please check dataset & widget config

Question

Hi,

I've created a cockpit with a table widget.

I'm trying to use a calculated field, but in every formulas it returns an error: Unable to load data from dataset XYZ Please check dataset & widget config.

Can you help me? I've already checked the dataset category on role and all the suggestions regarding that.

Thanks

Comments

Dear pozzatos,

I think there could be a problem related to the formula you are using or the dataset connection.
Can you please share your knowageCockpit.log and knowage.log and the screenshot of the formula you are using?

Regards.

---

This is the formula, but the error appears in every kind of formulas.Thanks

---

Dear redjaw,

the knowagecockpit.log doesn't contain any errors, while in the knowage.log appaers:

[http-bio-8080-exec-867] 27 May 2022 15:34:28,852 ERROR it.eng.spagobi.api.v2.DataSetResource.getDataStorePostWithJsonInBody:688 - Error loading dataset data from Vendite Fatturato Confronto
it.eng.spagobi.utilities.exceptions.SpagoBIServiceException: An unexpected error occured while executing service
at it.eng.spagobi.api.common.AbstractDataSetResource.getDataStore(AbstractDataSetResource.java:260)
at it.eng.spagobi.api.v2.DataSetResource.getDataStorePostWithJsonInBody(DataSetResource.java:685)
at sun.reflect.GeneratedMethodAccessor1292.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:140)
at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:294)
at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:248)
at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:235)
at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:398)
at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:205)
at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:228)
at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:56)
at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:51)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:409)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1044)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:315)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: it.eng.spagobi.utilities.exceptions.SpagoBIRuntimeException: An error occurred while executing statement: SELECT "resp_vendita", "mese_fatturato", SUM("Anno in corso") AS "Anno in corso", SUM("Anno Precedente") AS "Anno Precedente", SUM("Differenza") AS "Differenza", "Anno Precedente"/NULLIF("Anno in corso", 0) AS "Percentuale" FROM (
SELECT q.resp_vendita, q.bp_name,q.linea_bp_prodotto,q.categoria_cliente,
yearinvoiced, q.azienda,
(date_part('year', dateinvoiced)-1),
monthinvoicednoyear as mese_fatturato,
SUM(CASE  WHEN yearinvoiced = cast(date_part('year', now())-1 as varchar(4)) THEN valueinvoiced ELSE 0 END) as "Anno Precedente",
SUM(CASE  WHEN yearinvoiced = cast(date_part('year', now()) as varchar(4)) THEN valueinvoiced ELSE 0 END) as "Anno in corso",
dateinvoiced as data_fattura,
(SUM(CASE yearinvoiced WHEN cast(date_part('year', now()) as varchar(4)) THEN valueinvoiced ELSE 0 END) -
SUM(CASE yearinvoiced WHEN cast(date_part('year', now())-1 as varchar(4)) THEN valueinvoiced ELSE 0 END)) as "Differenza",
valueinvoiced
FROM f3p_bi_vend_fatturato_confronto q
where yearinvoiced > '2020' and prodotto_cat <> 'Addebito'
group by resp_vendita,bp_name,linea_bp_prodotto,categoria_cliente,dateinvoiced,yearinvoiced, azienda, monthinvoicednoyear,valueinvoiced
order by bp_name
) T GROUP BY "resp_vendita","mese_fatturato" ORDER BY "resp_vendita" ASC
at it.eng.spagobi.tools.dataset.common.dataproxy.JDBCDataProxy.load(JDBCDataProxy.java:144)
at it.eng.spagobi.tools.dataset.bo.ConfigurableDataSet.loadData(ConfigurableDataSet.java:141)
at it.eng.spagobi.tools.datasource.bo.DataSource.executeStatement(DataSource.java:573)
at it.eng.spagobi.tools.datasource.bo.DataSource.executeStatement(DataSource.java:560)
at it.eng.spagobi.tools.dataset.strategy.AbstractJdbcEvaluationStrategy.execute(AbstractJdbcEvaluationStrategy.java:55)
at it.eng.spagobi.tools.dataset.strategy.AbstractEvaluationStrategy.executeQuery(AbstractEvaluationStrategy.java:68)
at it.eng.spagobi.tools.dataset.DatasetManagementAPI.getDataStore(DatasetManagementAPI.java:287)
at it.eng.spagobi.api.common.AbstractDataSetResource.getDataStore(AbstractDataSetResource.java:245)
... 32 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "t.Anno Precedente" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 163
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:224)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206)
at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:206)
at it.eng.spagobi.tools.dataset.common.dataproxy.JDBCDataProxy.load(JDBCDataProxy.java:138)
... 39 more

Regards
Stefano

Answer 1

Dear pozzatos,

from your logs I can see that an aggregation is missing on your formula's values.
I suggest you to use SUM("Anno Precedente")  SUM("Anno in Corso") by writing it or adding it with the "Aggregazione" formula helper.

That should work, but let me know if you have other errors.

I think that from you feedback we need to give the user a few more hints to make that operation easier.

Regards.

Comments

Dear redjaw,

I tried to use Sum() and it works corretly.

Example: (SUM("Anno in corso")-SUM("Anno Precedente"))/NULLIF(SUM("Anno Precedente"), 0)*100

Thanks a lot for the support.