Error occur while generating reports

Hi,
i got error when I generating error.

my report structure:

Band name = headerUserSkillLinks Orientation=H

Band name = UserSkillLinks
Orientation=H
dataset name = Data
<query = sql >
select s.name as “skill_name” , count(user_id) as “amount”
from profilegen_User_Skill_Link as l
join profilegen_skill as s on l.skill_id = s.id’
group by s.name

Templates:
Template for report CountByUser.xlsx (8.6 KB)

Error
An error occurred while loading data for band [UserSkillLinks] and query [Data]. Report name [CountByUser]
An error occurred while loading data for data set [Data]
null

app log

app.log (1.1 MB)

Please help me I am new to Reports in cuba

Thank you
John

CountByUser.zip (12.3 KB)

Hi John,

It seems you have two named regions in your template: Data and Data_set, and there’s no Data band in the report structure. Try to modify the template, and it should work.

Hi Olga,
thank you for replay
I made modify with templates remove data and data_set from templates. but still not working
and I made some update in template and structure. now you can more understandable.
I use jpql query in the report now. this query is working in the normal cuba screen but not in the report.
if don’t use count in the query the report working fine.

error
An error occurred while loading data for band [UserSkillLinks] and query [Dataset]. Report name [UserSkill]
An error occurred while loading data for data set [Dataset]
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.2.cuba16): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: org.postgresql.util.PSQLException: ERROR: column “t2.name” must appear in the GROUP BY clause or be used in an aggregate function
Position: 17

Call: SELECT t0.NAME, t2.NAME, COUNT(t2.ID) FROM PROFILEGEN_SKILL_CATEGORY t3, PROFILEGEN_USER t2, PROFILEGEN_USER_SKILL_LINK t1, PROFILEGEN_SKILL t0 WHERE (((t3.NAME = ?) AND (t1.DELETE_TS IS NULL)) AND ((((t0.ID = t1.SKILL_ID) AND (t0.DELETE_TS IS NULL)) AND ((t3.ID = t0.SKILLCATEGORY_ID) AND (t3.DELETE_TS IS NULL))) AND ((t2.ID = t1.USER_ID) AND (t2.DELETE_TS IS NULL)))) GROUP BY t0.NAME
	bind => [Fachliche Kompetenzen]
Query: ReportQuery(referenceClass=UserSkillLink sql="SELECT t0.NAME, t2.NAME, COUNT(t2.ID) FROM PROFILEGEN_SKILL_CATEGORY t3, PROFILEGEN_USER t2, PROFILEGEN_USER_SKILL_LINK t1, PROFILEGEN_SKILL t0 WHERE (((t3.NAME = ?) AND (t1.DELETE_TS IS NULL)) AND ((((t0.ID = t1.SKILL_ID) AND (t0.DELETE_TS IS NULL)) AND ((t3.ID = t0.SKILLCATEGORY_ID) AND (t3.DELETE_TS IS NULL))) AND ((t2.ID = t1.USER_ID) AND (t2.DELETE_TS IS NULL)))) GROUP BY t0.NAME")

Internal Exception: org.postgresql.util.PSQLException: ERROR: column "t2.name" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 17
Error Code: 0
Call: SELECT t0.NAME, t2.NAME, COUNT(t2.ID) FROM PROFILEGEN_SKILL_CATEGORY t3, PROFILEGEN_USER t2, PROFILEGEN_USER_SKILL_LINK t1, PROFILEGEN_SKILL t0 WHERE (((t3.NAME = ?) AND (t1.DELETE_TS IS NULL)) AND ((((t0.ID = t1.SKILL_ID) AND (t0.DELETE_TS IS NULL)) AND ((t3.ID = t0.SKILLCATEGORY_ID) AND (t3.DELETE_TS IS NULL))) AND ((t2.ID = t1.USER_ID) AND (t2.DELETE_TS IS NULL)))) GROUP BY t0.NAME
	bind => [Fachliche Kompetenzen]
