Making report with the SQL query:
Select o.NAME as name1, company.name as name2, company.UPDATE_TS as date1, task.grade_risk_freq as freq,
task.grade_risk_rate as rate, task.is_grade as grade,
lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS freq_prev,
task.grade_risk_freq - lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS freq_diff,
lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS rate_prev,
task.grade_risk_freq - lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS rate_diff,
array_to_string(array_agg(distinct m.name),',') as offers,
array_to_string(array_agg(distinct m.plus),',') as plus,
array_to_string(array_agg(controly.name||'-'|| controly_ref.value_),', ') as controly
from OPERRISK_SCENARIO_COMPANY company
left join OPERRISK_ORDER o
on company.order_id=o.ID
left join operrisk_scenario_order_task task
on company.id = task.company_id
left join operrisk_additional_measure m
on task.id=m.scenario_order_task_id
left join operrisk_controly controly
on task.id=controly.scenario_order_task_id
left join operrisk_controly_ref controly_ref
on controly.controly_ref_id = controly_ref.id
where company.order_id=${anketa.id}
group by name1, name2, date1, freq, rate, grade
order by date1
Also I have created parameter “alias = anketa”, made a template .XLSX. But the System show me an error:
com.haulmont.reports.exception.ReportingException: An error occurred while loading data for band [name1] and query [name1]. Report name [Dynamics of scenario values]
An error occurred while loading data for data set [name1]
??????: ???????? ?? ??????????: uuid = character varying
?????????: ???????? ? ??????? ?????? ? ?????? ?????????? ?? ??????. ????????, ??? ??????? ???????? ????? ?????????? ?????.
???????: 1178 Query: Select o.NAME as name1, company.name as name2, company.UPDATE_TS as date1, task.grade_risk_freq as freq,
task.grade_risk_rate as rate, task.is_grade as grade,
lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS freq_prev,
task.grade_risk_freq - lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS freq_diff,
lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS rate_prev,
task.grade_risk_freq - lag(task.grade_risk_freq,1) OVER (ORDER BY company.UPDATE_TS) AS rate_diff,
array_to_string(array_agg(distinct m.name),',') as offers,
array_to_string(array_agg(distinct m.plus),',') as plus,
array_to_string(array_agg(controly.name||'-'|| controly_ref.value_),', ') as controly
from OPERRISK_SCENARIO_COMPANY company
left join OPERRISK_ORDER o
on company.order_id=o.ID
left join operrisk_scenario_order_task task
on company.id = task.company_id
left join operrisk_additional_measure m
on task.id=m.scenario_order_task_id
left join operrisk_controly controly
on task.id=controly.scenario_order_task_id
left join operrisk_controly_ref controly_ref
on controly.controly_ref_id = controly_ref.id
where company.order_id=?
group by name1, name2, date1, freq, rate, grade
order by date1 Parameters: [null]
??????: ???????? ?? ??????????: uuid = character varying
?????????: ???????? ? ??????? ?????? ? ?????? ?????????? ?? ??????. ????????, ??? ??????? ???????? ????? ?????????? ?????.
???????: 1178
at com.haulmont.reports.ReportingBean.createReportDocument(ReportingBean.java:283)
at com.haulmont.reports.ReportingBean.createReport(ReportingBean.java:166)
at com.haulmont.reports.ReportServiceBean.createReport(ReportServiceBean.java:37)
at sun.reflect.GeneratedMethodAccessor493.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:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)
at com.haulmont.cuba.core.sys.ServiceInterceptor.aroundInvoke(ServiceInterceptor.java:115)
at sun.reflect.GeneratedMethodAccessor241.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:629)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:618)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy140.createReport(Unknown Source)
at sun.reflect.GeneratedMethodAccessor492.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.haulmont.cuba.core.sys.remoting.LocalServiceInvokerImpl.invoke(LocalServiceInvokerImpl.java:95)
at com.haulmont.cuba.web.sys.remoting.LocalServiceProxy$LocalServiceInvocationHandler.invoke(LocalServiceProxy.java:146)
at com.sun.proxy.$Proxy248.createReport(Unknown Source)
at com.haulmont.reports.gui.ReportGuiManager.getReportResult(ReportGuiManager.java:207)
at com.haulmont.reports.gui.ReportGuiManager.printReportSync(ReportGuiManager.java:191)
at com.haulmont.reports.gui.ReportGuiManager.printReport(ReportGuiManager.java:177)
at com.haulmont.reports.gui.report.run.InputParametersWindow.printReport(InputParametersWindow.java:62)
at sun.reflect.GeneratedMethodAccessor491.invoke(Unknown Source)
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.GeneratedMethodAccessor366.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)
The query works if I delete condition with parameter “company.order_id=${anketa.id}”.