Query: ReportQuery(referenceClass=UserSkillLink sql="SELECT t0.NAME, t2.NAME, COUNT(t2.ID) FROM PROFILEGEN_SKILL_CATEGORY t3, PROFILEGEN_USER t2, PROFILEGEN_USER_SKILL_LINK t1, PROFILEGEN_SKILL t0 WHERE (((t3.NAME = ?) AND (t1.DELETE_TS IS NULL)) AND ((((t0.ID = t1.SKILL_ID) AND (t0.DELETE_TS IS NULL)) AND ((t3.ID = t0.SKILLCATEGORY_ID) AND (t3.DELETE_TS IS NULL))) AND ((t2.ID = t1.USER_ID) AND (t2.DELETE_TS IS NULL)))) GROUP BY t0.NAME")
ERROR: column "t2.name" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 17 ```

**import report file below**

<a class="attachment" href="/discuss/uploads/default/original/2X/5/55a2a11a731f38cffa94ef55d934d39cb7ad2947.zip">UserSkill (2).zip</a> (17.9 KB) 

**import templates below** 

<a class="attachment" href="/discuss/uploads/default/original/2X/d/df9184287d12b261c610806dbd5bd4ef94364173.xlsx">Template for report _UserSkill_ (2).xlsx</a> (14.1 KB)

It is a common postgresql aggregation issue. To workaround it, try to modify the query to calculate the aggregation (count) in a sub-query.

By the way, could you please use triple back quotes ``` for code blocks or </> button on editor panel.

Hello Olga,
Thank you so much for your replay. This is working well.

I made query like this

select
p.name as “skill.name”,
count(e.user) as “count”
from profilegen$UserSkillLink e
join profilegen$Skill p on p.id = e.skill.id
join profilegen$SkillCategory sc on p.skillcategory.id= sc.id
where e.skill.skillcategory.name = ${skill_skillcategory_name1}
group by p.id, p.name

Now my problem is I can’t make this report chart
report_chart

when I run this chart in "report _ show chart " i got error

why this happen ? How can i make chart in reports? Could you please send the solution of this problem?**

Thank you

IllegalStateException: Unable to parse JSON chart configuration

	at com.haulmont.charts.web.toolkit.ui.amcharts.CubaAmchartsScene.setJson(CubaAmchartsScene.java:142)
	at com.haulmont.charts.web.gui.components.charts.amcharts.WebCustomChart.setNativeJson(WebCustomChart.java:65)
	at com.haulmont.charts.web.ui.JsonChartController.init(JsonChartController.java:28)
	at com.haulmont.cuba.gui.xml.layout.loaders.FrameLoader$FrameLoaderInitTask.execute(FrameLoader.java:265)
	at com.haulmont.cuba.gui.xml.layout.loaders.ComponentLoaderContext$InitTaskExecutor.run(ComponentLoaderContext.java:266)
	at com.haulmont.cuba.gui.xml.layout.loaders.ComponentLoaderContext.executeInitTasks(ComponentLoaderContext.java:160)
	at com.haulmont.cuba.gui.WindowManager.openFrame(WindowManager.java:965)
	at com.haulmont.cuba.gui.WindowManager.openFrame(WindowManager.java:907)
	at com.haulmont.cuba.gui.components.WindowDelegate.openFrame(WindowDelegate.java:292)
	at com.haulmont.cuba.web.gui.WebWindow.openFrame(WebWindow.java:504)
	at com.haulmont.cuba.gui.components.AbstractFrame.openFrame(AbstractFrame.java:532)
	at com.haulmont.reports.gui.report.run.ShowChartController.openChart(ShowChartController.java:115)
	at com.haulmont.reports.gui.report.run.ShowChartController.printReport(ShowChartController.java:138)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.haulmont.cuba.gui.xml.DeclarativeAction.actionPerform(DeclarativeAction.java:92)
	at com.haulmont.cuba.web.gui.components.WebButton.performAction(WebButton.java:44)
	at com.haulmont.cuba.web.gui.components.WebButton.lambda$new$61446b05$1(WebButton.java:36)
	at sun.reflect.GeneratedMethodAccessor298.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.vaadin.event.ListenerMethod.receiveEvent(ListenerMethod.java:510)
	at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:200)
	at com.vaadin.event.EventRouter.fireEvent(EventRouter.java:163)
	at com.vaadin.server.AbstractClientConnector.fireEvent(AbstractClientConnector.java:1037)
	at com.vaadin.ui.Button.fireClick(Button.java:377)
	at com.haulmont.cuba.web.toolkit.ui.CubaButton.fireClick(CubaButton.java:54)
	at com.vaadin.ui.Button$1.click(Button.java:54)
	at sun.reflect.GeneratedMethodAccessor299.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:158)
	at com.vaadin.server.ServerRpcManager.applyInvocation(ServerRpcManager.java:119)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocation(ServerRpcHandler.java:444)
	at com.vaadin.server.communication.ServerRpcHandler.handleInvocations(ServerRpcHandler.java:409)
	at com.vaadin.server.communication.ServerRpcHandler.handleRpc(ServerRpcHandler.java:274)
	at com.vaadin.server.communication.UidlRequestHandler.synchronizedHandleRequest(UidlRequestHandler.java:90)
	at com.vaadin.server.SynchronizedRequestHandler.handleRequest(SynchronizedRequestHandler.java:41)
	at com.vaadin.server.VaadinService.handleRequest(VaadinService.java:1436)
	at com.vaadin.server.VaadinServlet.service(VaadinServlet.java:385)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.serviceAppRequest(CubaApplicationServlet.java:301)
	at com.haulmont.cuba.web.sys.CubaApplicationServlet.service(CubaApplicationServlet.java:192)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at de.bredexsw.superskill.web.BredexAuthProvider.doFilter(BredexAuthProvider.java:47)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.filterByAuthProvider(CubaHttpFilter.java:114)
	at com.haulmont.cuba.web.sys.CubaHttpFilter.doFilter(CubaHttpFilter.java:91)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.lang.Thread.run(Thread.java:748) ```

If you use the same query for the chart, pay attention is does not contain a color field. Try to leave the Color field empty.

I did color field in empty … but i still got same error
UserSkill (4).zip (18.7 KB)

Could you please attach a sample project. The report you attached will not work without your data model.

Sorry i can’t my company not allowed to share the project to others. could you find what is reason for this error. How JSON error ? I don’t do anything with JSON. :frowning:

Hi,

This why it is called a sample project :slight_smile: no one is interested in your real project. Create a new example project, take a similar domain (or just exchange domain names), put as little entities in it as possible, reproduce the bug.

If you can reproduce it, great - put it in here and we will try to figure out.
If you can’t reproduce - great. Try to identify what is different in your example. If you find something: repeat the process.

This has some major upsides:

  1. you will learn a lot. Isolating a problem is a very important skill to have. You will learn about the framework, about the patterns of your domain model, some internals of the framework and so on.

  2. You will increase the likelihood that you will get an answer. Think about it: this is free service. A lot of people dont get payed to answer your question. Those who get, they can still pick the question that will give them a quick win. Since you probably don’t payed for the answer - if you do your very best to help the people to help you, so that you increase your chances…

  3. You created an abstract example that fulfils a community purpose. If the example is not pullated with the domain you are in but instead with a domain everyone understand, you do the community a favor, because the likelihood that other people have a similar question.

  4. You are a good role model to other community members. When other people see you creating great questions, doing good examples etc. They will start mirroring your behavior. This leads to overall better examples, faster answers etc.

See also Ответы на вопросы - CUBA.Platform.

Looking forward to your great example :wink:

Bye
Mario

3 Likes

hello olga,
here is my sample project… in this attachment there is no gradle and deploy folders
And report templates attached inside this zip file

sampleProject - Copy.zip (469.9 KB)

Thank you for a sample project. We have created an issue: Show Charts screen always runs report with default template · Issue #121 · cuba-platform/reports · GitHub.